MySQL8新增了角色(role)的概念，使賬號權(quán)限的管理施籍，更加靈活方便居扒。所謂角色，就是一些權(quán)限的集合丑慎。然后再把該集合授權(quán)給某個(gè)賬戶（往往是某一批賬戶喜喂，因?yàn)橘~號會(huì)綁定IP，不同的IP竿裂，雖然賬號名相同被視為不同賬號）玉吁，這樣當(dāng)我們需要對這些賬號減少或增加權(quán)限時(shí)，只需要修改權(quán)限集合（role）即可腻异，不用單個(gè)賬號多次修改进副。這確實(shí)使DBA的運(yùn)維輕松了不少。
下面我們看下role是如何使用的悔常。
創(chuàng)建角色
比如開發(fā)環(huán)境賬戶需要某庫的所有權(quán)限影斑，生產(chǎn)環(huán)境賬號往往需要增刪改查這些權(quán)限，我們可以單獨(dú)為這些權(quán)限建一個(gè)role.如果有讀寫分離机打，還可以建兩個(gè)讀和寫的role矫户。
create role 'app_dev','app_read','app_write';
mysql8[(none)]>show grants for 'app_dev';
+-------------------------------------+
| Grants for app_dev@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO `app_dev`@`%` |
+-------------------------------------+
創(chuàng)建角色時(shí)同樣可以綁定Host（默認(rèn)%）, 即角色名分為name + host兩部分，這和賬號沒有什么區(qū)別姐帚。
同樣創(chuàng)建的角色也和賬號一樣保存在mysql.user表中吏垮。通過查詢此表可以看到角色的信息：
mysql8[(none)]>select * from mysql.user;
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+
| % | app_dev | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | | Y | 2018-06-14 11:27:35 | NULL | Y | N | N | NULL | NULL |
| % | app_read | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | | Y | 2018-06-14 11:27:35 | NULL | Y | N | N | NULL | NULL |
| % | app_write | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | | Y | 2018-06-14 11:27:35 | NULL | Y | N | N | NULL | NULL |
| % | repl | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N
給角色授權(quán)
上面我們建了三個(gè)role,但這三個(gè)role都只有usage權(quán)限障涯，我們還需要對角色進(jìn)行授權(quán)罐旗。授權(quán)方式與給賬號授權(quán)是完全一樣的膳汪。
mysql8[(none)]>grant select , insert,update,delete on test.* to app_dev;
Query OK, 0 rows affected (0.02 sec)
mysql8[(none)]>grant select on test.* to app_read;
Query OK, 0 rows affected (0.10 sec)
mysql8[(none)]>show grants for app_read;
+--------------------------------------------+
| Grants for app_read@% |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `app_read`@`%` |
| GRANT SELECT ON `test`.* TO `app_read`@`%` |
+--------------------------------------------+
2 rows in set (0.00 sec)
mysql8[(none)]>show grants for app_dev;
+-------------------------------------------------------------------+
| Grants for app_dev@% |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_dev`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO `app_dev`@`%` |
+-------------------------------------------------------------------+
授權(quán)后我們看到各角色已經(jīng)具有了相應(yīng)的權(quán)限。
將角色授權(quán)于賬號
下面我們創(chuàng)建具體的賬號九秀，并將相應(yīng)的role授權(quán)給賬號遗嗽。
mysql8[(none)]>create user dev01 identified with mysql_native_password by 'dev01';
Query OK, 0 rows affected (0.04 sec)
mysql8[(none)]>grant app_dev to dev01;
Query OK, 0 rows affected (0.05 sec)
mysql8[(none)]>show grants for dev01;
+------------------------------------+
| Grants for dev01@% |
+------------------------------------+
| GRANT USAGE ON *.* TO `dev01`@`%` |
| GRANT `app_dev`@`%` TO `dev01`@`%` |
+------------------------------------+
我們創(chuàng)建了一個(gè)賬號dev01,并授權(quán)角色app_dev, 執(zhí)行show grants 查看權(quán)限時(shí)，看到的是角色鼓蜒，并不是具體的權(quán)限痹换。如果要查看具體的權(quán)限則需要這樣執(zhí)行show grants.
mysql8[(none)]>show grants for dev01 using app_dev;
+-----------------------------------------------------------------+
| Grants for dev01@% |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev01`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO `dev01`@`%` |
| GRANT `app_dev`@`%` TO `dev01`@`%` |
+-----------------------------------------------------------------+
通過使用using app_dev，會(huì)將賬號和角色的權(quán)限一并顯示都弹。
我們給角色app_dev添加create權(quán)限
mysql8[(none)]>grant create on test.* to app_dev;
Query OK, 0 rows affected (0.10 sec)
mysql8[(none)]>show grants for dev01 using app_dev;
+-------------------------------------------------------------------------+
| Grants for dev01@% |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev01`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `test`.* TO `dev01`@`%` |
| GRANT `app_dev`@`%` TO `dev01`@`%` |
+-------------------------------------------------------------------------+
3 rows in set (0.00 sec)
可以看到給角色添加權(quán)限后娇豫，dev01賬號也具有了create權(quán)限。
激活角色
上面的一些列操作貌似完美畅厢，dev02賬號可以使用了冯痢，其實(shí)還不行！使用dev01賬號登陸：
mysql> show grants for dev01 using app_dev;
+-------------------------------------------------------------------------+
| Grants for dev01@% |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev01`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `test`.* TO `dev01`@`%` |
| GRANT `app_dev`@`%` TO `dev01`@`%` |
+-------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)
發(fā)現(xiàn)權(quán)限也有框杜，但并看不到test庫浦楣，什么也無法執(zhí)行。為什么呢咪辱？角色沒有被激活
mysql> select current_role()
-> ;
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
執(zhí)行select current_role()發(fā)現(xiàn)是None. 所授權(quán)的角色并沒有被激活振劳，因此這個(gè)賬號 還是廢柴一個(gè)。
對賬號激活權(quán)限也很簡單
mysql8[(none)]>set default role all to dev01;
Query OK, 0 rows affected (0.06 sec)
這樣對dev01授予的所有角色都會(huì)被激活油狂。再使用dev01登陸就正常訪問了历恐。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.01 sec)
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `app_dev`@`%` |
+----------------+
1 row in set (0.00 sec)
可以看到當(dāng)前激活的角色為app_dev.
感覺流程太繁瑣了，都授權(quán)完了還要激活专筷，但MySQL8 提供已一個(gè)參數(shù)夹供，可以使角色在賬號登陸后自動(dòng)被激活。
mysql8[(none)]>show global variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF |
+-----------------------------+-------+
1 row in set (0.01 sec)
mysql8[(none)]>set global activate_all_roles_on_login=ON;
Query OK, 0 rows affected (0.00 sec)
把a(bǔ)ctivate_all_roles_on_login設(shè)置為ON就可以了仁堪。
mysql8[(none)]>create user query identified with mysql_native_password by 'query';
Query OK, 0 rows affected (0.04 sec)
mysql8[(none)]>grant app_read to query;
Query OK, 0 rows affected (0.06 sec)
mysql8[(none)]>show grants for query using app_read;
+-----------------------------------------+
| Grants for query@% |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `query`@`%` |
| GRANT SELECT ON `test`.* TO `query`@`%` |
| GRANT `app_read`@`%` TO `query`@`%` |
+-----------------------------------------+
3 rows in set (0.00 sec)
mysql8[(none)]>exit
使用query賬號登陸
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| query@% |
+----------------+
1 row in set (0.00 sec)
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `app_read`@`%` |
+----------------+
1 row in set (0.00 sec)
可以看到角色已被激活哮洽。
角色和賬號交互使用
角色和賬號沒有什么區(qū)別，可以把一個(gè)賬號當(dāng)做一個(gè)角色弦聂，將其授權(quán)給其它賬號鸟辅。詳見MySQL 官方文檔
CREATE USER 'u1';
CREATE ROLE 'r1';
GRANT SELECT ON db1.* TO 'u1';
GRANT SELECT ON db2.* TO 'r1';
CREATE USER 'u2';
CREATE ROLE 'r2';
GRANT 'u1', 'r1' TO 'u2';
GRANT 'u1', 'r1' TO 'r2';
這也太靈活了吧？ 我驚掉了下巴莺葫！
