MySQL 8.0.26: Access Denied during GRANT PROXY as root, when connected via TCP/IP socket

Question

Synopsis

When I am logged into MySQL using a TCP/IP connection, I am unable to perform the GRANT PROXY command, even though I am logged in as root. However, if I connect to MySQL using the UNIX socket, I am able to successfully run the GRANT PROXY command.

Question: Why am I getting "access denied" when I try to run GRANT PROXY as root, when connected using the TCP/IP socket?

Reproduction Steps

First, spin up a MySQL 8.0.26 container and get an interactive Bash shell inside it.

docker run --name testmysqldeleteme --env MYSQL_ROOT_PASSWORD=123 --detach mysql:8.0.26
docker exec -it testmysqldeleteme bash

Then in the interactive Bash shell:

mysql --user root --password=123 --host 127.0.0.1

In the MySQL shell:

# Enable the check_proxy_users feature
SET GLOBAL check_proxy_users = 1;

# Enable proxy users for the mysql_native_password authentication plugin;
SET GLOBAL mysql_native_password_proxy_users = 1;

# Enable the no-login plugin for MySQL
# https://dev.mysql.com/doc/refman/8.0/en/no-login-pluggable-authentication.html
INSTALL PLUGIN mysql_no_login SONAME 'mysql_no_login.so';

# Create two users
CREATE USER trevor IDENTIFIED WITH mysql_native_password BY 'Trevor123';
CREATE USER trevor_proxy IDENTIFIED WITH mysql_no_login;

# Grant proxy privileges
GRANT PROXY ON trevor_proxy TO trevor;

NOTE: At this point, you should receive:

ERROR 1698 (28000): Access denied for user 'root'@'127.0.0.1'

Answer 1

Check the root permissions on mysql.proxies_priv. By default, only root@localhost has the privilege to GRANT PROXY to other accounts. In order to grant root privileges to perform the GRANT PROXY command when connected via IP, you must first login using the UNIX socket and run the following command.

GRANT PROXY ON ''@'' TO root@'%' WITH GRANT OPTION;

This will create a record in the mysql.proxies_priv table. After this, the root@<ip> user will have access to perform additional GRANT PROXY operations.

See:

$ mysql -u root -p -h 127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 current_user();
+----------------+
| current_user() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.00 sec)

mysql> # Enable the check_proxy_users feature
mysql> SET GLOBAL check_proxy_users = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> # Enable proxy users for the mysql_native_password authentication plugin;
mysql> SET GLOBAL mysql_native_password_proxy_users = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> # Enable the no-login plugin for MySQL
mysql> # https://dev.mysql.com/doc/refman/8.0/en/no-login-pluggable-authentication.html
mysql> INSTALL PLUGIN mysql_no_login SONAME 'mysql_no_login.so';
Query OK, 0 rows affected (0.01 sec)

mysql> # Create two users
mysql> CREATE USER trevor IDENTIFIED WITH mysql_native_password BY 'Trevor123';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER trevor_proxy IDENTIFIED WITH mysql_no_login;
Query OK, 0 rows affected (0.00 sec)

mysql> # Grant proxy privileges
mysql> GRANT PROXY ON trevor_proxy TO trevor;
ERROR 1698 (28000): Access denied for user 'root'@'127.0.0.1'

mysql> select * from mysql.proxies_priv\G
*************************** 1. row ***************************
        Host: localhost
        User: root
Proxied_host: 
Proxied_user: 
  With_grant: 1
     Grantor: boot@
   Timestamp: 0000-00-00 00:00:00
1 row in set (0.00 sec)

mysql> exit;
Bye

$ mysql -u root -p -h localhost
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> # Enable the check_proxy_users feature
mysql> SET GLOBAL check_proxy_users = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> # Enable proxy users for the mysql_native_password authentication plugin;
mysql> SET GLOBAL mysql_native_password_proxy_users = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> # Enable the no-login plugin for MySQL
mysql> # https://dev.mysql.com/doc/refman/8.0/en/no-login-pluggable-authentication.html
mysql> INSTALL PLUGIN mysql_no_login SONAME 'mysql_no_login.so';
ERROR 1125 (HY000): Function 'mysql_no_login' already exists

mysql> # Create two users
mysql> CREATE USER trevor IDENTIFIED WITH mysql_native_password BY 'Trevor123';
ERROR 1396 (HY000): Operation CREATE USER failed for 'trevor'@'%'

mysql> CREATE USER trevor_proxy IDENTIFIED WITH mysql_no_login;
ERROR 1396 (HY000): Operation CREATE USER failed for 'trevor_proxy'@'%'

mysql> # Grant proxy privileges
mysql> GRANT PROXY ON trevor_proxy TO trevor;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for trevor;
+---------------------------------------------------+
| Grants for trevor@%                               |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `trevor`@`%`                |
| GRANT PROXY ON 'trevor_proxy'@'%' TO 'trevor'@'%' |
+---------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from mysql.proxies_priv\G
*************************** 1. row ***************************
        Host: %
        User: trevor
Proxied_host: %
Proxied_user: trevor_proxy
  With_grant: 0
     Grantor: root@localhost
   Timestamp: 0000-00-00 00:00:00
*************************** 2. row ***************************
        Host: localhost
        User: root
Proxied_host: 
Proxied_user: 
  With_grant: 1
     Grantor: boot@
   Timestamp: 0000-00-00 00:00:00
2 rows in set (0.00 sec)