这篇文章主要讲解了“如何使用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角色管理这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!