数据库实验五 安全性语言实验

1.实验目的

掌握自主存取控制权限的定义和维护方法。

2.实验内容和要求

定义用户、角色，分配权限给用户和角色、回收权限，以相应的用户名登陆数据库验证权限分配是否正确。

3.实验步骤

（1）创建用户：

①为采购、销售和客户管理等三个部门的经理 David、Tom、Jerry 创建用户表示，要求具有创建用户或角色的权利。

源代码：

CREATE USER 'David'@'localhost' IDENTIFIED BY '123456';

CREATE USER 'Tom'@'localhost' IDENTIFIED BY '123456';

CREATE USER 'Jerry'@'localhost' IDENTIFIED BY '123456';

GRANT CREATE USER,
 CREATE ROLE ON *.* TO 'David'@'localhost' WITH GRANT OPTION;

GRANT CREATE USER,
 CREATE ROLE ON *.* TO 'Tom'@'localhost' WITH GRANT OPTION;

GRANT CREATE USER,
 CREATE ROLE ON *.* TO 'Jerry'@'localhost' WITH GRANT OPTION;

结果截图：

 

②为采购、销售和客户管理等三个部门的职员 Marry、Jack、Mike 创建用户标识和用户口令。

源代码：

CREATE USER 'Marry'@'localhost' IDENTIFIED BY '123456';

CREATE USER 'Jack'@'localhost' IDENTIFIED BY '123456';

CREATE USER 'Mike'@'localhost' IDENTIFIED BY '123456';

结果截图：

 

（2）创建角色并分配权限：

①各个部门分别创建一个查询角色，并分配相应的查询权限。采购对应：零件表、供应商表、零件供应联系表

销售对应：订单表和订单明细表

客户对应：顾客表、国家表、地区表

源代码：

CREATE ROLE 'SELECT_David'@'localhost';

GRANT SELECT
	ON test.part TO 'SELECT_David'@'localhost';

GRANT SELECT
	ON test.supplier TO 'SELECT_David'@'localhost';

GRANT SELECT
	ON test.partsupp TO 'SELECT_David'@'localhost';

CREATE ROLE 'SELECT_Tom'@'localhost';

GRANT SELECT
	ON test.orders TO 'SELECT_Tom'@'localhost';

GRANT SELECT
	ON test.lineitem TO 'SELECT_Tom'@'localhost';

CREATE ROLE 'SELECT_Jerry'@'localhost';

GRANT SELECT
	ON test.customer TO 'SELECT_Jerry'@'localhost';

GRANT SELECT
	ON test.nation TO 'SELECT_Jerry'@'localhost';

GRANT SELECT
	ON test.region TO 'SELECT_Jerry'@'localhost';

结果截图：

 

 

 

②为各个部门分别创建一个职员角色，对本部门具有查看、插入权限。

源代码：

CREATE ROLE 'WORKERS_Marry'@'localhost';

GRANT SELECT,INSERT
	ON test.part TO 'WORKERS_Marry'@'localhost';

GRANT SELECT,INSERT
	ON test.supplier TO 'WORKERS_Marry'@'localhost';

GRANT SELECT,INSERT
	ON test.partsupp TO 'WORKERS_Marry'@'localhost';

CREATE ROLE 'WORKERS_Jack'@'localhost';

GRANT SELECT,INSERT
	ON test.orders TO 'WORKERS_Jack'@'localhost';

GRANT SELECT,INSERT
	ON test.lineitem TO 'WORKERS_Jack'@'localhost';

CREATE ROLE 'WORKERS_Mike'@'localhost';

GRANT SELECT,INSERT
	ON test.customer TO 'WORKERS_Mike'@'localhost';

GRANT SELECT,INSERT
	ON test.nation TO 'WORKERS_Mike'@'localhost';

GRANT SELECT,INSERT
	ON test.region TO 'WORKERS_Mike'@'localhost';

结果截图：

 

 

 

③为各部门创建一个经理角色，相应角色对本部门的信息具有完全控制权限，经理有权给本部门职员分配权限

源代码：

CREATE ROLE 'MANAGER_David'@'localhost';

GRANT ALL PRIVILEGES ON test.part TO 'MANAGER_David'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON test.supplier TO 'MANAGER_David'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON test.partsupp TO 'MANAGER_David'@'localhost' WITH GRANT OPTION;

CREATE ROLE 'MANAGER_Tom'@'localhost';

GRANT ALL PRIVILEGES ON test.orders TO 'MANAGER_Tom'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON test.lineitem TO 'MANAGER_Tom'@'localhost' WITH GRANT OPTION;

CREATE ROLE 'MANAGER_Jerry'@'localhost';

GRANT ALL PRIVILEGES ON test.customer TO 'MANAGER_Jerry'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON test.nation TO 'MANAGER_Jerry'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON test.region TO 'MANAGER_Jerry'@'localhost' WITH GRANT OPTION;

结果截图：

 

 

（3）给用户分配权限

需要激活角色！！！

set default role all to David;

①给各部门经理分配权限。

源代码：

GRANT 'MANAGER_David'@'localhost' TO 'David'@'localhost';

GRANT 'MANAGER_Tom'@'localhost' TO 'Tom'@'localhost';

GRANT 'MANAGER_Jerry'@'localhost' TO 'Jerry'@'localhost';

 结果截图：

 

 

②给各部门职员分配权限。

源代码：

GRANT 'WORKERS_Marry'@'localhost' TO 'Marry'@'localhost';

GRANT 'WORKERS_Jack'@'localhost' TO 'Jack'@'localhost';

GRANT 'WORKERS_Mike'@'localhost' TO 'Mike'@'localhost';

结果截图：

 

 

（4）回收角色或用户权限

①收回客户经理角色的客户信息查看权限。

源代码：

REVOKE SELECT
	ON TABLE test.customer
FROM
	'MANAGER_Jerry'@'localhost';
REVOKE SELECT
	ON TABLE test.nation
FROM
	'MANAGER_Jerry'@'localhost';
REVOKE SELECT
	ON TABLE test.region
FROM
	'MANAGER_Jerry'@'localhost';

 结果截图：

 

②回收 Mike 的客户部门职员权限。

由于Mike的权限是由WORKERS_Mike授予的，自身无法查看权限，故需要通过收回WORKERS_Mike的权限收回Mike的权限。

源代码：

 

REVOKE ALL PRIVILEGES
	ON TABLE test.customer
FROM
	'WORKERS_Mike'@'localhost';
REVOKE ALL PRIVILEGES
	ON TABLE test.nation
FROM
	'WORKERS_Mike'@'localhost';
REVOKE ALL PRIVILEGES
	ON TABLE test.region
FROM
	'WORKERS_Mike'@'localhost';

结果截图：

 

（5）验证权限分配的正确性

①以 David 用户名登录数据库，验证采购部门经理的权限。

在命令行中进行了该部分实验，代码及结果如下。

源代码及结果截图：

在命令行中使用

mysql -u David -p

命令并输入密码123456登录David用户身份

使用

SELECT *

FROM test.customer;

命令查询顾客表，此时报错David用户没有权限查询顾客表

 

使用

SELECT *

FROM test.part;

命令查询零件表，此时查询成功，证明权限授予正确

②回收 Mike 的客户部门职员权限。

源代码及结果截图：

以Mike身份登录数据库，使用数据库test，报错，证明经过上一次的回收权限后，Mike已经没有权限使用该数据库。