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

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.partsupp to 'select_David' @'localhost';
grant select on test.supplier 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 'selins_Marry' @'localhost';
grant select,insert on test.part to 'selins_Marry' @'localhost';
grant select,insert on test.partsupp to 'selins_Marry' @'localhost';
grant select,insert on test.supplier to 'selins_Marry' @'localhost';
create role 'selins_Jack' @'localhost';
grant select,insert on test.orders to 'selins_Jack' @'localhost';
grant select,insert on test.lineitem  to 'selins_Jack' @'localhost';
create role 'selins_Mike' @'localhost';
grant select,insert on test.customer to 'selins_Mike' @'localhost';
grant select,insert on test.nation to 'selins_Mike' @'localhost';
grant select,insert on test.region to 'selins_Mike' @'localhost';

 得到结果：

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

create role 'manager_David' @'localhost';
grant all privileges on test.part to 'manager_David' @'localhost';
grant all privileges on test.supplier to 'manager_David' @'localhost';
grant all privileges on test.partsupp to 'manager_David' @'localhost';
grant select on test.orders to 'manager_David' @'localhost';
grant select on test.lineitem  to 'manager_David' @'localhost';
grant select on test.customer to 'manager_David' @'localhost';
grant select on test.nation to 'manager_David' @'localhost';
grant select on test.region to 'manager_David' @'localhost';
create role 'manager_Tom' @'localhost';
grant all privileges on test.orders to 'manager_Tom' @'localhost';
grant all privileges on test.lineitem to 'manager_Tom' @'localhost';
grant select on test.part to 'manager_Tom' @'localhost';
grant select on test.partsupp to 'manager_Tom' @'localhost';
grant select on test.supplier to 'manager_Tom' @'localhost';
grant select on test.customer to 'manager_Tom' @'localhost';
grant select on test.nation to 'manager_Tom' @'localhost';
grant select on test.region to 'manager_Tom' @'localhost';
create role 'manager_Jerry' @'localhost';
grant all privileges on test.customer to 'manager_Jerry' @'localhost';
grant all privileges on test.nation to 'manager_Jerry' @'localhost';
grant all privileges on test.region to 'manager_Jerry' @'localhost';
grant select on test.part to 'manager_Jerry' @'localhost';
grant select on test.partsupp to 'manager_Jerry' @'localhost';
grant select on test.supplier to 'manager_Jerry' @'localhost';
grant select on test.orders to 'manager_Jerry' @'localhost';
grant select on test.lineitem  to 'manager_Jerry' @'localhost';

得到结果:

（3）给用户分配权限 

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

grant 'manager_David' @'localhost' to 'David'@'localhost';
grant 'manager_Tom' @'localhost' to 'Tom' @'localhost';
grant 'manager_Jerry' @'localhost' to 'Jerry' @'localhost';

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

grant 'selins_Marry' @'localhost' to 'Marry' @'localhost';
grant 'selins_Jack' @'localhost' to 'Jack' @'localhost';
grant 'selins_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 的客户部门职员权限。

revoke all privileges on test.customer
from 'selins_Mike'@'localhost';
revoke all privileges on test.nation 
from 'selins_Mike'@'localhost';
revoke all privileges on test.region
from 'selins_Mike'@'localhost';

 得到结果：

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

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

  登录david后

select * from  test.partsupp;

insert into test.partsupp values(1,666,666,666.666,NULL);

delete from test.partsupp where partkey = 1 and suppkey = 666;

 

select * from test.customer;

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

  登录Mike后

select * from test.customer;