角色 ( Role ) 可以用来批量管理用户,同一个角色下的用户,拥有相同的权限。那 MySQL 数据库是否也有这样的功能呢 ?答案是肯定的。MySQL 5.7.X 可以通过 mysql.proxies_priv 来模拟角色 (Role) 的功能。下面让我们来实验一下(测试的版本 MySQL 5.7.28):
1 配置 proxy
- mysql> show variables like "%proxy%"; #查看当前proxy是否开启,OFF 表示没有开启
- +-----------------------------------+-------+
- | Variable_name | Value |
- +-----------------------------------+-------+
- | check_proxy_users | OFF |
- | mysql_native_password_proxy_users | OFF |
- | proxy_user | |
- | sha256_password_proxy_users | OFF |
- +-----------------------------------+-------+
- 4 rows in set (0.02 sec)
- mysql> set global check_proxy_users =on;
- Query OK, 0 rows affected (0.00 sec)
- mysql> set global mysql_native_password_proxy_users = on;
- Query OK, 0 rows affected (0.01 sec)
- mysql> exit
以上设置参数,对当前会话无效,需要退出后重新登录,或直接设置到 my.cnf 中去;
2 创建角色和用户
- mysql> create user role_dba;
- Query OK, 0 rows affected (1.03 sec)
- mysql> create user 'jack';
- Query OK, 0 rows affected (0.01 sec)
- mysql> create user 'mary';
- Query OK, 0 rows affected (0.01 sec)
用户为设置密码,如需密码可以使用 identified by '####' 设置;
3 权限映射
将 role_dba 的权限映射( map )到 jack 、mary
- mysql> grant proxy on role_dba to jack;
- Query OK, 0 rows affected (0.02 sec)
- mysql> grant proxy on role_dba to mary;
- Query OK, 0 rows affected (0.01 sec)
4 给用户赋权
给 role_dba 赋权(模拟 role 赋权)
- mysql> grant select on *.* to role_dba;
- Query OK, 0 rows affected (0.01 sec)
- mysql> show grants for role_dba;
- +---------------------------------------+
- | Grants for role_dba@% |
- +---------------------------------------+
- | GRANT SELECT ON *.* TO 'role_dba'@'%' |
- +---------------------------------------+
- 1 row in set (0.00 sec)
- mysql> show grants for jack;
- +---------------------------------------------+
- | Grants for jack@% |
- +---------------------------------------------+
- | GRANT USAGE ON *.* TO 'jack'@'%' |
- | GRANT PROXY ON 'role_dba'@'%' TO 'jack'@'%' |
- +---------------------------------------------+
- 2 rows in set (0.00 sec)
- mysql> show grants for mary;
- +---------------------------------------------+
- | Grants for mary@% |
- +---------------------------------------------+
- | GRANT USAGE ON *.* TO 'mary'@'%' |
- | GRANT PROXY ON 'role_dba'@'%' TO 'mary'@'%' |
- +---------------------------------------------+
- 2 rows in set (0.00 sec)
5 查看 mysql.proxies_priv
- mysql> select * from mysql.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 rows in set (0.01 sec)
6 验证
- $ mysql -h 127.0.0.1 -u jack
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 249
- Server version: 5.7.28-log MySQL Community Server (GPL)
- Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql> select * from test.ssd limit 1;
- +---+------+------+
- | a | b | c |
- +---+------+------+
- | 1 | NULL | NULL |
- +---+------+------+
- 1 row in set (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
idea官方推送了2020.2.4版本的更新,那么大家最关心的问题来了,之前激活idea202...
CKeditor,以前叫FCKeditor,已经使用过好多年了,功能自然没的说。最近升级到3....
本文转载自微信公众号「SQL数据库」,作者丶平凡世界 。转载本文请联系开发公众...
大家好,我是狂聊君。 今天来聊一聊 Mysql 缓存池原理。 提纲附上,话不多说,直...
来源:DeepenStudy 漏洞文件:js.asp % Dimoblog setoblog=newclass_sys oblog.a...
问题:我们在做flex的开发中,如果用到别人搭建好的框架,而别人的server名称往...
本文实例讲述了AJAX+Servlet实现的数据处理显示功能。分享给大家供大家参考,具...
本文转载自微信公众号「SH的全栈笔记」,作者SH。转载本文请联系SH的全栈笔记公...
在Flash Player 10.1及以上版本中,adobe新增了全局错误处理程序UncaughtErrorEv...
前言 项目开发中不管是前台还是后台都会遇到烦人的null,数据库表中字段允许空值...