I'm using MySQL 5.6. I want to have a limited service-user which is able to create/modify the users of my databases.
Now my problem is that I can quite figure out what privileges my service-user needs to perform all the user administration tasks (create user, grant/revoke db privileges, set password)
-
If I give him the global "create user" and "reload" privileges, he can't use "set password" [= MySQL Error: 1044 (Access denied)]
-
If I give him "select", "insert", "update" on 'mysql'.'user' and 'mysql'.'db', he can't use "set password" [= MySQL Error: 1044 (Access denied)]
-
If I give him "select", "insert", "update" on 'mysql'.*, he CAN use "set password"
I'd like to understand why this is happening, and how to achieve my approach 1 or 2. I don't want to use approach 3.
The "set password" equivalent
UPDATE mysql.user
SET Password=PASSWORD('newpass')
WHERE User='bob'
AND Host='%';
…works with my privileges 1 and 2.
Can somebody help me out?
Best Answer
The problem I see with what you propose is that a user who has the ability to grant privileges to other users... is, almost by definition, not a "limited" user.
You can't grant privileges at all without the
GRANT
privilege, and even with that, you can't grant a specific privilege that you don't possess ... unless you have permission to manipulate the grant tables directly, in which case, you're not exactly a limited user either.But, aha, here's your workaround.
Stored procedures run with the credentials of the user who defined them or as the explicitly-specified definer. (You have to have
SUPER
to specify somebody else asDEFINER
.) Any user with theEXECUTE
privilege on a stored procedure can execute the procedure, and the procedure essentially escalates their privilege level while it is running.If you wrap your administrative operations in (well-written) procedures, then you don't have to actually give the limited user permission to do anything, other than run the procedures.
The 'limited'@'%' user can now change passwords for other users even though they don't have permission to do it themselves, by using
CALL mysql.change_password('user','host','password');
.This user does not have permissions themselves, but does have execute on the proc; they can't do it directly:
... but they can do it this way. Note that "0 rows affected" is not a meaningful value.
Notes:
String-concatenated queries with input parameters are unacceptable, but the
SET PASSWORD
statement does not accept?
positional-parameters, so there's no choice here. To sanitize the inputs, I take care of the password by encrypting it in advance, outside the prepared statement, and concatenated the encrypted password there; I've sanitized the user and host by actually selecting them from the mysql.user table and using the values I've selected, also outside the prepared statement. If they aren't found, we can't set their password, so we throw an exception usingSIGNAL
. If they are found, we concatenate the fetched values into the prepared statement, sanitizing them by this mechanism. It's still theoretically possible that bad data in the mysql.user table could render the quoting of the prepared statement invalid, but if you have bad data in the mysql.user table, then you have 2 problems (1 problem in addition to this one).Any explicit
GRANT EXECUTE
you've given at the procedure level disappears from themysql.procs_priv
table if you drop and redefine the procedure, so if you make changes to the procedure, you have to re-grant the privileges to the limited user.You need MySQL 5.5+ for the
SIGNAL
statement to be valid. Below 5.5 you can replace it with a hack, something like CALL mysql.`change_password(): the user/host provided`; (note the backticks) which will throw a not quite as pretty, but still serviceable message complaining that the bogus procedure name quoted in the backticks... does not exist. Heh...ERROR 1305 (42000): PROCEDURE mysql.change_password(): the user/host provided does not exist