《数据库系统概论》课程之实验三：数据控制（安全性部分）

《数据库系统概论》课程之实验三：数据控制（安全性部分）

实验目的

（1）熟悉通过SQL对数据进行安全性控制。

（2）完成书本上习题的上机练习。

实验平台MySQL

MySQL8.0及其图形化工具Navicat Premium。

实验内容和要求

​ 使用SQL对数据进行安全性控制，包括：授权和权利回收。操作完成后查看已授权的用户是否真正具有授予的数据操作的权利；权利收回操作之后的用户是否确实丧失了收回的数据操作的权利）。根据以下要求认真填写实验报告，记录所有的实验用例。

教材：《数据库系统概论》第5版——第四章习题P153

第6题

对下列两个关系模式：

​ 学生（学号，姓名，年龄，性别，家庭住址，班级号）

​ 班级（班级号，班级名，班主任，班长）

使用GRANT语句完成下列授权功能：

（1）授予用户U1对两个表的所有权限，并可给其他用户授权。

（2）授予用户U2对学生表具有查看权限，对家庭住址具有更新权限。

（3）将对班级表查看权限授予所有用户。

（4）将对学生表的查询、更新权限授予角色R1.

（5）将角色R1授予用户U1，并且U1可继续授权给其他角色。

建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE 班级
(
	班级号 VARCHAR(31) PRIMARY KEY,
	班级名 VARCHAR(15),
	班主任 VARCHAR(15),
	班长 VARCHAR(15)
);

CREATE TABLE 学生 
(
	学号 VARCHAR(31) PRIMARY KEY,
	姓名 VARCHAR(15),
	年龄 INT,
	性别 VARCHAR(7),
	家庭住址 VARCHAR(255),
	班级号 VARCHAR(31),
	FOREIGN KEY(班级号) REFERENCES 班级(班级号)
);
  • 注意：先建”班级”表，因为”班级”表的主键”班级号”是”学生”表的字段”班级号”的外键（或者先建”职工“表，后建”部门“表，再使用ALTER TABLE 学生 ADD FOREIGN KEY (班级号) REFERENCES 班级(班级号);添加外键）

1

授予用户U1对两个表的所有权限，并可给其他用户授权。

  • 先创建用户U1

    1
    CREATE USER 'U1'IDENTIFIED BY '123456';

    image-20220401183413062

  • 再授予权限

    1
    2
    GRANT ALL PRIVILEGES ON `db4.6`.`学生` TO `U1` WITH GRANT OPTION;
    GRANT ALL PRIVILEGES ON `db4.6`.`班级` TO `U1` WITH GRANT OPTION;

    • 注意，对于MySQL，授予权限时只能使用WITH GRANT OPTION使权限可传递。

      image-20220401184719216

2

授予用户U2对学生表具有查看权限，对家庭住址具有更新权限。

  • 先创建用户U2

    1
    CREATE USER 'U2'IDENTIFIED BY '123456';

    image-20220401194854387

  • 再授予权限

    1
    GRANT SELECT,UPDATE(`家庭住址`) ON `学生` TO `U2`;

    image-20220401194919632

3

将对班级表查看权限授予所有用户。

由于MySQL没有PUBLIC关键字。故这里创建一个存储过程：把所有用户及其地址读至游标，遍历所有用户及其地址，并用grant语句分别授权，并查看权限进行验证。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
delimiter $$ -- 定界符
CREATE PROCEDURE grant_select_table1_to_pubic () 
BEGIN
	DECLARE v_user CHAR ( 32 ); -- 定义变量，用于储存用户名：
	DECLARE v_host CHAR ( 32 ); -- 定义变量，用于储存地址：
	DECLARE done INT DEFAULT FALSE; -- 定义结束标志
	DECLARE cur CURSOR FOR SELECT user,host FROM mysql.USER;  -- 定义游标（用户名及其地址）
	# where user <> 'root' and user not like '%.%' and user <>'' ;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 定义 NOT FOUND类异常的HANDLER：

	OPEN cur; -- 打开游标
	FETCH cur INTO v_user,v_host; 	-- 取游标的一行数据到变量，并推进游标

	-- 每取一个user，构造一条授权语句，然后执行这条语句
	-- 因为grant语句不接受变量，所以只能构造整条语句
	WHILE NOT done DO

			SET @QUERY = CONCAT( 'GRANT SELECT ON `班级` to \'', v_user,'\'@\'',v_host,'\''); -- 授权语句
			PREPARE statement FROM	@QUERY;
			EXECUTE statement;
			SET @QUERY = CONCAT( 'SHOW GRANTS FOR \'', v_user,'\'@\'',v_host,'\''); -- 查看权限语句
			PREPARE statement FROM	@QUERY;
			EXECUTE statement;
			DEALLOCATE PREPARE statement;
			FETCH cur INTO v_user,v_host; -- 取下一个用户及其地址
	END WHILE;
	
	CLOSE cur;
	
