MySQL: Lost the WITH GRANT OPTION Privilege from Root
2018-02-26 13:14 (8 years ago)

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.
Related posts
Install MySQL 5.6 on Ubuntu Linux 14.10
Ansible Playbook for Installing Docker CE on Ubuntu 16.04
Recovery of Stopped AWS RDS MySQL Replication
Here's the English translation of your blog title:"A Story About Trying to JOIN Fields with Different Collations in MySQL and Getting 'Range checked for each record'"
Please rate this article (No signup or login required)
Currently unrated
The author runs the application development company Cyberneura.
We look forward to discussing your development needs.
We look forward to discussing your development needs.