7

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.

0

4 Answers 4

4

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.

3
  • possible... but not sure if it'd work in all cases... we do something like this with a lot of other stuff. Jan 13, 2011 at 14:38
  • biggest problem with this method is it is certain to temporarily break apps that use the database. Jan 15, 2011 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, 2011 at 15:03
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; enter image description here

  • 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();

    enter image description here

  • mysql> SHOW GRANTS; enter image description here

2

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.

1

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.

5
  • 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, 2011 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. Jan 11, 2011 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, 2011 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. Jan 11, 2011 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, 2011 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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