创建角色之后记得激活，可以把mysql设置为自动激活，（应该是一个用户对应一个全局变量act…,因为我做的时候是在创建角色的时候改过变量为ON，写这篇的时候直接在root下登录MySQL，发现这个变量是OFF）

此图出处点这里

show variables like 'activate_all_roles_on_login';
set global activate_all_roles_on_login = on;

创建用户：

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

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

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

这样的话有error：ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

把权限改到全局就好了，创建用户或者角色的权限需要赋予所有数据库？

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

SELECT Create_user_priv,Create_role_priv
 FROM mysql.user
  WHERE user='David';

改成这样就好了

然后

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

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

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

 SELECT user,Create_user_priv,Create_role_priv
  FROM mysql.user
   WHERE user='Jerry' OR user='Tom';

 SELECT user,Create_user_priv,Create_role_priv
  FROM mysql.user
   WHERE user='Jerry' OR user='Tom' OR user='David';

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

CREATE USER 'Marry'@'localhost' IDENTIFIED BY 'Marry';
CREATE USER 'Jack'@'localhost' IDENTIFIED BY 'Jack';
CREATE USER 'Mike'@'localhaot' IDENTIFIED BY 'Mike';

创建角色并分配权限：

各个部门分别创建一个查询角色，并分配相应的查询权限。采购对应：零件表、供应商表、零件供应联系表 销售对应：订单表和订单明细表 客户对应：顾客表、国家表、地区表

采购对应：零件表、供应商表、零件供应联系表

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';

SHOW GRANTS FOR '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';

SHOW GRANTS FOR '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';

SHOW GRANTS FOR 'SELECT_Jerry'@'localhost';

验证角色赋予用户后的情况

CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
GRANT 'SELECT_David'@'localhost' TO 'test'@'localhost';

select * from part where partkey=55 \G;
update part set type=17 where partkey=55;

ERROR 1142 (42000): UPDATE command denied to user ‘test’@‘localhost’ for table ‘part’

show 角色赋予用户后的情况

SELECT CURRENT_ROLE();
SHOW GRANTS FOR 'test'@'localhost';

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

CREATE ROLE 'WORKER_David'@'localhost';

GRANT INSERT,SELECT ON test.part TO 'WORKER_David'@'localhost';
GRANT INSERT,SELECT ON test.partsupp TO 'WORKER_David'@'localhost';
GRANT INSERT,SELECT ON test.supplier TO 'WORKER_David'@'localhost';

SHOW GRANTS FOR 'WORKER_David'@'localhost';

CREATE ROLE 'WORKER_Tom'@'localhost';

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

SHOW GRANTS FOR 'WORKER_Tom'@'localhost';

CREATE ROLE 'WORKER_Jerry'@'localhost';

GRANT INSERT,SELECT ON test.nation  TO 'WORKER_Jerry'@'localhost';
GRANT INSERT,SELECT ON test.region  TO 'WORKER_Jerry'@'localhost';
GRANT INSERT,SELECT ON test.customer TO 'WORKER_Jerry'@'localhost';

SHOW GRANTS FOR 'WORKER_Jerry'@'localhost';

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

WITH GRANT OPTION 不能赋予角色

???经理有权给本部门职员分配权限???

CREATE ROLE 'Manager_David'@'localhost';

GRANT ALL ON test.part TO 'Manager_David'@'localhost';
GRANT ALL ON test.partsupp TO 'Manager_David'@'localhost';
GRANT ALL ON test.supplier TO 'Manager_David'@'localhost';

SHOW GRANTS FOR 'Manager_David'@'localhost';

CREATE ROLE 'Manager_Tom'@'localhost';

GRANT ALL ON test.orders TO 'Manager_Tom'@'localhost';
GRANT ALL ON test.lineitem TO 'Manager_Tom'@'localhost';


SHOW GRANTS FOR 'Manager_Tom'@'localhost';

CREATE ROLE 'Manager_Jerry'@'localhost';

