3

I run a MySQL server and I have MySQL root-level privileges on this instance:

mysql> select CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.02 sec)
mysql>

I am troubleshooting MySQL privileges for a series of users. I do not know the passwords for these users. As the MySQL root user, can I assume the identity of another user without knowing their password?

In the Linux/Unix world, I would do this with a command like 'su - someuser' to assume the identity of the user. Does MySQL provide an equivalent function?

Stefan Lasiewski
  • 22,949
  • 38
  • 129
  • 184
  • 2
    Great question. I've never heard of such a thing, but will be quite interested to see if indeed there is. – EEAA Mar 17 '14 at 19:02
  • Oh, and BTW...sysadmin/parent here as well. :) – EEAA Mar 17 '14 at 19:03
  • The only way I know of doing something like this would be to safely store the user's password hash then set a new password and when you're finished put the hash back as it was. Obviously, the user gets locked out while you do this. – user9517 Mar 17 '14 at 19:07
  • Thanks Iain. That's what I normally do, but I was hoping for something quicker. `su - jane` on Unix is a super quick and simple solution and I am hoping to find something like this on MySQL. – Stefan Lasiewski Mar 17 '14 at 19:13
  • @StefanLasiewski: I don't think that exists you could always clone the privs to another account http://serverfault.com/questions/105612/how-to-copy-user-privileges-with-mysql – user9517 Mar 17 '14 at 19:18
  • Yes, this has existed since MySQL 5.5. I added an answer for it. – RolandoMySQLDBA Mar 17 '14 at 20:10

1 Answers1

5

What you are asking for can only be done in two ways

PROXY USERS

MySQL 5.5 introduced proxy users

When authentication to the MySQL server occurs by means of an authentication plugin, the plugin may request that the connecting (external) user be treated as a different user for privilege-checking purposes. This enables the external user to be a proxy for the second user; that is, to have the privileges of the second user. In other words, the external user is a “proxy user” (a user who can impersonate or become known as another user) and the second user is a “proxied user” (a user whose identity can be taken on by a proxy user).

You can find the defined proxy users in mysql.proxies_priv:

mysql> desc mysql.proxies_priv;
+--------------+------------+------+-----+-------------------+-----------------------------+
| Field        | Type       | Null | Key | Default           | Extra                       |
+--------------+------------+------+-----+-------------------+-----------------------------+
| Host         | char(60)   | NO   | PRI |                   |                             |
| User         | char(16)   | NO   | PRI |                   |                             |
| Proxied_host | char(60)   | NO   | PRI |                   |                             |
| Proxied_user | char(16)   | NO   | PRI |                   |                             |
| With_grant   | tinyint(1) | NO   |     | 0                 |                             |
| Grantor      | char(77)   | NO   | MUL |                   |                             |
| Timestamp    | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.22 sec)

mysql>

Here is sample code from the MySQL Documentation

CREATE USER 'empl_external'@'localhost'
  IDENTIFIED WITH auth_plugin AS 'auth_string';
CREATE USER 'employee'@'localhost'
  IDENTIFIED BY 'employee_pass';
GRANT PROXY
  ON 'employee'@'localhost'
  TO 'empl_external'@'localhost';

SECURITY TYPE (STORED PROCEDURES/VIEWS)

In mysql.proc, there is a column called security_type. With views, this exists in information_schema.views. It has two values 1) invoker and 2) definer. If a stored procedure has security_type as definer, you have the same access rights as the owner of the stored procedure. Of course, this is only applicable

  • when calling a Stored Procedure or SELECTing from a View
  • You have the EXECUTE privilege
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • Thanks again Rolando. This looks like a good solution when a long term solution is needed (And I could use this elsewhere). For my one-off tests, I will probably chose a simpler solution like copying the privileges to a temporary user with a password that I do know. – Stefan Lasiewski Mar 17 '14 at 23:49