END $$

CALL grant_select_table1_to_pubic ();  -- 调用存储过程，授权给所有用户:

  • 结果如下：对所有用户授权成功

    image-20220401231019589

    image-20220401231033201

    image-20220401231125226

    image-20220401231157009

    image-20220401231407276

    image-20220401231420023

4

将对学生表的查询、更新权限授予角色R1

  • 先创建角色R1

    1
    CREATE ROLE R1

    image-20220401201556069

  • 再授予权限

    1
    2
    3
    GRANT SELECT,UPDATE
    ON `学生`
    TO R1;

    image-20220401201627245

5

将角色R1授予用户U1，并且U1可继续授权给其他角色。

1
2
3
4
5
6
GRANT R1
TO U1
WITH ADMIN OPTION;

#注意：要激活角色
SET DEFAULT ROLE R1 TO 'U1';
  • 在MySQL中，将角色授予用户，，则需要使用WITH ADMIN OPTION，而无法使用WITH GRANT OPTION。
  • 只有通过SET DEFAULT ROLE R1 TO 'U1'激活，U1才会拥有角色R1的权限。

查看授权结果

1
2
3
SHOW GRANTS FOR 'U1';
SHOW GRANTS FOR 'R1';
SHOW GRANTS FOR 'U2';

  • U1

    image-20220401232638711

  • U2

    image-20220401232914870

  • R1

    image-20220401232721391

第7题

今有以下两个关系模式：

​ 职工（职工号，姓名，年龄，职务，工资，部门号）

​ 部门（部门号，名称，经理名，地址，电话号）

请用SQL语句的GRANT和REVOKE语句（加上视图机制）完成以下授权定义或存取控制功能：

（1）用户王明对两个表有SELECT权限。

（2）用户李勇对两个表有INSERT和DELETE权限。

（3）每个职工只对自己的记录有SELECT权限。

（4）用户刘星对职工表有SELECT权限，对工资字段具有更新权限。

（5）用户张新具有修改这两个表的结构的权限。

（6）用户周平具有对两个表的所有权限（读、插、改、删数据），并具有给其他用户授权的权限。

（7）用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，他不能查看每个人的工资。

建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE 部门
(
	部门号 VARCHAR(31) PRIMARY KEY,
	名称 VARCHAR(15),
	经理名 VARCHAR(15),
	地址 VARCHAR(15),
	电话号 VARCHAR(15)
);

CREATE TABLE 职工 
(
	职工号 VARCHAR(31) PRIMARY KEY,
	姓名 VARCHAR(15),
	年龄 INT,
	职务 VARCHAR(15),
	工资 DOUBLE,
	家庭住址 VARCHAR(255),
	部门号 VARCHAR(31),
	FOREIGN KEY(部门号) REFERENCES 部门(部门号)
);
  • 注意：先建”部门”表，因为”部门”表的主键”部门号”是”职工”表的字段”部门号”的外键。（或者先建”职工“表，后建”部门“表，再使用ALTER TABLE 职工 ADD FOREIGN KEY (部门号) REFERENCES 部门(部门号);添加外键）

1

用户王明对两个表有SELECT权限。

  • 先创建用户

    1
    CREATE USER 王明;

    image-20220402145004027

  • 再授予权限

    1
    2
    GRANT SELECT ON TABLE 职工 TO 王明;
    GRANT SELECT ON TABLE 部门 TO 王明;

    image-20220402145017024

2

用户李勇对两个表有INSERT和DELETE权限。

  • 先创建用户

    1
    CREATE USER 李勇;

    image-20220402145110724

  • 再授予权限

    1
    2
    GRANT INSERT,DELETE ON TABLE 职工 TO 李勇;
    GRANT INSERT,DELETE ON TABLE 部门 TO 李勇;

    image-20220402145241943

3

