Can you “su -” in MySQL?

Question

If I have root on a mysql database and I want to drop privileges to that of a normal user, without using their password, can I do it? if so how? think # su - username on unix. Basically, I'm just looking to avoid needing their password to be them, so I can test their privileges at their user. In postgres I could simply allow an ident authentication on the system root user, to bypass password auth. The reason I need this is to be able to reproduce a users problem by being them, not being them will not allow an accurate reproduction. I can of course ask for their password, but that takes more time that bypassing it.

Answer 1

I just realized -- so long as you don't mind locking out the user while you log in --

1. back up the mysql.user table (well, the user's hashed password, at the very least)
2. set their password to something you know : UPDATE mysql.user SET password=PASSWORD('new password') WHERE user='username' AND host='hostname';
3. log in as them
4. set their password back to what it was : UPDATE mysql.user SET password='saved password hash' WHERE user='username' AND host='hostname';

... you may need to flush privileges; after manipulating the mysql.user table.

Answer 2

I don't know of a specific way to replicate the equivalent of su, however, you don't need their password -- because of the way that mysql handles authentication, you can set a different password (or none at all) from each machine that's logged in from.

Answer 3

It is possible to emulate a user as of MySQL 5.5.7, with the introduction of Proxy Users. I had never done this before, so I tried it out using the test authentication plugin, as it seems proxy users only works with authentication plugins enabled. Here are the steps I took.

First steps as root:

• mysql> INSTALL PLUGIN test_plugin_server SONAME 'auth_test_plugin.so';
• mysql> SHOW PLUGINS;

• Create user to emulate (in your case it already exists):

  mysql> GRANT ALL PRIVILEGES ONdtest@localhostIDENTIFIED BY 'mypass';

• Create 'proxy' user:

  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, try to login using user: proxy, password: dtest (the 'AS' variable of the proxy user):

• $ mysql -uproxy -pdtest

• mysql> SELECT USER(), CURRENT_USER();

  

• mysql> SHOW GRANTS;