Mysql 5.7.18 Using MySQL proxies_priv to implement similar user group management

Use MySQL proxies_priv (simulated role) to implement similar user group management

Roles can be used to manage users in batches. Users with the same role have the same permissions.

MySQL 5.7.X and later can simulate the role function, which can be achieved through mysql.proxies_priv simulation

1. Configure proxy

mysql> show variables like "%proxy%"; #Check whether the current proxy is enabled. The following figure shows that it is not enabled. 

mysql> set global check_proxy_users = on; #Enable proxy. The following figure shows that proxy is enabled.mysql> set global mysql_native_password_proxy_users = on;

mysql> exit
Bye #The above parameters are invalid for the current session. You need to log out and log in again, or set them directly in my.cnf

2. Create a user

mysql> create user will_dba; #Similar groupmysql> create user 'will';
mysql> create user 'tom'; 
#The password is not set. If you need to set a password, add identified by '123'

3. Map the permissions of will_dba to will,tom

mysql> grant proxy on will_dba to will;
mysql> grant proxy on will_dba to tom;

4. Grant actual permissions to will_dba (simulated role)

mysql> grant select on *.* to will_dba;

5. Check will_dba's permissions

mysql> show grants for will_dba;

6. Check the permissions of will and tom

mysql> show grants for will; 

mysql> show grants for tom;

7. Check the permissions of proxies_priv

mysql> select * from mysql.proxies_priv;

8. Verification

Use will and tom users to view the database

[root@test-1 ~]# mysql -utom -p
mysql> show databases; #We did not grant permissions to user tom before, but he can view it here mysql> show tables;
mysql> select * from user\G

mysql.proxies_priv is just a simulation of Role, which is different from Oracle's role. The official name is Role like

MySQL5.6.X needs to install a plug-in to simulate the Role function. For specific methods, please refer to:

https://dev.mysql.com/doc/refman/5.6/en/proxy-users.html

https://dev.mysql.com/doc/refman/5.6/en/pluggable-authentication.html

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.