数据库原理及应用.实验5.数据库的完整性和安全性

2022/11/3 修复 employees员工表、departments 部门表 的 Markdown表格显示

实验报告

　　课程名称：数据库原理及应用
　　实验项目名称：数据库的完整性和安全性
　　实验时间：2021年5月24日

---

实 验 目 的:

　　1. 熟悉MySQL的完整性约束技术。
　　2. 了解MySQL的违反完整性处理措施。
　　3. 了解登录账户的管理理念与具体方法。
　　4. 了解数据库用户的管理的要则。
　　5. 了解用户权限管理的内涵与方法。

实 验 环 境:

　　MySQL、SQLyog

实 验 内 容 及 过 程:

一、测试完整性

• 创建库MyEmployees并创建以下两个关系模式，使用SQL对数据进行完整性控制, 并证实，当操作违反了完整性 约束条件时，系统是如何处理的。

  员工（员工编号，姓名，年龄，工种名称，工资，部门号）；

  部门（部门编号，名称，经理名，地址，电话号码）；

  employees 员工表

  字段名	数据类型	约束	含义
  employee_id	int	primary key	员工编号
  employee_name	varchar(20)		姓名
  age	smallint		年龄
  job_title	varchar(20)	not null	工种名称
  salary	double(10,2)	default 2000	工资
  department_id	int	foreign key	部门编号

  departments 部门表

  字段名	数据类型	约束	含义
  department_id	int	primary key	部门编号
  department_name	varchar(20)		部门名称
  manager_name	varchar(20)		经理名
  location	varchar(30)	default '福建'	地址
  phone	char(11)	not null unique	电话号码

  DROP DATABASE MyEmployees;
  CREATE DATABASE IF NOT EXISTS MyEmployees;
  USE MyEmployees;
  CREATE TABLE departments(
  	department_id INT PRIMARY KEY,
  	department_name VARCHAR(20),
  	manager_name VARCHAR(20),
  	location VARCHAR(30) DEFAULT '福建',
  	phone CHAR(11) NOT NULL UNIQUE
  );
  CREATE TABLE employees (
  	employee_id INT PRIMARY KEY,
  	employee_name VARCHAR(20),
  	age SMALLINT,
  	job_title VARCHAR(20) NOT NULL,
  	salary DOUBLE(10,2) DEFAULT 2000,
  	department_id INT,
  	FOREIGN KEY(department_id) REFERENCES departments(department_id)
  );
  

1. 向员工表中插入记录，验证主码不能为空且不能重复。

   INSERT departments
   VALUES (01,'信息部','李四','北京','010110');
   
   INSERT employees
   VALUES (1001,'张三',20,'技工',5000,01); #正常添加
   
   INSERT employees
   VALUES ('','张三',20,'技工',5000,01); #主码不能为空
   
   INSERT employees
   VALUES (1001,'张三',20,'技工',5000,01); #主码不能重复
   

2. 向员工表中插入记录，验证工种名称不能取空值。

   INSERT employees
   VALUES (1002,'李四',20,'',50000,01);
   

3. 向员工表中插入记录，不用给工资赋值，查看是否有默认值。

   INSERT employees (employee_id,employee_name,age,job_title,department_id)
   VALUES (1002,'李四',20,'经理',01);
   
   SELECT * FROM employees
   WHERE employee_id=1002;
   

4. 向员工表中插入记录，验证外码的值必须是其所参照的主码的值。

   INSERT employees
   VALUES (1003,'王五',21,'技工',6000,02);
   

5. 向部门表中插入记录，验证电话号码是否能取重复值。

   INSERT departments
   VALUES (02,'后勤部','小明','北京','010110');#电话号码不可取重复值
   

