角色 ( Role ) 可以用来批量管理用户，同一个角色下的用户，拥有相同的权限。那 MySQL 数据库是否也有这样的功能呢 ?答案是肯定的。MySQL 5.7.X 可以通过 mysql.proxies_priv 来模拟角色 (Role) 的功能。下面让我们来实验一下(测试的版本 MySQL 5.7.28)：

1 配置 proxy

mysql>showvariableslike"%proxy%";#查看当前proxy是否开启，OFF表示没有开启
+-----------------------------------+-------+
|Variable_name|Value|
+-----------------------------------+-------+
|check_proxy_users|OFF|
|mysql_native_password_proxy_users|OFF|
|proxy_user||
|sha256_password_proxy_users|OFF|
+-----------------------------------+-------+
4rowsinset(0.02sec)

mysql>setglobalcheck_proxy_users=on;
QueryOK,0rowsaffected(0.00sec)

mysql>setglobalmysql_native_password_proxy_users=on;
QueryOK,0rowsaffected(0.01sec)

mysql>exit

以上设置参数，对当前会话无效，需要退出后重新登录，或直接设置到 my.cnf 中去;

2 创建角色和用户

mysql>createuserrole_dba;
QueryOK,0rowsaffected(1.03sec)

mysql>createuser'jack';
QueryOK,0rowsaffected(0.01sec)

mysql>createuser'mary';
QueryOK,0rowsaffected(0.01sec)

用户为设置密码，如需密码可以使用 identified by ‘####’ 设置;

3 权限映射

将 role_dba 的权限映射( map )到 jack 、mary

mysql>grantproxyonrole_dbatojack;
QueryOK,0rowsaffected(0.02sec)

mysql>grantproxyonrole_dbatomary;
QueryOK,0rowsaffected(0.01sec)

4 给用户赋权

给 role_dba 赋权(模拟 role 赋权)

mysql>grantselecton*.*torole_dba;
QueryOK,0rowsaffected(0.01sec)

mysql>showgrantsforrole_dba;
+---------------------------------------+
|Grantsforrole_dba@%|
+---------------------------------------+
|GRANTSELECTON*.*TO'role_dba'@'%'|
+---------------------------------------+
1rowinset(0.00sec)

mysql>showgrantsforjack;
+---------------------------------------------+
|Grantsforjack@%|
+---------------------------------------------+
|GRANTUSAGEON*.*TO'jack'@'%'|
|GRANTPROXYON'role_dba'@'%'TO'jack'@'%'|
+---------------------------------------------+
2rowsinset(0.00sec)

mysql>showgrantsformary;
+---------------------------------------------+
|Grantsformary@%|
+---------------------------------------------+
|GRANTUSAGEON*.*TO'mary'@'%'|
|GRANTPROXYON'role_dba'@'%'TO'mary'@'%'|
+---------------------------------------------+
2rowsinset(0.00sec)

5 查看 mysql.proxies_priv

mysql>select*frommysql.proxies_priv;
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
|Host|User|Proxied_host|Proxied_user|With_grant|Grantor|Timestamp|
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
|localhost|root|||1|boot@connectinghost|0000-00-0000:00:00|
|%|will|%|will_dba|0|root@localhost|0000-00-0000:00:00|
|%|tom|%|will_dba|0|root@localhost|0000-00-0000:00:00|
|%|jack|%|role_dba|0|root@localhost|0000-00-0000:00:00|
|%|mary|%|role_dba|0|root@localhost|0000-00-0000:00:00|
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
5rowsinset(0.01sec)

6 验证

$mysql-h127.0.0.1-ujack

WelcometotheMySQLmonitor.Commandsendwith;or\g.

YourMySQLconnectionidis249

Serverversion:5.7.28-logMySQLCommunityServer(GPL)

Copyright(c)2000,2019,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits

affiliates.Othernamesmaybetrademarksoftheirrespective

owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>select*fromtest.ssdlimit1;

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

|a|b|c|

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

|1|NULL|NULL|

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

1rowinset(0.01sec)

mysql.proxies_priv 仅仅是对 Role 的模拟，和 Oracle 的角色还是有所不同的;官方称呼为 Role like。

MySQL 8.0 正式增加了 role 功能，有兴趣的同学可以自行了解。MySQL 5.6.X 模拟 Role 功能需要安装插件，具体方法可