实验目的
掌握自主存取控制权限的定义和维护方法。
实验内容和要求
定义用户、角色,分配权限给用户和角色、回收权限,以相应的用户名登陆数据库验证
权限分配是否正确。
实验步骤和实验结果
(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';
select_David:
select_Tom:
select_Jerry:
②为各个部门分别创建一个职员角色,对本部门具有查看、插入权限:
CREATE ROLE 'employee_Marry' @'localhost';
GRANT INSERT,
SELECT ON
test.part TO 'employee_Marry' @'localhost';
GRANT INSERT,
SELECT ON
test.supplier TO 'employee_Marry' @'localhost';
GRANT INSERT,
SELECT ON
test.partsupp TO 'employee_Marry' @'localhost';
CREATE ROLE 'employee_Jack' @'localhost';
GRANT INSERT,
SELECT ON
test.orders TO 'emloyee_Jack' @'localhost';
GRANT INSERT,
SELECT ON
test.lineitem TO 'employee_Jack' @'localhost';
CREATE ROLE 'employee_Mike' @'localhost';
GRANT INSERT,
SELECT ON
test.customer TO 'employee_Mike' @'localhost';
GRANT INSERT,
SELECT ON
test.nation TO 'employee_Mike' @'localhost';
GRANT INSERT,
SELECT ON
test.region TO 'employee_Mike' @'localhost';
employee_Marry:
employee_Jack:
employee_Mike:
③为各部门创建一个经理角色,相应角色对本部门的信息具有完全控制权限,对其他部门的信息具有查询权,经理有权给本部门职员分配权限:
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';
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';
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';
manager_David:
manager_Tom:
manager_Jerry:
(3)给用户分配权限:
①给各部门经理分配权限:
GRANT 'manager_David' @'localhost' TO 'David'@'localhost';
GRANT 'manager_Tom' @'localhost' TO 'Tom'` @'localhost';
GRANT 'manager_Jerry' @'localhost' TO 'Jerry' @'localhost';
David:
Tom:
Jerry:
②给各部门职员分配权限:
GRANT 'employee_Marry' @'localhost' TO 'Marry' @'localhost';
GRANT 'employee_Jack' @'localhost' TO 'Jack' @'localhost';
GRANT 'employee_Mike' @'localhost' TO 'Mike' @'localhost';
Marry:
Jack:
Mike:
(4)回收角色或用户权限:
①收回客户经理角色的客户信息查看权限:
对三个客户经理的其中一个进行回收权限:Jerry:
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 'employee_Mike'@'localhost';
REVOKE ALL PRIVILEGES ON test.nation
FROM 'employee_Mike'@'localhost';
REVOKE ALL PRIVILEGES ON test.region
FROM 'employee_Mike'@'localhost';
Employee_Mike:
(5)验证权限分配的正确性:
①以 David 用户名登录数据库,验证采购部门经理的权限:
mysql> SELECT * FROM mysql.tables_priv WHERE user='manager_David';
进一步验证David的采购部门经理权限,查询part、supplier、partsupp表的数据:
②验证Mike 的客户部门职员权限:
mysql> SELECT * FROM mysql.table_priv WHERE user='employee_Mike';
对和Mike同一级别的采购部门职员Marry的权限进行验证:
说明Mike的客户部门职员权限确实已经被回收了。
实验总结
(1)全局权限不能定义在某一个数据库上,因此授予用户全局权限的时候,应该使用“.”来表示授予用户权限的使用范围。
(2)在SQL语言中,用户(user)+地址(host)才表示一个用户。
(3)全局权限:
授予权限:
grant all privileges on . to ‘ua’@‘%’ with grant option;
收回权限:
revoke all privileges on . from ‘ua’@‘%’;
(4)%:表示所有IP都可以此账号
localhost:表示本地IP可用此账号
192.168.0.1:表示具体哪个IP地址可用此账号
192.168.0.%:表示具体哪个网段可用此账号
|(5)用户表格:
部门 经理 职员 口令
采购 David Merry 123456
销售 Tom Jack
客户管理 Jerry Mike
部门 | 经理 | 职员 | 口令 |
---|---|---|---|
采购 | David | Marry | 123456 |
销售 | Tom | Jack | 123456 |
客户经理 | Jerry | Mike | 123456 |
(5)mysql数据库:
mysql数据库中的user表:描述用户权限
mysql中的table_priv表:描述用户的权限所对应的表格,记录时间等