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

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 ON dtest@localhost IDENTIFIED 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;

Answer 3

Nowadays MySQL doesn't have a root password and instead uses the auth_socket plugin to verify the user that connected to the socket is the root user of the system. You could use this same technique to solve your problem if you are ok with having unix accounts for all your users, which opens up all kinds of possibilities e.g. easy binary file storage!

First you would add a unix account for the user:

root: adduser testuser

Then add a matching MySQL user (could be done automatically in an adduser.local script):

CREATE USER 'testuser'@'localhost' IDENTIFIED WITH auth_socket;

Now switch to the the user (no password required when done as root):

root: su testuser

Connect to MySQL as the user (no password required because you are logged in as that user):

testuser: mysql -u $USER (or -you testuser instead of using the environment variable)

It's best to supply the -u user param because the mysql client automatic user convenience routine isn't good at figuring it out on its own, e.g. if you su from user to user account it uses the first user for some reason.

Answer 4

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.