6. 向两张表中分别插入一条记录，如下，删除部门表中新插入的记录，能否正常删除？若不能，该怎么处理？（违约处理，可级联置空或删除；可先删外键定义，再重新添加外键定义）

   INSERT INTO departments
   VALUES(10, '研发部', '张时', '上海', 1361111111);
   INSERT INTO employees
   VALUES(100, '李丽莎', 30, '软件工程师', 10000, 10);
   
   DELETE FROM departments
   WHERE department_id=10; #传统办法无法正常删除
   
   #SHOW CREATE TABLE employees
   #先删除表级约束
   ALTER TABLE employees DROP FOREIGN KEY employees_ibfk_1;
   #再添加级联删除
   ALTER TABLE employees
   ADD FOREIGN KEY(department_id) REFERENCES departments(department_id)
   ON DELETE CASCADE;
   #即可删除数据
   DELETE FROM departments
   WHERE department_id=10;
   

7. 向两张表中分别插入一条记录，如下，更新部门表中新插入的记录，将部门号更改为99，能否正常更改？若不能，该怎么处理？（违约处理，可级联置空或更新；可先删外键定义，再重新添加外键定义）

   INSERT INTO departments
   VALUES(30, '人事部', '张琳', '福建', 1361111112);
   INSERT INTO employees
   VALUES(110, '李莎', 30, '会计', 6000, 30);
   
   ALTER TABLE employees DROP FOREIGN KEY employees_ibfk_1;
   ALTER TABLE employees
   ADD FOREIGN KEY(department_id) REFERENCES departments(department_id)
   ON UPDATE CASCADE;
   UPDATE departments
   SET department_id=99
   WHERE department_id=30
   

二、测试安全性

1. 添加两个新用户，user1的密码为1234，只能通过本地访问，user2的密码为adcd，可以任意电脑进行链接访问。点击"创建新连接 Ctrl+M"打开登录界面，分别以user1和user2身份登录MySQL服务器，试运行CREATE DATABASE test，能否成功？

   CREATE USER 
   'user1'@'localhost' IDENTIFIED BY '1234',
   'user2' IDENTIFIED BY 'adcd';
   

   无法成功

2. 使用GRANT语句创建一个新用户user3，密码为1234，可以任意电脑进行链接访问，并授予库MyEmployees所有数据表的SELECT和UPDATE权限，验证该用户是否可以查询、更新、删除？

   CREATE USER 'user3'@'%' IDENTIFIED BY '1234';
   GRANT SELECT,UPDATE 
   ON MyEmployees.* 
   TO 'user3'@'%';
   

3. 以使用root身份将user1的密码修改为1111，并验证。

   SET PASSWORD FOR 'user1'@'localhost' ='1111';
   

4. 以使用user2身份将user1的密码修改为1234，能否修改，为什么？

   无法修改，因为无权限。

5. 将查询员工表信息，修改员工表的工资授权给user1。

   GRANT SELECT,UPDATE(salary)
   ON MyEmployees.employees 
   TO 'user1';
   
   

6. 使用GRANT语句创建一个新用户user4，可以任意电脑进行链接访问，密码为"1234"。用户user4对库MyEmployees中所有的数据有查询、插入权限，并授予GRANT权限。

   CREATE USER 'user4'@'%' IDENTIFIED BY '1234';
   GRANT SELECT,INSERT 
   ON MyEmployees.* 
   TO 'user4'@'%'
   WITH GRANT OPTION;
   

7. 以user4身份登录，将对部门表的查询权限授予user2，验证是否能将删除权限也授予给user2。

   GRANT SELECT
   ON MyEmployees.* 
   TO 'user2'@'%'
   WITH GRANT OPTION;
   

8. 回收user2的所有权限后再删除user2的账户。

   REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'user2';
   DROP USER 'user2';
   

实 验 心 得：

　　通过本次实验，我更加熟悉了MySQL的完整性约束技术，了解了MySQL的违反完整性处理措施，了解了登录账户的管理理念与具体方法，了解数据库用户的管理的要则，了解用户权限管理的内涵与方法。收获颇丰。

附 录：