每个职工只对自己的记录有SELECT权限。

  • 先创建相应的视图。USER()的返回值是用户名和地址，故假定员工的用户名是”职工“表中的姓名，且从localhost登录数据库。

    1
    create view 职工视图 as select * from 职工 where CONCAT(职工.姓名,'@localhost')=USER();

    image-20220402153904098

  • 因为MySQL没有PUBLIC关键字，故再如6.3一般调用一个存储过程把视图的select权限授予每个用户

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    delimiter $$
    CREATE PROCEDURE grant_select_职工视图_to_pubic () 
    BEGIN
    	DECLARE v_user CHAR ( 32 ); -- 定义变量，用于储存用户名：
    	DECLARE v_host CHAR ( 32 ); -- 定义变量，用于储存地址：
    	DECLARE done INT DEFAULT FALSE; -- 定义结束标志
    	DECLARE cur CURSOR FOR SELECT user,host FROM mysql.USER;  -- 定义游标（用户名及其地址）
    	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 定义 NOT FOUND类异常的HANDLER：

    	OPEN cur; -- 打开游标
    	FETCH cur INTO v_user,v_host; 	-- 取游标的一行数据到变量，并推进游标

    	-- 每取一个user，构造一条授权语句，然后执行这条语句
    	-- 因为grant语句不接受变量，所以只能构造整条语句
    	WHILE NOT done DO
    			SET @QUERY = CONCAT( GRANT SELECT ON 职工视图 to \'', v_user,'\'@\'',v_host,'\''); -- 授权语句
    			PREPARE statement FROM	@QUERY;
    			EXECUTE statement;
    			DEALLOCATE PREPARE statement;
    			FETCH cur INTO v_user,v_host; -- 取下一个用户
    	END WHILE;
    	
    	CLOSE cur;
    	
    END $$

    CALL grant_select_职工视图_to_pubic ();  -- 调用过程，授权给所有用户:

    image-20220402154216161

  • 在创建存储过程后，还可以创建一个触发器，使在”职工”表中有新职工的记录时，再使用GRANT进行授权（默认该职工的地址为’%’）。（注意，触发器无法定义于对系统表的操作）

    1
    2
    3
    4
    5
    6
    DELIMITER $$
    CREATE TRIGGER grant_select_职工视图 AFTER INSERT ON 职工
    BEGIN
    	GRANT SELECT ON `职工视图` TO CONCAT(NEW.姓名,'@%') 
    END
    DELIMITER ; -- 把定界符改回‘;’

  • 听说这个地方易出问题，故做个测试：

    “职工”表如下：

    image-20220402211401588

    在”李勇”表查看视图”职工视图”：

    image-20220402211519668

    可以得到，李勇只能查看自己的记录：

    image-20220402211701809

4

用户刘星对职工表有SELECT权限，对工资字段具有更新权限。

  • 先创建用户

    1
    CREATE USER 刘星;

    image-20220402160351729

  • 再授予权限

    1
    GRANT SELECT,UPDATE(工资) ON TABLE 职工 TO 刘星;

    image-20220402160409622

5

用户张新具有修改这两个表的结构的权限。

  • 先创建用户

    1
    CREATE USER 张新;

    image-20220402160817955

  • 再授予权限

    1
    2
    GRANT ALTER ON TABLE 职工 TO 张新;
    GRANT ALTER ON TABLE 部门 TO 张新;

    image-20220402161019804

6

用户周平具有对两个表的所有权限（读、插、改、删数据），并具有给其他用户授权的权限。

  • 先创建用户

    1
    CREATE USER 周平;

  • 再授予权限

    1
    2
    GRANT ALL PRIVILEGES ON TABLE 职工 TO 周平 WITH GRANT OPTION;
    GRANT ALL PRIVILEGES ON TABLE 部门 TO 周平 WITH GRANT OPTION;

    image-20220402161238445

7

用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，他不能查看每个人的工资。

  • 创建用户

    1
    CREATE USER 杨兰;

    image-20220402161433925

  • 创建含每个部门职工最高工资、最低工资、平均工资的视图

    1
    2
    3
    4
    5
    CREATE VIEW 部门工资 AS
    	SELECT 部门.名称,MAX(工资),MIN(工资),AVG(工资)
    		FROM 部门,职工
    			WHERE 部门.部门号=职工.部门号
    				GROUP BY 职工.部门号;

    image-20220402162134126

  • 给杨兰授予SELECT该视图的权限

    1
    GRANT SELECT ON 部门工资 TO 杨兰;

    image-20220402162338611

第8题

针对习题7中（1）～（7）的每一种情况，撤销个用户所授予的权限。

1

撤销用户王明对两个表的SELECT权限。

1
2
REVOKE SELECT ON TABLE 职工 FROM 王明;
REVOKE SELECT ON TABLE 部门 FROM 王明;

image-20220402162547599

2

撤销用户李勇对两个表的INSERT和DELETE权限。

1
2
REVOKE INSERT,DELETE ON TABLE 职工 FROM 李勇;
REVOKE INSERT,DELETE ON TABLE 部门 FROM 李勇;

