Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have an account, user_a, and I would like to grant all available permissions on some_db to user_b. I have tried the following query:

GRANT 
    ALTER, ALTER ROUTINE, 
    CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, 
    DELETE, DROP, EVENT, EXECUTE, 
    INDEX, INSERT, LOCK TABLES, 
    REFERENCES, SELECT, SHOW VIEW, 
    TRIGGER, UPDATE 
ON  `some_db`.* TO 'user_b'@'%' WITH GRANT OPTION

The result:

Access denied for user 'user_a'@'%' to database 'some_db'

Some experimentation has shown me that the only permissions my account (user_a) is unable to grant are EVENT, EXECUTE, LOCK TABLES, and TRIGGER.

What privileges are required for my account to GRANT these privileges to another user?

If I run SHOW GRANTS, I get this output:

"GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'user_a'@'%' IDENTIFIED BY PASSWORD '1234567890abcdef' WITH GRANT OPTION"
"GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `some_other_unrelated_db`.* TO 'user_a'@'%'"
"GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `another_unrelated_db`.* TO 'user_a'@'%' WITH GRANT OPTION"
share|improve this question

2 Answers 2

up vote 3 down vote accepted

With the GRANT OPTION privilege, you can only grant privileges that you have. So, the following privileges cannot be granted to the user_b: EVENT, EXECUTE, LOCK TABLES, TRIGGER, UPDATE.

share|improve this answer
    
Yes, of course! I feel like an idiot, I don't know how I missed that. Thanks! –  Brad Aug 23 '11 at 13:24

Granting rights to users is limited to users who have the "grant" privileges globally.

Basically, you must have sufficient privileges to modify the global "grant" table... which gives you enough privileges to grant any privilege to any user. Permissions are not stored in each schema... but rather stored in 1 table in the "mysql" schema.

For a list of users with the grant privilege try running this query:

SELECT User, Grant_priv from mysql.user;
share|improve this answer
    
Thanks. I ran your query, and user_a does indeed have Grant_priv=='Y'. Any other thoughts on what the problem might be? –  Brad Aug 22 '11 at 21:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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