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