Avoid MySQL Permission Loss: Database Names with Underscores and Wildcard Risks

In MySQL, wildcards _ and % are used to match single or multiple characters in database object names. However, many DBAs overlook their special behavior during authorization, leading to permission misconfigurations. This article discusses the risks of wildcard misuse and provides solutions to avoid them.

Misusing Wildcards Causes Permission Errors

When authorizing database permissions, a database name containing _ might unexpectedly match multiple targets. For example:

GRANT ALL ON `db_1`.* TO 'test_user';

‍

While intended for db_1, this statement also grants access to databases like db01, dba1, or db-1. Such errors scale exponentially with multiple underscores. A name like db_1_1 could expose ​1,444x​ more databases than intended.

Hidden Dangers of Overly Broad Permissions

Sensitive data in mismatched databases becomes vulnerable. For instance:

• A typo in GRANT ... ON app_db.* might accidentally include app_dba or app_db_backup.
• Mixed use of escaped (\) and unescaped wildcards can ​invalidate existing permissions, as shown in real-world tests.

How to Prevent These Issues

1. ​Escape Wildcards Explicitly:
Use \ to treat _ as a literal character:

GRANT ALL ON `db\_1`.* TO 'test_user';

‍

2. Avoid Wildcards Altogether:
Modern MySQL versions discourage wildcard usage. Opt for explicit database names instead.

3. ​Leverage Tools Like Alibaba Cloud DMS:
Automated tools escape wildcards by default, reducing manual errors.

Remediation Best Practices

• Audit existing permissions using scripts like:

SELECT * FROM mysql.db 
WHERE Db LIKE '%_%' OR Db LIKE '%%';

‍

• est changes in staging environments before production deployment.
• Enable partial_revokes to restrict wildcard privileges granularly.

Why Wildcards Are Deprecated

MySQL’s documentation warns against wildcard misuse due to unpredictable behavior, such as:

• Conflicts between escaped and unescaped wildcards.
• Priority given to the first matching grant in multi-statement scenarios.

By adopting strict naming conventions and secure authorization practices, you can mitigate risks and align with MySQL’s evolving security standards.

‍