MySQL8.0中role的使用实践

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