Technology Sharing | MySQL 8.0 Proxy User Use

Author: Yang Taotao

Senior database expert, specializing in MySQL for more than ten years. Good at backup and recovery related to open source databases such as MySQL, PostgreSQL, MongoDB, SQL tuning, monitoring operation and maintenance, and high-availability architecture design. Currently working at Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial companies.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

background

Someone asked me a question about how to use MySQL PROXY users.

The original question was this: After the MySQL version was upgraded from 5.5 to 8.0, how could proxy users not be able to use it? I used it according to the method written on your blog, but after the upgrade, installing the plug-in prompted the following error:

mysql:(none)>install plugin test_plugin_server soname 'auth_test_plugin.so';
ERROR 1126 (HY000): Can't open shared library 'auth_test_plugin.so' (errno: 0 API version for AUTHENTICATION plugin is too different)

What's the matter with this?

I gave an answer that everyone hates: Go to the MySQL8.0 official manual.

text

In fact, when the MySQL version has developed to 8.0, there is no need to use the proxy user function at all, and it can be perfectly replaced by roles.

auth_test_plugin.so is a plug-in for MySQL 5.5. It is limited to the test environment. It is not recommended for online use and is only for function demonstrations. After a series of major versions of the installation package does not include this plug-in, so there are some differences in the method of use.

Below I will give a simple demonstration of how to use proxy users under MySQL 8.0.

I use the plug-in mysql_native_password in the following example, this plug-in comes with proxy user function, so you need to turn on the corresponding switch in the configuration file and restart the MySQL instance: (If you use sha256_password, you should add the parameter sha256_password_proxy_users=ON to the configuration file .)

[mysqld] 
check_proxy_users=ON 
mysql_native_password_proxy_users=ON 

using the proxy user function, you need to install the mysql_no_login plug-in to prevent real users hidden under the proxy user from logging in to MySQL.

mysql:(none)>install plugin mysql_no_login soname 'mysql_no_login.so';
Query OK, 0 rows affected (0.10 sec)

Create a proxy user ytt_fake and use the authentication plug-in mysql_native_password:

mysql:(none)>create user ytt_fake identified with mysql_native_password by 'ytt';
Query OK, 0 rows affected (0.32 sec)

Create a real user, and the authentication plug-in uses mysql_no_login, prohibit this user from logging in to MySQL, and give him all permissions to operate the database ytt.

mysql:(none)>create user ytt_real identified with mysql_no_login by 'ytt';
Query OK, 0 rows affected (0.02 sec)

mysql:(none)>grant all on ytt.* to ytt_real;
Query OK, 0 rows affected (0.16 sec)

Authorize proxy users.

mysql:(none)>grant proxy on ytt_real to ytt_fake;
Query OK, 0 rows affected (0.08 sec)

Use Proxy user to log in to MySQL:

root@ytt-ubuntu:~# mysql -u ytt_fake -pytt -hytt-ubuntu
...

Your MySQL connection id is 10
Server version: 8.0.26 MySQL Community Server - GPL

...

Confirm whether the value of the variable proxy_user is ytt_fake:

mysql:ytt>select @@proxy_user;
+----------------+
| @@proxy_user   |
+----------------+
| 'ytt_fake'@'%' |
+----------------+
1 row in set (0.00 sec)

After logging in with the proxy user, check the information of the currently logged in user: the user is actually ytt_real.

mysql:(none)>select user(),current_user();
+---------------------+----------------+
| user()              | current_user() |
+---------------------+----------------+
| ytt_fake@ytt-ubuntu | ytt_real@%     |
+---------------------+----------------+
1 row in set (0.00 sec)

Confirm the permissions: have all the permissions of the real user.

mysql:(none)>show grants;
+---------------------------------------------------+
| Grants for ytt_real@%                             |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `ytt_real`@`%`              |
| GRANT ALL PRIVILEGES ON `ytt`.* TO `ytt_real`@`%` |
+---------------------------------------------------+
2 rows in set (0.00 sec)

Use proxy users to create tables, insert records, query, and destroy tables:

mysql:ytt>create table fake1( id int primary key);
Query OK, 0 rows affected (0.23 sec)

mysql:ytt>insert fake1 select 1;
Query OK, 1 row affected (0.28 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql:ytt>table fake1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql:ytt>drop table fake1;
Query OK, 0 rows affected (0.29 sec)

Since the real user ytt_real uses the authentication plug-in mysql_no_login, MySQL does not allow this user to log in:

root@ytt-ubuntu:~# mysql -uytt_real -pytt -h ytt-ubuntu
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'ytt_real'@'ytt-ubuntu' (using password: NO)

PROXY users have the following two restrictions:

1. The real user hidden behind the proxy user cannot be an anonymous user, nor can the user be given an anonymous PROXY user. The MySQL scenario only passes grammar detection, not practical application.
2. Multiple users can share a proxy user, but it is not recommended! Multiple users share a proxy user, and the results are very different from expectations, that is, the results have uncertain characteristics. For example, the user ytt_real uses the proxy user ytt_fake, and the user ytt_real_other also wants to use ytt_fake. At this time, after the user ytt_fake connects to MySQL, the real user is still ytt_real. Until the user ytt_real is deleted, it will be the second user's turn.