这篇文章主要讲解了“如何使用MySQL角色管理”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“如何使用MySQL角色管理”吧！

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-ujackWelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis249Serverversion:5.7.28-logMySQLCommunityServer(GPL)Copyright(c)2000,2019,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>select*fromtest.ssdlimit1;+---+------+------+|a|b|c|+---+------+------+|1|NULL|NULL|+---+------+------+1rowinset(0.01sec)

感谢各位的阅读，以上就是“如何使用MySQL角色管理”的内容了，经过本文的学习后，相信大家对如何使用MySQL角色管理这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！