use Sales;
SHOW GRANTS FOR 'David'@'localhost';
/* (1)创建用户 */
CREATE USER 'David' @'localhost' IDENTIFIED BY '111111';
CREATE USER 'Tom' @'localhost' IDENTIFIED BY '111111';
CREATE USER 'Kathy' @'localhost' IDENTIFIED BY '111111';
GRANT CREATE ROLE ON *.* TO 'David'@'localhost';
GRANT CREATE ROLE ON *.* TO 'Tom'@'localhost';
GRANT CREATE ROLE ON *.* TO 'Kathy'@'localhost';
GRANT CREATE USER ON *.* TO 'David'@'localhost';
GRANT CREATE USER ON *.* TO 'Tom'@'localhost';
GRANT CREATE USER ON *.* TO 'Kathy'@'localhost';
CREATE USER 'Jefery' @'localhost' IDENTIFIED BY '111111';
CREATE USER 'Jane' @'localhost' IDENTIFIED BY '111111';
CREATE USER 'Mike' @'localhost' IDENTIFIED BY '111111';
/* (2)创建角色并分配权限 */
/* 为各个部门分别创建一个查询角色,并分配相应的查询权限 */
CREATE ROLE PurchaseQueryRole;
GRANT SELECT ON TABLE Sales.part TO PurchaseQueryRole;
GRANT SELECT ON TABLE Sales.supplier TO PurchaseQueryRole;
GRANT SELECT ON TABLE Sales.partsupp TO PurchaseQueryRole;
CREATE ROLE SaleQueryRole;
GRANT SELECT ON TABLE Sales.orders TO SaleQueryRole;
GRANT SELECT ON TABLE Sales.lineitem TO SaleQueryRole;
CREATE ROLE CustomerQueryRole;
GRANT SELECT ON TABLE Sales.customer TO CustomerQueryRole;
GRANT SELECT ON TABLE Sales.nation TO CustomerQueryRole;
GRANT SELECT ON TABLE Sales.region TO CustomerQueryRole;
/* 为各个部门分别创建一个职员角色,对本部门信息具有查看、插入权限 */
CREATE ROLE PurchaseEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.part TO PurchaseEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.supplier TO PurchaseEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.partsupp TO PurchaseEmployeeRole;
CREATE ROLE SaleEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.orders TO SaleEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.lineitem TO SaleEmployeeRole;
CREATE ROLE CustomerEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.customer TO CustomerEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.nation TO CustomerEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.region TO CustomerEmployeeRole;
/* 为各部门创建一个经理角色,相应角色对本部门的信息具有完全控制权限,
对其他部门的信息具有查询权。经理有权给本部门职员分配权限。 */
CREATE ROLE PurchaseManagerRole;
GRANT CREATE ROLE ON TABLE Sales.* TO PurchaseManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.part TO PurchaseManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.partsupp TO PurchaseManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.supplier TO PurchaseManagerRole;
GRANT SaleQueryRole TO PurchaseManagerRole;
GRANT CustomerQueryRole TO PurchaseManagerRole;
CREATE ROLE SaleManagerRole;
GRANT CREATE ROLE ON TABLE *.* TO SaleManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.orders TO SaleManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.lineitem TO SaleManagerRole;
GRANT PurchaseQueryRole TO SaleManagerRole;
GRANT CustomerQueryRole TO SaleManagerRole;
CREATE ROLE CustomerManagerRole;
GRANT CREATE ROLE ON TABLE *.* TO CustomerManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.customer TO CustomerManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.region TO CustomerManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.nation TO CustomerManagerRole;
GRANT PurchaseQueryRole TO CustomerManagerRole;
GRANT SaleQueryRole TO CustomerManagerRole;
/* (3)给用户分配权限 */
/* 给各部门经理分配权限。 */
GRANT PurchaseManagerRole TO David@localhost WITH ADMIN OPTION;
GRANT SaleManagerRole TO Tom@localhost WITH ADMIN OPTION;
GRANT CustomerManagerRole TO Kathy@localhost WITH ADMIN OPTION;
/* 给各部门职员分配权限。 */
GRANT PurchaseEmployeeRole TO Jefery@localhost;
GRANT SaleEmployeeRole TO Jane@localhost;
GRANT CustomerEmployeeRole TO Mike@localhost;
/* (4)回收角色或用户权限 */
/* 收回客户经理角色的销售信息查看权限 */
REVOKE SaleQueryRole FROM CustomerManagerRole;
/* 回收 Mike的客户部门职员权限 */
REVOKE CustomerEmployeeRole FROM Mike@localhost;
/* (5)验证权限分配正确性 */
/* 以David用户名登录数据库,验证采购部门经理的权限。 */
- 用以上方法创建用户后,打开命令行,输入
mysql -uDavid -p111111
进入数据库,输入show databases;
只有一个数据库名字叫做information_schema
,没有原来的数据库。 - 使用
select * from sales.part
后提示SELECT command denied to user 'Tom'@'localhost' for table 'orders'
use sales;
提示Access denied for user 'Tom'@'localhost' to database 'sales'。- 输入
show grants for Tom@localhost;
后命令行输出
+--------------------------------------------------------------------+
| Grants for Tom@localhost |
+--------------------------------------------------------------------+
| GRANT CREATE USER, CREATE ROLE ON *.* TO `Tom`@`localhost` |
| GRANT `SaleManagerRole`@`%` TO `Tom`@`localhost` WITH ADMIN OPTION |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
- 有没有哪位大神帮忙解决一下!多谢了!
- 下面是数据库用户和表的截图
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…