mysql从8.0版本开始提供了角色，通过角色可以方便的进行权限的管理。

但相对oracle来说有一些差异，包括：

1.MySQL中用户的角色默认是非活动的（inactive），需要为用户修改默认角色，才能登录后直接使用

2.添加某个逻辑库中一张表的查询权限后，show database、information_schema.tables中可以看到所有逻辑库下的所有表（但select该表仍会报错），如果不使用角色而直接授权则无此问题。

3.MySQL中用户、库、角色都是相对独立的，如果遇到紧急情况，比如某个账号的sql出现严重性能问题、升级或迁移等操作需要将用户或角色的增删改权限临时禁用，也可以通过revoke来实现。

mysql>  CREATE ROLE app_read;

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON test.account TO app_read;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'read_user1' IDENTIFIED BY 'read_user1pass';

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for read_user1;

+----------------------------------------+

| Grants for read_user1@%                |

+----------------------------------------+

| GRANT USAGE ON *.* TO `read_user1`@`%` |

+----------------------------------------+

1 row in set (0.00 sec)

mysql> GRANT app_read TO read_user1;

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT ON test.account TO app_read;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for read_user1 using app_read;

+------------------------------------------------------+

| Grants for read_user1@%                              |

+------------------------------------------------------+

| GRANT USAGE ON *.* TO `read_user1`@`%`               |

| GRANT SELECT ON `test`.`account` TO `read_user1`@`%` |

| GRANT `app_read`@`%` TO `read_user1`@`%`             |

+------------------------------------------------------+

3 rows in set (0.00 sec)

登录到read_user1账号：

# mysql -uread_user1 -P3307 -h127.0.0.1 -p

mysql> show grants ;

+------------------------------------------+

| Grants for read_user1@%                  |

+------------------------------------------+

| GRANT USAGE ON *.* TO `read_user1`@`%`   |

| GRANT `app_read`@`%` TO `read_user1`@`%` |

+------------------------------------------+

2 rows in set (0.00 sec)

mysql> select current_role();

+----------------+

| current_role() |

+----------------+

| NONE           |

+----------------+

1 row in set (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

+--------------------+

1 row in set (0.00 sec)

mysql> select count(1) from test.account;

ERROR 1142 (42000): SELECT command denied to user 'read_user1'@'localhost' for table 'account'

mysql> exit

使用root账号为read_user1设置默认（活动）角色：

mysql> set default role all to read_user1;

Query OK, 0 rows affected (0.00 sec)

再次用read_user1查看：

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| test               |

+--------------------+

2 rows in set (0.00 sec)

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| a              |

| a2             |

| account        |

| sample         |

| sample2        |

+----------------+

5 rows in set (0.00 sec)

mysql> select count(1) from account;

+----------+

| count(1) |

+----------+

|     9999 |

+----------+

1 row in set (0.01 sec)

mysql> select count(1) from a;

ERROR 1142 (42000): SELECT command denied to user 'read_user1'@'localhost' for table 'a'

mysql> select table_schema,count(1) from information_schema.tables group by table_schema;

+--------------------+----------+

| TABLE_SCHEMA       | count(1) |

+--------------------+----------+

| information_schema |       79 |

| mysql              |       36 |

| sbtest             |        2 |

| sys                |      101 |

| test               |        5 |

+--------------------+----------+

5 rows in set (0.00 sec)

如果收回角色权限

mysql> set default role none to read_user1;

Query OK, 0 rows affected (0.00 sec)

再次登录后，就看不到information_schema以外的其他逻辑库了

mysql> select table_schema,count(1) from information_schema.tables group by table_schema;

+--------------------+----------+

| TABLE_SCHEMA       | count(1) |

+--------------------+----------+

| information_schema |       79 |

+--------------------+----------+

1 row in set (0.00 sec)

这时root再给read_user1增加表查询权限

mysql> grant select on sbtest.sbtest1 to read_user1;

Query OK, 0 rows affected (0.00 sec)

read_user1只能看到对应的表:

mysql> select table_schema,count(1) from information_schema.tables group by table_schema;

+--------------------+----------+

| TABLE_SCHEMA       | count(1) |

+--------------------+----------+

| information_schema |       79 |

| sbtest             |        1 |

+--------------------+----------+

2 rows in set (0.00 sec)

mysql> use sbtest;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+------------------+

| Tables_in_sbtest |

+------------------+

| sbtest1          |

+------------------+

1 row in set (0.01 sec)

其他：

1、区分以下两个语句

set default role 语句用于定义账号的默认活动角色

set role default 语句用于定义当前账号在当前会话中的默认角色

2、通过系统参数activate_all_roles_on_login 、 mandatory_roles 可以控制某些角色登陆时生效/全局生效

参考文档：

https://dev.mysql.com/doc/search/?d=201&p=1&q=role