Role功能可以說是一個期待已有的功能,這從它的Worklog號(WL#988)就可以看出來,這是個相當早並且呼聲很高的需求了。
所謂Role,可以認為是一個權限的集合,這個集合有一個統一的名字,就是Role名,你可以為多個賬戶賦予統一的某個Role的權限,而權限的修改可以直接通過修改Role來實現,而無需每個賬戶逐一GRANT權限,大大方便了運維和管理。
Role可以被創建,修改和刪除,並作用到其所屬於的賬戶上。
舉個簡單的例子。創建如下測試表
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
mysql> use testdb; create table t1 (a int, b int, primary key(a));
Database changed
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (1,2);
Query OK, 1 row affected (0.00 sec)
創建Role,擁有t1表的查詢權限:
mysql> create role priv_t1;
Query OK, 0 rows affected (0.00 sec)
mysql> grant select on testdb.t1 to 'priv_t1';
Query OK, 0 rows affected (0.00 sec)
創建一個賬戶,並將role的權限賦給它
mysql> create user 'rw_user1'@'%' identified by 'xxx';
Query OK, 0 rows affected (0.00 sec)
mysql> grant 'priv_t1' to 'rw_user1'@'%';
Query OK, 0 rows affected (0.00 sec)
以rw_user1登錄
---- 查看權限
mysql> show grants;
+---------------------------------------+
| Grants for rw_user1@% |
+---------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`%` |
| GRANT `priv_t1`@`%` TO `rw_user1`@`%` |
+---------------------------------------+
2 rows in set (0.00 sec)
## 需要加using "role名"才會展開權限
mysql> show grants for 'rw_user1'@'%' using priv_t1;
+-------------------------------------------------+
| Grants for rw_user1@% |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`%` |
| GRANT SELECT ON `testdb`.`t1` TO `rw_user1`@`%` |
| GRANT `priv_t1`@`%` TO `rw_user1`@`%` |
+-------------------------------------------------+
3 rows in set (0.00 sec)
然而此時並不能直接獲得t1表的查詢權限, 你需要手動進行選擇哪些role在賬戶連接上來時被激活,如下:
mysql> select * from testdb.t1;
ERROR 1142 (42000): SELECT command denied to user 'rw_user1'@'localhost' for table 't1'
mysql> SET DEFAULT ROLE ALL TO 'rw_user1'@'%';
Query OK, 0 rows affected (0.00 sec)
--- 重新登錄生效
mysql> select user();
+--------------------+
| user() |
+--------------------+
| rw_user1@localhost |
+--------------------+
1 row in set (0.00 sec)
mysql> select * from testdb.t1;
+---+------+
| a | b |
+---+------+
| 1 | 2 |
+---+------+
1 row in set (0.00 sec)
-- SET ROLE語法參閱官方文檔:
-- http://dev.mysql.com/doc/refman/8.0/en/set-default-role.html
修改role的權限,會直接作用到對應的賬戶上:
--- 增加insert權限
--- login as root
mysql> grant insert on testdb.t1 to 'priv_t1';
Query OK, 0 rows affected (0.00 sec)
--- login as rw_user1
mysql> insert into testdb.t1 values (2,3);
Query OK, 1 row affected (0.00 sec)
--- 刪除insert權限
--- login as root
mysql> revoke insert on testdb.t1 from 'priv_t1';
Query OK, 0 rows affected (0.00 sec)
--- login as rw_user1
mysql> insert into testdb.t1 values (3,4);
ERROR 1142 (42000): INSERT command denied to user 'rw_user1'@'localhost' for table 't1'
增加了兩個系統表來維護Role信息,一個是mysql.default_roles表,用於展示賬戶使用的默認role信息,一個是role_edges,用於展示已創建的role信息
mysql> select * from default_roles;
+------+----------+-------------------+-------------------+
| HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+----------+-------------------+-------------------+
| % | rw_user1 | % | priv_t1 |
+------+----------+-------------------+-------------------+
1 row in set (0.00 sec)
mysql> select * from role_edges;
+-----------+-----------+---------+----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+----------+-------------------+
| % | priv_t1 | % | rw_user1 | N |
+-----------+-----------+---------+----------+-------------------+
1 row in set (0.00 sec)
新增函數用於顯示當前賬戶使用的role:
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `priv_t1`@`%` |
+----------------+
1 row in set (0.00 sec)