MySQL 8.0 : Proxy User

by Mark Nielsen
Copyright September 2023

For this document, I am using proxy in reverse of how MySQL users it. To me accounts are proxied to another account. Technically, the user accounts are proxies and the account being proxied is a real account. But to the user they don't know. I call the user account the use account, and the account they become as the proxy account. MySQL does it in reverse, which is technically better. I don't like calling it that though.

Links
Proxy example

Proxy Examples

An account that uses a proxy account, ignores it own permissions and uses the proxy accounts. Here I am reversing the meaning of proxy. I consider the account made for the user a user account, and it uses a proxy account that you cannot directly log into.

Make sure you load the plugin and let any authentication module use PROXY, not just the ones that support it. In your root connection...

INSTALL PLUGIN mysql_no_login SONAME 'mysql_no_login.so';
set persist check_proxy_users =ON;
set persist mysql_native_password_proxy_users = ON;
set PERSIST sha256_password_proxy_users = ON;
show global variables like '%proxy%';

drop database if exists mark_temp2;
create database mark_temp2;
drop database if exists mark_temp3;
create database mark_temp3;
use mark_temp2;

drop user if exists 'user1'@'localhost';
CREATE USER 'user1'@'localhost'  
IDENTIFIED WITH mysql_native_password
  BY 'bad_password';
GRANT ALL  ON mark_temp3.*  TO 'user1'@'localhost';

CREATE USER 'proxy_acct'@'localhost'  IDENTIFIED WITH mysql_no_login;
GRANT ALL  ON mark_temp2.*  TO 'proxy_acct'@'localhost';

GRANT PROXY
  ON 'proxy_acct'@'localhost'
  TO 'user1'@'localhost';

A the linux or unix prompt, it should work...

mysql -u user1 -pbad_password -e "create table if not exists i (i int)" mark_temp2

# This next command should work.
mysql -u user1 -pbad_password -e "select user(), current_user(), @@proxy_user" mark_temp2

# This will fail because user1 ignores its own permissions;
mysql -u user1 -pbad_password -e "select user(), current_user(), @@proxy_user" mark_temp3

# The grants for proxy_acct is shown instead of user1
mysql -u user1 -pbad_password -e "show grants"

# you cannot even get your own permissons
mysql -u user1 -pbad_password -e "show grants for user1@localhost"

NOTE: I got it to work with the plugin mysql_native_password and not sha256_password. Not sure why. SHA is the default.

MySQL 8.0 : Proxy User

Links

Proxy Examples