When USAGE Meets GRANT OPTION

Background

A client reported anomalous behavior: MySQL users could see databases beyond their assigned permissions.

​User Permissions:​

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     |
+------------------------------------------------------------------------+

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| austin             |  ← Should be invisible (USAGE only)
| data_center        |
| file               |  ← Should be invisible (USAGE only)
| nacos              |
| redmoonoa9         |
| xxl_job            |
+--------------------+

‍

Key Permission Concepts

• ​ALL：​All privileges (opposite of USAGE)
• ​USAGE：​No privileges (opposite of ALL)
• ​GRANT OPTION：​Allows granting/revoking others' permissions

Reproduction

Test Case 1: Normal Behavior

-- User with only SELECT + GRANT OPTION
GRANT SELECT ON `test`.* TO 'hjm'@'%' WITH GRANT OPTION;

SHOW DATABASES;  → Only sees 'test'
USE test; 
SHOW TABLES;     → Can list tables
SELECT * FROM t1;→ Works (has SELECT)

‍

Test Case 2: Conflict Scenario

-- Revoke SELECT, keep USAGE + GRANT OPTION
REVOKE SELECT ON `test`.* FROM 'hjm'@'%';

SHOW GRANTS;
+--------------------------------------------------------+
| Grants for hjm@%                                       |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hjm'@'%'                        |
| GRANT USAGE ON `test`.* TO 'hjm'@'%' WITH GRANT OPTION |
+--------------------------------------------------------+

SHOW DATABASES;  → Still sees 'test' (unexpected)
USE test;
SHOW TABLES;     → Can list tables (unexpected)
SELECT * FROM t1;→ Fails: "SELECT command denied" (expected)

‍

Conclusion

​Conflict Condition:​​
When a user has ​both​ USAGE and GRANT OPTION on the same database:

1. Gains visibility of the database and its tables
2. Cannot access table data (no SELECT/INSERT/etc.)

​Critical Note:​​
Always revoke GRANT OPTION separately:

REVOKE GRANT OPTION ON `db`.* FROM 'user'@'%';  -- Required!

‍

Open Question

Why does MySQL allow this permission combination despite its side effects?

‍