Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer
possible... but not sure if it'd work in all cases... we do something like this with a lot of other stuff. – xenoterracide Jan 13 '11 at 14:38
biggest problem with this method is it is certain to temporarily break apps that use the database. – xenoterracide Jan 15 '11 at 11:51
@xenoterracide : sorry, can't think of any better way ... it's not like Postgres where you can just assign one user's rights to someone else. – Joe Jan 15 '11 at 15:03

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 '';
  • mysql> SHOW PLUGINS; enter image description here
  • 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;


Now, try to login using user: proxy, password: dtest (the 'AS' variable of the proxy user):

  • $ mysql -uproxy -pdtest

    enter image description here

  • mysql> SHOW GRANTS; enter image description here

share|improve this answer

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.

share|improve this answer
Of course, this assumes that you've actually got mysql listening on a port, and not only accesable from a unix socket. – Joe Jan 11 '11 at 12:28
Well in this case it's only one machine, and I don't want to change the users password, because then it would break their existing deployed apps. – xenoterracide Jan 11 '11 at 12:31
@xenoterracide : can you turn on network sockets, and allow traffic from a different machine? Obviously, keep a host-based firewall so you can only connect from the machine that you designate. If you're already connecting from a remote machine, just set up different rules for connecting from localhost. – Joe Jan 11 '11 at 13:19
well I can technically do whatever I want, but what I need to do, is replicate what idiot customer is doing. I was just trying to do it without asking idiot customer for password. It should already have network sockets on, and I can open the firewall if I want, etc, etc. technically tomorrow this won't matter at all. At this point it's a "for future reference". In postgres all I'd have to do is disable password auth temporarily, ugh... it's so much better. – xenoterracide Jan 11 '11 at 15:23
I don't have an install I'd be willing sacrifice if it doesn't work ... but if you drop the primary key on mysql.user (currently user+host) and added a second record with an alternate password, you might find out how they handle auth -- if they hash the new password, and do a select where (user,host,password_hash) match, it might work ... if they do a select password_hash where (user,host), and then compare the hashes, it won't. – Joe Jan 11 '11 at 16:12

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.