角色 ( Role ) 可以用来批量管理用户,同一个角色下的用户,拥有相同的权限。那 MySQL 数据库是否也有这样的功能呢 ?答案是肯定的。MySQL 5.7.X 可以通过 mysql.proxies_priv 来模拟角色 (Role) 的功能。下面让我们来实验一下(测试的版本 MySQL 5.7.28):
1 配置 proxy
mysql> show variableslike"%proxy%"; #查看当前proxy是否开启,OFF表示没有开启
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| check_proxy_users | OFF|
| mysql_native_password_proxy_users | OFF|
| proxy_user | |
| sha256_password_proxy_users | OFF|
+-----------------------------------+-------+
4 rowsinset(0.02 sec)
mysql> setglobalcheck_proxy_users =on;
Query OK, 0 rowsaffected (0.00 sec)
mysql> setglobalmysql_native_password_proxy_users =on;
Query OK, 0 rowsaffected (0.01 sec)
mysql> exit
以上设置参数,对当前会话无效,需要退出后重新登录,或直接设置到 my.cnf 中去;
2 创建角色和用户
mysql>createuserrole_dba;
Query OK, 0 rowsaffected (1.03 sec)
mysql> createuser'jack';
Query OK, 0 rowsaffected (0.01 sec)
mysql> createuser'mary';
Query OK, 0 rowsaffected (0.01 sec)
用户为设置密码,如需密码可以使用 identified by '####' 设置;
3 权限映射
将 role_dba 的权限映射( map )到 jack 、mary
mysql>grantproxyonrole_dbatojack;
Query OK, 0 rowsaffected (0.02 sec)
mysql> grantproxyonrole_dbatomary;
Query OK, 0 rowsaffected (0.01 sec)
4 给用户赋权
给 role_dba 赋权(模拟 role 赋权)
mysql>grantselecton*.*torole_dba;
Query OK, 0 rowsaffected (0.01 sec)
mysql> show grants forrole_dba;
+---------------------------------------+
| Grants forrole_dba@% |
+---------------------------------------+
| GRANTSELECTON*.*TO'role_dba'@'%'|
+---------------------------------------+
1 row inset(0.00 sec)
mysql> show grants forjack;
+---------------------------------------------+
| Grants forjack@% |
+---------------------------------------------+
| GRANTUSAGEON*.*TO'jack'@'%'|
| GRANTPROXYON'role_dba'@'%'TO'jack'@'%'|
+---------------------------------------------+
2 rowsinset(0.00 sec)
mysql> show grants formary;
+---------------------------------------------+
| Grants formary@% |
+---------------------------------------------+
| GRANTUSAGEON*.*TO'mary'@'%'|
| GRANTPROXYON'role_dba'@'%'TO'mary'@'%'|
+---------------------------------------------+
2 rowsinset(0.00 sec)
5 查看 mysql.proxies_priv
mysql>select*frommysql.proxies_priv;
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
| Host | User| Proxied_host | Proxied_user | With_grant | Grantor |Timestamp|
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
| localhost | root | | | 1 | boot@connecting host | 0000-00-00 00:00:00 |
| % | will | % | will_dba | 0 | root@localhost | 0000-00-00 00:00:00 |
| % | tom | % | will_dba | 0 | root@localhost | 0000-00-00 00:00:00 |
| % | jack | % | role_dba | 0 | root@localhost | 0000-00-00 00:00:00 |
| % | mary | % | role_dba | 0 | root@localhost | 0000-00-00 00:00:00 |
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
5 rowsinset(0.01 sec)
6 验证
$ mysql -h 127.0.0.1 -u jack
Welcome tothe MySQL monitor. Commandsendwith;or\g.
Your MySQL connectionidis249
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/orits affiliates.Allrights reserved.
Oracle isa registered trademarkofOracle Corporationand/orits
affiliates. Other names may be trademarks oftheir respective
owners.
Type 'help;'or'\h'forhelp. Type'\c'toclear thecurrentinput statement.
mysql> select*fromtest.ssd limit 1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | NULL|NULL|
+---+------+------+
1 row inset(0.01 sec)
mysql.proxies_priv 仅仅是对 Role 的模拟,和 Oracle 的角色还是有所不同的;官方称呼为 Role like。
MySQL 8.0 正式增加了 role 功能,有兴趣的同学可以自行了解。MySQL 5.6.X 模拟 Role 功能需要安装插件,具体方法可参考:https://dev.mysql.com/doc/refman/5.6/en/proxy-users.htmlhttps://dev.mysql.com/doc/refman/5.6/en/pluggable-authentication.html
【编辑推荐】
【责任编辑:庞桂玉 TEL:(010)68476606】
点赞 0