麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践
828
2023-05-23
MySQL 实战笔记 第01期:MySQL 角色管理
角色 ( 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.proxies_priv 仅仅是对 Role 的模拟,和 *** 的角色还是有所不同的;官方称呼为 Role like。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。