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 as DEFINER
.) Any user with the EXECUTE
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.
DELIMITER $$
DROP PROCEDURE IF EXISTS `mysql`.`change_password` $$
-- root@localhost is an example; use an appropriate local user that has the
-- permissions that need to be available for the operation to succeed
CREATE DEFINER='root'@'localhost' PROCEDURE `mysql`.`change_password` (
IN dirty_user VARCHAR(16),
IN dirty_host VARCHAR(40),
IN dirty_password VARCHAR(41))
BEGIN
DECLARE encrypted_password TINYTEXT DEFAULT PASSWORD(dirty_password);
DECLARE clean_user TINYTEXT DEFAULT NULL;
DECLARE clean_host TINYTEXT DEFAULT NULL;
SELECT user, host
FROM mysql.user
WHERE user = dirty_user
AND host = dirty_host
INTO clean_user, clean_host;
IF clean_user IS NULL OR clean_host IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'user/host provided does not exist';
END IF;
SET @_sql = CONCAT_WS('\'','SET PASSWORD FOR ',clean_user,'@',clean_host,' = ',encrypted_password,'');
PREPARE stmt FROM @_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @_sql = NULL;
END $$
DELIMITER ;
GRANT EXECUTE ON PROCEDURE mysql.change_password TO 'limited'@'%';
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:
mysql> set password for 'wombat'@'localhost' = PASSWORD('secret');
ERROR 1044 (42000): Access denied for user 'limited'@'%' to database 'mysql'
... but they can do it this way. Note that "0 rows affected" is not a meaningful value.
mysql> call mysql.change_password('wombat','localhost','secret');
Query OK, 0 rows affected (0.00 sec)
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 using SIGNAL
. 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 the mysql.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
I have dealt with this issue before.
When you ran
select count(1) UserTableColumnCount from information_schema.columns
where table_schema='mysql' and table_name='user';
you should have gotten 42. That's how many columns MySQL 5.5 has for mysql.user
. Since you got 39, that means you must have upgraded from MySQL 5.1. That has 39 columns.
I wrote an earlier post about the number of columns in mysql.user
in different versions : MySQL service stops after trying to grant privileges to a user
Here is the post where I dealt with this : mysql: Restore All privileges to admin user
Hopefully you could run
# mysql_upgrade --upgrade-system-tables
to realign mysql.user
and have it autofill missing permissions with Y
.
Give it a Try !!!
If you want to try to fix the mysql.user manually, here are the steps:
#
# Backup the mysql.user table
#
CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
#
# Add Missing Columns
#
ALTER TABLE mysql.user
ADD COLUMN Create_tablespace_priv enum('N','Y') NOT NULL DEFAULT 'N'
AFTER Trigger_Priv
;
ALTER TABLE mysql.user ADD COLUMN plugin char(64);
ALTER TABLE mysql.user ADD COLUMN authentication_string text DEFAULT NULL;
#
# Give root user all privileges
#
UPDATE mysql.user SET
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',
Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',
Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',
Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',
Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',
Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',
Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',
Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user='root';
FLUSH PRIVILEGES;
That's it !!!
Best Answer
You only have
SELECT
privilege onmysql.user
andmysql.db
.You need to have more than that. If you could be granted
This will allow you to
CREATE USER
as it must be able to write to those two tables.With you current grants on
mysql.user
andmysql.db
, you cannot create any user.