Confused by GRANT ALL PRIVILEGES ON *.* (...) WITH GRANT OPTION

Question

Normally I manage users on my databases via phpMyAdmin, but since I do everything else in standalone programs such as SQLyog or HeidiSQL, I wanted the ability to manage users remotely from these programs.

• The remote user already had SUPER because it is needed when copying views from a local database to a remote database.
• The remote user did not have the required database-level rights on mysql and the target databases as SUPER does not give that.

I tried this (MySQL version 5.6.23):

GRANT ALL PRIVILEGES ON *.* TO 'UserName'@'myIP' IDENTIFIED BY 'password'
WITH GRANT OPTION

Strangely, the *.* did not seem to work. For instance:

SELECT USER, HOST, db, select_priv, insert_priv, grant_priv FROM mysql.db
WHERE db="somedatabase"

returned an empty set. As a result, at that stage my remote user was not able to grant privileges to users (error 1442).

And yet,

SHOW GRANTS FOR 'UserName'@'myIP'

did show

GRANT ALL PRIVILEGES ON *.* TO 'UserName'@'myIP' IDENTIFIED BY PASSWORD 'someHash' WITH GRANT OPTION

To solve it, I had to GRANT ALL specifically on mysql and on somedatabase:

GRANT ALL PRIVILEGES ON mysql.* TO 'UserName'@'myIP' IDENTIFIED BY 'password'
WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON somedatabase.* TO 'UserName'@'myIP' IDENTIFIED BY 'password'
WITH GRANT OPTION;

Would someone have insights as to why *.* was not enough?

Answer 1

I really had to think about this one. Error 1442 affecting GRANT commands ?

Note the MySQL Documentation

Error: 1442 SQLSTATE: HY000 (ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG)

Message: Can't update table '%s' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

If you are using managed hosting, some are known to use triggers and stored procedures to deliberately prevent running GRANT commands.

EXAMPLE : Amazon RDS allows the following:

Q: What privileges are granted to the master user for my DB Instance?

For MySQL, the default privileges for the master user include: create, drop, references, event, alter, delete, index, insert, select, update, create temporary tables, lock tables, trigger, create view, show view, alter routine, create routine, execute, trigger, create user, process, show databases, grant option.

What grants are disallowed ?

• SUPER
• FILE
• SHUTDOWN (See my post what could cause a mysql process to close (clean))
• REPLICATION SLAVE
• REPLICATION CLIENT
• See MySQL Documentation on all the GRANTS

If you are seeing Error 1442, then your hosting company must be using some Proxy code written as a Stored Procedure or has created triggers against the mysql schema to throw regular GRANT command under the bus. From a business point-of-view, they are right in doing this.

If Amazon RDS allowed all grants, can you imagine the havoc being wreaked ?

• If you had SHUTDOWN, you could shutdown MySQL RDS instance without Amazon's API
• If you had REPLICATION SLAVE and REPLICATION CLIENT, you could setup Slaves outside of Amazon (with the right security group defined).
• If had SUPER, you can kill connections, hose binary logs, increase connection limits, etc.
• If you had FILE, you could create files with SELECT ... INTO OUTFILE right inside the MySQL RDS Instance and LOAD DATA ... INFILE from external sources (again, with the right security group defined).

Therefore, you need to read the hosting company's documentation on the limits they place on MySQL grants. If Amazon does this with RDS, I am sure other companies would have the same protocols in place. The way around this for an Amazon RDS user would be to ditch Amazon RDS and switch to Amazon EC2.

Contact your hosting company and see if this is indeed the case.