How to Use MySQL Proxy Users in MySQL 8.0 – Replacing Proxy with Roles and Practical Demonstration

Background

After upgrading MySQL from 5.5 to 8.0, the traditional proxy user stopped working and installing the old auth_test_plugin.so plugin fails with an API version mismatch.

Main Content

Proxy Users Are Obsolete in MySQL 8.0

The auth_test_plugin.so plugin was only for testing in MySQL 5.5 and is not shipped with newer releases, so its usage differs in 8.0.

Demonstration of Using Proxy Users in MySQL 8.0

Enable the built‑in mysql_native_password proxy functionality by adding the following options to my.cnf and restarting the server:

[mysqld]
check_proxy_users=ON
mysql_native_password_proxy_users=ON

Install the mysql_no_login plugin to prevent the real user hidden behind the proxy from logging in directly:

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 using the mysql_native_password authentication plugin:

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

Create the real user ytt_real with the mysql_no_login plugin and grant it full privileges on 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)

Grant proxy rights from the real user to the proxy user:

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

Log in using the proxy user:

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
...

Verify the proxy variable:

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

Check the actual logged‑in user (the real user):

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

Show that the real user retains all privileges:

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)

Perform typical operations (create table, insert, query, drop) using the proxy user, which succeed because the real user has the required rights.

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)

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)

Attempting to log in directly as the real user fails because of the mysql_no_login plugin:

root@ytt-ubuntu:~# mysql -uytt_real -pytt -h ytt-ubuntu
ERROR 1045 (28000): Access denied for user 'ytt_real'@'ytt-ubuntu' (using password: NO)

Limitations of Proxy Users

The real user hidden behind a proxy cannot be an anonymous user, nor can an anonymous proxy be granted.

Sharing a single proxy user among multiple real users is discouraged because the authentication result is nondeterministic; the first real user to connect remains active until it is removed.