GRANT ALL ON test.nation TO 'Manager_Jerry'@'localhost';
GRANT ALL ON test.region TO 'Manager_Jerry'@'localhost';
GRANT ALL ON test.customer TO 'Manager_Jerry'@'localhost';


SHOW GRANTS FOR 'Manager_Jerry'@'localhost';

给用户分配权限

给各部门经理分配权限。

GRANT 'Manager_David'@'localhost' TO 'David'@'localhost';
GRANT 'Manager_Tom'@'localhost' TO 'Tom'@'localhost';
GRANT 'Manager_Jerry'@'localhost' TO 'Jerry'@'localhost';

SHOW GRANTS FOR 'David'@'localhost';
SHOW GRANTS FOR 'Tom'@'localhost';
SHOW GRANTS FOR 'Jerry'@'localhost';

给各部门职员分配权限。

GRANT 'WORKER_David'@'localhost' TO 'Marry'@'localhost';
GRANT 'WORKER_Tom'@'localhost' TO 'Jack'@'localhost';
GRANT 'WORKER_Jerry'@'localhost' TO 'Mike'@'localhost';

SHOW GRANTS FOR 'Marry'@'localhost';
SHOW GRANTS FOR 'Jack'@'localhost';
SHOW GRANTS FOR 'Mike'@'localhost';

回收角色或用户权限

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

角色

一开始读错题了，想回收Jerry，客户经理用户的权限，发现

select * from mysql.tables_priv where user='Jerry'\G;
# Empty set (0.00 sec)
#ERROR:
#No query specified

找不到他对表的的权限

 select * from mysql.tables_priv where user='Manager_Jerry'\G;

而对经理角色是有相应对表的权限的，如下图：

所以应该这样写

SHOW GRANTS FOR 'Manager_Jerry'@'localhost';

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

SELECT Table_name,Table_priv FROM mysql.tables_priv WHERE user='Manager_Jerry' \G;

验证结果

quit;
#退出root登录
mysql -u Jerry -p
Enter password: ******
#从Jerry登录，前面改变了角色Manager_Jerry，而此角色是已经被赋予Jerry的
select * from customer where custkey=100 \G;
#ERROR 1142 (42000): SELECT command denied to user 'Jerry'@'localhost' for table 'customer'
#ERROR:
#No query specified
# 验证成功，实际截图如下：

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

 select * from mysql.tables_priv where user='Mike';
 # Empty set (0.01 sec)
 # Mike用户自己对表的权限查不到
 # 所以回收被赋予Mike的角色WORKER_Jerry的权限
  select * from mysql.tables_priv where user='WORKER_Jerry';
 # 先查询它的权限有哪些

REVOKE SELECT,INSERT ON test.customer FROM 'WORKER_Jerry'@'localhost';
REVOKE SELECT,INSERT ON test.nation FROM 'WORKER_Jerry'@'localhost';
REVOKE SELECT,INSERT ON test.region FROM 'WORKER_Jerry'@'localhost';

select * from mysql.tables_priv where user='WORKER_Jerry';
# 已经为空

验证权限分配的正确性

在root下查询Manager_David 角色的权限

SELECT * FROM mysql.tables_priv WHERE user='Manager_David';

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

SELECT * FROM part WHERE partkey=100 \G;
SELECT * FROM partsupp WHERE partkey=100 \G;
SELECT * FROM supplier WHERE suppkey=100 \G;

验证Mike 的客户部门职员权限。

==Mike的权限在前面已经被回收，验证结果应该是无法执行，如下图 : ==

mysql> quit;
Bye

C:\Users\15328>mysql -u Mike -p
Enter password: ****

mysql> use test;
#ERROR 1044 (42000): Access denied for user 'Mike'@'localhost' to database 'test'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

mysql> quit;
Bye

收回权限之后根本找不到test数据库

再来一个佐证：

查看和Mike同等级的Jack：

C:\Users\15328>mysql -u Jack -p
Enter password: ****

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.01 sec)

没有被回收角色权限的用户Jack就可以访问到test，验证成功