MySQL Proxy Users – SU emulation
Earlier this year (January 2011), a question was asked on Database Administrator regarding whether it was possible to masquerade as a user other than the one you log in as. That is to say, can you assume the permissions of another user, similar to how ‘su’ works on *nix?
At the time, the answer was ‘no’. However, with the release of MySQL 5.5.7, you can do this using the ‘Proxy Users‘ feature, which is combined with pluggable authentication.
Here I outline the steps to get a rudimentary ‘su’ working in MySQL 5.5.7 and above.
First, as root user:
- 1) Install plugin
mysql> INSTALL PLUGIN test_plugin_server SONAME 'auth_test_plugin.so'; Query OK, 0 rows affected (0.22 sec) mysql> SHOW PLUGINS; +-----------------------+----------+--------------------+---------------------+---------+ | Name | Status | Type | Library | License | +-----------------------+----------+--------------------+---------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | test_plugin_server | ACTIVE | AUTHENTICATION | auth_test_plugin.so | GPL | +-----------------------+----------+--------------------+---------------------+---------+ 21 rows in set (0.08 sec) - 2) Create user to emulate (if it doesn’t exist)
mysql> GRANT ALL PRIVILEGES ON test.* TO dtest@localhost IDENTIFIED BY 'mypass'; - 3) Create ‘proxy’ user and grant the PROXY privilege
mysql> CREATE USER proxy@localhost IDENTIFIED WITH test_plugin_server AS 'dtest'; mysql> GRANT PROXY ON dtest@localhost TO proxy@localhost; mysql> FLUSH PRIVILEGES;
Now, you should be able to login as user ‘proxy’ and have the same grants as ‘dtest’:
$ mysql -uproxy -pdtest mysql> SELECT USER(), CURRENT_USER(); +-----------------+-----------------+ | USER() | CURRENT_USER() | +-----------------+-----------------+ | proxy@localhost | dtest@localhost | +-----------------+-----------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS; +----------------------------------------------------------------------------------------------+ | Grants for dtest@localhost | +----------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dtest'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA' | | GRANT ALL PRIVILEGES ON `test`.* TO 'dtest'@'localhost' | +----------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
