SQLyog FAQ

To illustrate this we create such user

GRANT ALL ON ´somedatabase´.* TO 'oneuser'@'localhost' WITH GRANT OPTION;

As this user has ALL privileges to a specific database and WITH GRANT OPTION he will successfully be able to GRANT any set of privileges ON ´somedatabase´ TO another user. For instance this will work for user 'oneuser' from any command-line tool including the SQLyog editor: 

GRANT SELECT, INSERT, UPDATE ON ´somedatabase´.´sometable´ TO 'otheruser'@'localhost';


But if user 'oneuser' tries to open SQLyog User Management in order to do the same a MySQL error is returned. So 'oneuser' can manage (a limited set) of user privileges if he uses the right command syntax and know what other users exist. So the question is: why can't he use the SQLyog User Management for this as well?


The reason is that there is no way for a client to know what other users exist and what privileges they have already if SELECT privileges to the privileges tables in the ´mysql´ database are not available. That is true no matter if you try to retrieve the information using a SELECT from the ´mysql´ database, the ´information_schema´ database or use SHOW GRANTS statements. The MySQL server will not expose the information to this user ('oneuser') as he has not appropriate privileges.

So basically this is the difference between a 'programmed client' and a 'human operator': the human may have some knowledge in advance so that he will not need to query the server for information required to compose a valid statement.


However if user 'oneuser' was granted privileges like this:

GRANT ALL ON ´somedatabase´.* TO 'oneuser'@'localhost' WITH GRANT OPTION;
GRANT SELECT ON ´mysql´.´user´ TO 'oneuser'@'localhost';
GRANT SELECT ON ´mysql´.´tables_priv´ TO 'oneuser'@'localhost';
GRANT SELECT ON ´mysql´.´procs_priv´ TO 'oneuser'@'localhost';
GRANT SELECT ON ´mysql´.´columns_priv´ TO 'oneuser'@'localhost';

.. he would be able to populate the User management GUI and successfully perform the equivalent of the above "GRANT SELECT, INSERT, UPDATE .." statement from the GUI (but would still encounter an error should he try to GRANT privileges to objects outside ´somedatabase´ of course).