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.