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. It's 100% free, no registration required.

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.

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.

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.

Of course, this assumes that you've actually got mysql listening on a port, and not only accesable from a unix socket.
–
JoeJan 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.
–
xenoterracideJan 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.
–
JoeJan 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.
–
xenoterracideJan 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.
–
JoeJan 11 '11 at 16:12