image-20220402162752828

3

撤销每个职工对自己的记录的SELECT权限。

同样使用存储过程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
delimiter $$
CREATE PROCEDURE revoke_select_职工视图_from_pubic () 
BEGIN
	DECLARE v_user CHAR ( 32 ); -- 定义变量，用于储存用户名：
	DECLARE v_host CHAR ( 32 ); -- 定义变量，用于储存地址：
	DECLARE done INT DEFAULT FALSE; -- 定义结束标志
	DECLARE cur CURSOR FOR SELECT user,host FROM mysql.USER;  -- 定义游标（用户名及其地址）
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 定义 NOT FOUND类异常的HANDLER：

	OPEN cur; -- 打开游标
	FETCH cur INTO v_user,v_host; 	-- 取游标的一行数据到变量，并推进游标

	-- 每取一个user，构造一条授权语句，然后执行这条语句
	-- 因为grant语句不接受变量，所以只能构造整条语句
	WHILE NOT done DO

			SET @QUERY = CONCAT( 'GRANT SELECT ON 职工视图 TO \'', v_user,'\'@\'',v_host,'\''); -- 授权语句
			PREPARE statement FROM	@QUERY;
			EXECUTE statement;
			-- 先授权,再撤权,防止因有些用户没有该权限而报错
			SET @QUERY = CONCAT( 'REVOKE SELECT ON 职工视图 FROM \'', v_user,'\'@\'',v_host,'\''); -- 撤权语句
			PREPARE statement FROM	@QUERY;
			EXECUTE statement;
			DEALLOCATE PREPARE statement;
			FETCH cur INTO v_user,v_host; -- 取下一个用户
	END WHILE;
	
	CLOSE cur;
	
END $$

CALL revoke_select_职工视图_from_pubic () ;  -- 调用过程，撤销所有用户的该权限

4

撤销用户刘星对职工表的SELECT权限，对工资字段的更新权限。

1
REVOKE SELECT,UPDATE(工资) ON TABLE 职工 FROM 刘星;

image-20220402164013361

5

撤销用户张新修改这两个表的结构的权限。

1
2
REVOKE ALTER ON TABLE 职工 FROM 张新;
REVOKE ALTER ON TABLE 部门 FROM 张新;

image-20220402164141766

6

撤销用户周平对两个表的所有权限（读、插、改、删数据）。

1
2
REVOKE ALL PRIVILEGES ON TABLE 职工 FROM 周平;
REVOKE ALL PRIVILEGES ON TABLE 部门 FROM 周平;

image-20220402164434305

7

撤销用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，即撤销用户杨兰查看视图”部门工资“的权限。

1
REVOKE SELECT ON 部门工资 FROM 杨兰;

image-20220402164605029

实验心得

在这次实验中，我总结了以下注意点，从而对SQL语句和数据库的安全性控制更加熟练：

  • 建表时有个细节：对于有外键的表，可以先建立该外键对应主键的表，然后在建具有外键的表时同时使用FOREIGN ... REFERENCES ...语句。或者在建完两张表后再使用ALTER给表添加外键。

  • MySQL的Grant操作不能同时对两张表使用。（6.1、7.1、7.5等）

  • 对于MySQL，授予权限时，即GRANT后所跟的是某些权限，只能使用WITH GRANT OPTION使权限可传递。而授予角色时，若要用户可继续授权，需要使用WITH ADMIN OPTION。在MySQL中，权限授予的传播是关联的。需要再加注意的是，只有通过SET DEFAULT ROLE R1 TO 'U1'激活，U1才会拥有角色R1的权限。（6.1、6.5）

  • 由于MySQL没有PUBLIC关键字。故对所有用户进行相同的操作时，可以构建一个存储过程（PROCEDURE），用游标遍历用户名以及地址，达到批量处理的目的，在有许多用户名时能够提升效率。（6.3、7.3）

  • 若改变某一个表，或其中的某行后，想要对数据库做固定的操作，可以使用触发器来实现。需要注意的是，MySQL的触发器不支持动态SQL语句。（7.3）
  • 视图可以提供一个统一访问数据的接口，即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限。当用户关心一部分数据时，可以考虑创建一个视图进行查看数据。（7.3、7.7）

  • Group by根据一个或多个列对结果集进行分组，从而能使用MAX、MIN、AVG等函数对该组数据进行处理。（7.7）

  • REVOKE只能撤销已存在的权限。

部分参考链接

with admin option 和 with grant option 的区别 (转)

mysql用户及权限（WITH GRANT OPTION）

mysql数据库如何将某一个表的查询权限授予给所有用户？