Misuse of Wildcards in MySQL GRANT Statements Leads to Permission Errors and How to Avoid Them

In MySQL privilege management, the '_' and '%' characters act as single‑character and multi‑character wildcards when granting database‑level permissions, which can cause unexpected permission expansion if database names contain these symbols.

1. Misuse of Wildcards Causes Grant Errors

Granting GRANT ALL ON `db_1`.* TO test_user; appears to give test_user full rights on db_1 , but the '_' is interpreted as a wildcard, potentially matching db01 , db11 , db-1 , etc., expanding the scope up to 38‑fold.

2. Security Implications

When multiple '_' appear (e.g., db_1_1 ), the expansion can reach 1444‑fold, exposing sensitive data and creating severe security hazards.

3. Proper Avoidance – Escape the Wildcards

Use a backslash to treat the wildcard as a literal: GRANT ALL ON `db\_1`.* TO 'test_user'; . This ensures only the intended database receives the privileges.

4. Risks During Remediation

Two scenarios arise when fixing existing grants: (1) missed remediation leaves some wildcard grants active, and (2) retaining wildcard functionality for specific cases.

5. Example of Missed Remediation Leading to Permission Loss

A user app_user was granted SELECT, INSERT, UPDATE, DELETE on app_db and later granted CREATE, DROP, ALTER on app\_db . After the change, the original DML privileges disappeared because MySQL applied only the first matching grant.

ERROR 1142 (42000): SELECT command denied to user 'app_user'@'127.0.0.1' for table 't'
...

6. Analysis of MySQL Behavior

The MySQL documentation states that wildcard usage in GRANT statements is deprecated and may be removed in future versions. When multiple wildcard grants match the same database, MySQL considers only the first matching grant.

Testing across versions shows:

MySQL Version

Effective Grant When Mixing Wildcard and Escaped Wildcard

5.5

Non‑wildcard grant wins

5.7

Non‑wildcard grant wins

8.0

First‑created grant wins

8.4

Non‑wildcard grant wins

9.0

Non‑wildcard grant wins

7. Mitigation Recommendations

Avoid using wildcard grants altogether; escape '_' and '%' when necessary.

If wildcards must be used, never mix escaped and unescaped forms.

Audit existing grants for unescaped wildcards and replace them with escaped equivalents.

Example audit script (generated by AI) checks for unescaped '_' or '%' in database names and grants:

SELECT CASE WHEN EXISTS (SELECT 1 FROM information_schema.schemata WHERE INSTR(schema_name,'_')>0 OR INSTR(schema_name,'%')>0) THEN '是' ELSE '否' END AS '库名是否含有_或%通配符', ...;

8. References

[1] MySQL Bug 116161 – https://bugs.mysql.com/bug.php?id=116161

[2] MySQL GRANT Documentation – https://dev.mysql.com/doc/refman/8.4/en/grant.html