MySQL: Lost the WITH GRANT OPTION Privilege from Root
2018-02-26 22:14 (6 years ago)
ytyng
While managing the root user with the mysql_user module in Ansible, I found that I was no longer able to grant privileges to other users.
When I checked the grants for root, I saw:
mysql> show grants;
+--------------------------------------------------------------+
| Grants for root@localhost |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
The WITH GRANT OPTION was missing.
In such a case, you can forcefully set the Grant_priv by executing:
UPDATE mysql.user SET Grant_priv = 'Y' WHERE User='root';
(You might need to run FLUSH PRIVILEGES; afterward?)
The correct show grants should look like this:
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
This is how it should appear.
Comments