MySQL: Lost the WITH GRANT OPTION Privilege from Root
2018-02-26 22:14 (7 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.
The author runs the application development company Cyberneura.
We look forward to discussing your development needs.
We look forward to discussing your development needs.
Comments