Confused by GRANT ALL PRIVILEGES ON *.* (...) WITH GRANT OPTION 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?
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.