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?