Check for MySQL Database Issues with Underscores in Names

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

Misusing Wildcards in Authorization

When granting permissions, database names containing underscores _ can lead to unexpected results. Consider a common authorization statement:

GRANT ALL ON `db_1`.* TO test_user;

‍

This statement appears to grant all permissions on the db_1 database to test_user. However, since _ is a wildcard character in MySQL, it matches any single character. This means the statement could match multiple databases, such as:

• Databases with numbers: db01, db11, db21, etc.
• Databases with letters: dba1, dbb1
• Databases with special characters: db-1, db+1, db?1

This mistake can grant unintended permissions to users, creating significant security risks. Based on common naming conventions, this error could expand permissions by up to 38 times, depending on the naming rules used.

Risks of Authorization Errors

The situation becomes more complex when a database name contains multiple underscores. For example, if the database name is db_1_1, the authorization could expand permissions by up to 38 × 38 = 1,444 times. If sensitive data exists in these databases, the security risk is severe.

How to Avoid This Issue

Properly Escape Wildcards

To prevent unauthorized access, treat wildcards as literal characters. MySQL allows escaping wildcards using a backslash (\), such as:

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

‍

This ensures that _ is treated as a literal character rather than a wildcard, granting permissions only to the specific db_1 database.

Benefits of Tools Like Alibaba Cloud DMS

Tools like Alibaba Cloud DMS automatically escape wildcards during authorization, reducing the risk of manual errors. However, since Alibaba Cloud allows manual authorization bypassing DMS, understanding these risks is still essential.

Risks During Remediation

When correcting authorization issues, two scenarios can lead to problems:

1. ‍Omitted Remediation: Some databases might not be fully corrected and still use wildcard authorizations.‍
2. Retaining Wildcard Functionality: In some cases, wildcard authorizations might still be necessary.

These scenarios can lead to permission loss, as discussed in the following section.

Simulating a Scenario: Permission Loss Due to Omitted Remediation

Assume you missed remediating a database named app_db during a permission cleanup. The original authorization was:

GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `app_user`@`%`;

‍

Later, you added new permissions using an escaped wildcard:

GRANT CREATE, DROP, ALTER ON `app\_db`.* TO `app_user`@`%`;

‍

The resulting grants for app_user would look like this:

mysql> show grants for app_user;
+----------------------------------------------------------------------+
| Grants for app_user@%                                                |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`%`                                 |
| GRANT CREATE, DROP, ALTER ON `app\_db`.* TO `app_user`@`%`           |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `app_user`@`%` |
+----------------------------------------------------------------------+
3 rows in set (0.01 sec)

‍

However, testing the permissions reveals an unexpected issue:

mysql> select * from `app_db`.t;
ERROR 1142 (42000): SELECT command denied to user 'app_user'@'127.0.0.1' for table 't'
mysql> insert into `app_db`.t values (1);
ERROR 1142 (42000): INSERT command denied to user 'app_user'@'127.0.0.1' for table 't'
mysql> update `app_db`.t set a=1;
ERROR 1142 (42000): UPDATE command denied to user 'app_user'@'127.0.0.1' for table 't'
mysql> delete from `app_db`.t;
ERROR 1142 (42000): DELETE command denied to user 'app_user'@'127.0.0.1' for table 't'

mysql> create table `app_db`.t2(a int);
Query OK, 0 rows affected (0.01 sec)
mysql> alter table `app_db`.t2 engine=innodb;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> drop table `app_db`.t2;
Query OK, 0 rows affected (0.01 sec)

‍

While the new permissions (CREATE, DROP, ALTER) took effect, the original permissions (SELECT, INSERT, UPDATE, DELETE) were lost.

Explanation and Analysis

This behavior is not a MySQL bug but a known characteristic of its authorization mechanism. According to the MySQL documentation:

• When multiple grants involving wildcards match the same database, only the first matching grant is applied.
• In cases where wildcard and escaped wildcard grants coexist, MySQL prioritizes the escaped wildcard grant.

Further Testing

Testing across MySQL versions (5.7 and 8.0) revealed:

• In MySQL 5.7, the escaped wildcard grant takes precedence over the wildcard grant.
• In MySQL 8.0, the order of grants in the mysql.db table determines which grant takes precedence.

Special Cases and Exceptions

MySQL interprets underscores _ as literal characters in certain scenarios:

• When the database name is used as a qualifier for table, function, or routine permissions (e.g., GRANT ... ON db_name.tbl_name).
• When the partial_revokes parameter is enabled, underscores are treated as literal characters regardless of escaping.

How to Mitigate Risks

1. ‍Avoid Wildcard Authorizations: Replace wildcard characters with escaped versions (\_ and \%).
2. ‍Audit Existing Authorizations: Use the following SQL script to identify wildcard usage in authorizations:

SELECT
    -- Check if database names contain wildcards
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM information_schema.schemata
            WHERE INSTR(schema_name, '_') > 0 OR INSTR(schema_name, '%') > 0
        ) THEN 'Yes'
        ELSE 'No'
    END AS 'Do Database Names Contain Wildcards',

    -- Check if authorizations use wildcards
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM mysql.db
            WHERE (INSTR(Db, '_') > 0 OR INSTR(Db, '%') > 0)
              AND (INSTR(Db, '\\_') = 0 AND INSTR(Db, '\\%') = 0)
        ) THEN 'Yes'
        ELSE 'No'
    END AS 'Do Authorizations Use Wildcards',

    -- Check if authorizations use escaped wildcards
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM mysql.db
            WHERE INSTR(Db, '\\_') > 0 OR INSTR(Db, '\\%') > 0
        ) THEN 'Yes'
        ELSE 'No'
    END AS 'Do Authorizations Use Escaped Wildcards',

    -- Check for table-level authorizations
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM mysql.tables_priv
            WHERE NOT (
                (Host = 'localhost' AND Db = 'mysql' AND User = 'mysql.session' AND Table_name = 'user')
                OR
                (Host = 'localhost' AND Db = 'sys' AND User = 'mysql.sys' AND Table_name = 'sys_config')
            )
        ) THEN 'Yes'
        ELSE 'No'
    END AS 'Are There Table-Level Authorizations';

‍

Final Recommendation

• Avoid using wildcards in authorizations whenever possible.
• If wildcards must be used, avoid mixing them with escaped wildcards.
• Test authorization configurations thoroughly, especially when upgrading MySQL versions.

‍

‍