Understanding MySQL Permission Conflict Between USAGE and GRANT OPTION

The author, a DBA from the Aikesheng South District Delivery Service Department, investigates a customer-reported anomaly where a MySQL user, originally limited to certain tables, could view information from databases outside its intended scope.

Background : The user ttt@% had a series of grants, including USAGE on *.* and GRANT OPTION on several databases (e.g., austin , file , redmoonoa9 , nacos , xxl_job , data_center ). The SHOW GRANTS output and the list of databases confirmed these permissions.

Permission Overview : MySQL permissions are divided into global and database‑specific privileges. Common privileges such as ALL, ALTER, CREATE, DROP, SELECT, INSERT, UPDATE, DELETE, GRANT OPTION, and USAGE are described in a table. The analysis suggests that the conflict arises when a user simultaneously holds USAGE and GRANT OPTION on the same database.

Local Test : A test user hjm was created with USAGE on *.* and SELECT + GRANT OPTION on the test database. The following commands were executed to verify behavior:

mysql> show grants;
+------------------------------------------------------------------------+
| Grants for ttt@%                                                       |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ttt'@'%'                                      |
| GRANT USAGE ON `austin`.* TO 'ttt'@'%' WITH GRANT OPTION             |
| GRANT USAGE ON `file`.* TO 'ttt'@'%' WITH GRANT OPTION                |
| GRANT ALL PRIVILEGES ON `redmoonoa9`.* TO 'ttt'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `nacos`.* TO 'ttt'@'%' WITH GRANT OPTION      |
| GRANT ALL PRIVILEGES ON `data_center`.* TO 'ttt'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `xxl_job`.* TO 'ttt'@'%' WITH GRANT OPTION |
+------------------------------------------------------------------------+
7 rows in set (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| austin             |
| data_center        |
| file               |
| nacos              |
| redmoonoa9         |
| xxl_job            |
+--------------------+
7 rows in set (0.00 sec)

The test confirmed that when only USAGE is granted, the user cannot see any database objects. When only GRANT OPTION is granted, the user can see schema metadata but still cannot query data.

Modifying Permissions : The test user was then granted both USAGE and GRANT OPTION on test , after which SELECT was revoked. The resulting SHOW GRANTS output demonstrated the combined privileges:

mysql> revoke SELECT ON `test`.* from 'hjm'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for hjm;
+--------------------------------------------------------+
| Grants for hjm@%                                      |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hjm'@'%'                      |
| GRANT USAGE ON `test`.* TO 'hjm'@'%' WITH GRANT OPTION |
+--------------------------------------------------------+
2 rows in set (0.01 sec)

When logging in as hjm , the user could list databases and tables but received an "ERROR 1142 (42000): SELECT command denied" when attempting to query data from a table, confirming the permission conflict.

Conclusion : Holding both USAGE and GRANT OPTION on the same database allows the user to view the database and its tables, but prevents data retrieval. To fully revoke the conflicting rights, one must explicitly execute REVOKE GRANT OPTION after removing other privileges.

Note: When using REVOKE to remove permissions, if the user also has WITH GRANT OPTION , you must issue REVOKE GRANT OPTION to ensure the privilege is completely cleared.