MySQL: Lost the WITH GRANT OPTION Privilege from Root

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.