MySQL Privileges required to GRANT EVENT, EXECUTE, LOCK TABLES, and TRIGGER
7,862
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.
Author by
Brad
Updated on September 18, 2022Comments
-
Brad almost 2 years
I have an account,
user_a
, and I would like to grant all available permissions onsome_db
touser_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 areEVENT
,EXECUTE
,LOCK TABLES
, andTRIGGER
.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"
-
Brad almost 13 yearsThanks. I ran your query, and
user_a
does indeed have Grant_priv=='Y'. Any other thoughts on what the problem might be? -
Brad almost 13 yearsYes, of course! I feel like an idiot, I don't know how I missed that. Thanks!