Can you "su -" in MySQL?

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.

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;

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.