在 MySQL 的授權(quán)操作中，通配符 "_" 和 "%" 用于匹配單個或多個字符的數(shù)據(jù)庫對象名。然而，許多 DBA 在進行授權(quán)時可能忽視了這些通配符的特殊作用，導(dǎo)致數(shù)據(jù)庫權(quán)限錯配。這篇文章將討論通配符誤用所帶來的潛在風(fēng)險，并提供避免此類問題的解決方案。

1誤用通配符導(dǎo)致權(quán)限授予錯誤

在授權(quán)數(shù)據(jù)庫權(quán)限時，如果數(shù)據(jù)庫名中含有下劃線 _，可能會引發(fā)意想不到的結(jié)果。我們來看一個常見的授權(quán)語句：

GRANT ALL ON `db_1`.* TO  test_user;

表面上看，這個語句似乎是授予用戶 test_user 對數(shù)據(jù)庫 db_1 的全部權(quán)限。然而，通配符 _ 在 MySQL 中具有特殊含義，它用于匹配任意單個字符。因此，這條授權(quán)語句實際上可能會匹配多個數(shù)據(jù)庫，而不僅僅是 db_1。例如，以下數(shù)據(jù)庫名都可能被匹配：

• 數(shù)據(jù)庫名匹配數(shù)字：db01，db11，db21，…，db91
• 數(shù)據(jù)庫名匹配英文字符：dba1，dbb1
• 數(shù)據(jù)庫名匹配特殊字符：db-1，db+1，db?1，等等

這種誤操作可能導(dǎo)致某些用戶意外獲得了不該有的權(quán)限，從而帶來嚴(yán)重的安全隱患。實際上，按照常見的數(shù)據(jù)庫命名規(guī)范，數(shù)據(jù)庫名中的字符通常是 26 個英文小寫字母或 10 個數(shù)字，也包括 2 種特殊字符(中劃線或下劃線)。因此，這個授權(quán)錯誤可能將權(quán)限的應(yīng)用范圍擴大到 38 倍之多。這是基于對命名模式的分析得出的估算，具體情況可能因?qū)嶋H使用的命名規(guī)則而有所不同。

2授權(quán)帶來的隱患

當(dāng)庫名中有多個 "_" 時，情況更為復(fù)雜。假設(shè)數(shù)據(jù)庫名稱是 db_1_1，那么授權(quán)就不僅是擴大到 38 倍，而是 38 * 38 = 1444 倍，權(quán)限擴大的規(guī)模超出想象。如果這些庫中有不應(yīng)該公開的敏感數(shù)據(jù)，安全性風(fēng)險將非常嚴(yán)重。

3如何避免這個問題？

正確的做法：轉(zhuǎn)義通配符

為了避免這種授權(quán)濫用的風(fēng)險，我們應(yīng)該將通配符作為普通字符來處理。MySQL 支持使用反斜杠（\）對通配符進行轉(zhuǎn)義，例如：

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

通過這種方式，_ 將被解釋為字面量，而不是通配符，從而確保授權(quán)的僅是特定的 db_1 數(shù)據(jù)庫。

接下來，文章會多次提到“通配符（_）”和“轉(zhuǎn)義通配符（\_）”這兩個術(shù)語，理解它們的區(qū)別有助于避免常見授權(quán)錯誤。

阿里云 DMS 等連接工具的優(yōu)勢

值得注意的是，在使用阿里云 DMS 授權(quán)時，系統(tǒng)底層會自動將通配符進行轉(zhuǎn)義，這也就是為什么很多 DBA 并沒有意識到自己授權(quán)時遇到的潛在風(fēng)險。阿里云的這種機制為用戶省去了手動轉(zhuǎn)義的煩惱，保證了授權(quán)的準(zhǔn)確性。

然而，阿里云允許你繞過 DMS，底層手動授權(quán)，所以本篇文章內(nèi)容依然適用于使用阿里云的 DBA。

4整改過程中的風(fēng)險

在你意識到這個問題后，可能會急于對現(xiàn)有授權(quán)進行整改，但需要注意兩種場景：

• 遺漏整改：部分庫可能沒有徹底整改，仍然使用了通配符授權(quán)
• 保留通配符功能：有些場景下，你希望保留部分通配符授權(quán)

在這兩種場景下，會碰到我這篇文章要講的正餐 —— 含有下劃線的數(shù)據(jù)庫名在特殊情況下會有權(quán)限丟失的坑。

5模擬場景：遺漏整改導(dǎo)致權(quán)限丟失

現(xiàn)在我們來模擬一個場景，展示如何由于遺漏整改而導(dǎo)致權(quán)限問題的發(fā)生。

假設(shè)在權(quán)限整改過程中，你不需要保留通配符的授權(quán)，于是你對幾百個數(shù)據(jù)庫的授權(quán)進行了整改，但你還是遺漏了其中一個數(shù)據(jù)庫，我認為這類情況很有可能發(fā)生。該數(shù)據(jù)庫名為 app_db，其授權(quán)如下：

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

然后，隨著業(yè)務(wù)的擴展，你意識到應(yīng)用程序需要自動維護分區(qū)表的能力，因此你希望新增 CREATE、DROP、ALTER

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

之后，app_user 的授權(quán)狀態(tài)如下：

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)

于是，產(chǎn)生了一種，通配符（_）和轉(zhuǎn)義通配符（\_）混合使用的場景。

表面上看，兩個授權(quán)并沒有合并到一條語句，但根據(jù)我們前面學(xué)到的知識，不難理解，這兩個授權(quán)是希望表達：

• app_user 擁有對 app_db 的 CREATE、DROP、ALTER
• app_user 也擁有對 app_db 本身及其他符合通配符匹配的數(shù)據(jù)庫的 SELECT、INSERT、UPDATE、DELETE

表面看似一切正常，但實際上在操作中卻發(fā)現(xiàn)了問題。

權(quán)限測試

我們來實際測試一下授權(quán)效果：

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)

盡管新增的 CREATE、DROP、ALTER 權(quán)限生效了，但原來的 SELECT、INSERT、UPDATE 和 DELETE

解釋與分析

這顯然會在生產(chǎn)環(huán)境中引發(fā)嚴(yán)重問題。

那么這是一個 MySQL 的 Bug 嗎？

最初，我也認為這可能是個 S2 級別的 Bug，并向官方提交了報告[1]。

但深入調(diào)查后發(fā)現(xiàn)，這實際上是 MySQL 授權(quán)機制的一個已知行為，而不是 Bug。根據(jù)官方文檔[2]：

The use of the wildcard characters % and _ as described in the next few paragraphs is deprecated, and thus subject to removal in a future version of MySQL.

【翻譯】重要提示：接下來幾段中描述的使用通配符 % 和 _ 的方式已被棄用，因此在未來的 MySQL 版本中可能會被移除。

這意味著 MySQL 未來會徹底廢棄通配符在授權(quán)中的使用。更進一步的，官方文檔提到：

Issuing multiple GRANT statements containing wildcards may not have the expected effect on DML statements; when resolving grants involving wildcards, MySQL takes only the first matching grant into consideration. In other words, if a user has two database-level grants using wildcards that match the same database, the grant which was created first is applied. Consider the database db and table t created using the statements shown here:

【懶人版翻譯】當(dāng)多個授權(quán)中涉及通配符時，MySQL 只會考慮第一個匹配的授權(quán)。

我的案例

我遇到的情況與官方文檔中描述的多個通配符授權(quán)略有不同。

官方文檔提到，當(dāng)涉及多個通配符授權(quán)時，MySQL 只會應(yīng)用第一個匹配的授權(quán)，后續(xù)的通配符授權(quán)將不會生效。然而，在我的案例中，情況有所不同：我只使用了一個通配符授權(quán)，之后又添加了一個經(jīng)過正確轉(zhuǎn)義的授權(quán)。結(jié)果是，MySQL 僅識別并應(yīng)用了轉(zhuǎn)義后的授權(quán)，而原本的通配符授權(quán)則被忽略。

這表明，MySQL 在處理通配符和轉(zhuǎn)義字符時存在文檔不完善的情況。盡管官方文檔中提到通配符授權(quán)的局限性，但并未具體說明在混合使用通配符和轉(zhuǎn)義后的授權(quán)時，通配符授權(quán)可能會被轉(zhuǎn)義后的授權(quán)所取代。這種情況下，開發(fā)者容易誤認為這是一種 Bug，而實際上是 MySQL 授權(quán)機制的已知行為。

更進一步測試

上述是基于 MySQL 5.7 測試的結(jié)論:

• 單一授權(quán)生效：測試和官方文檔一致，MySQL 只會匹配并生效其中一條授權(quán)，不會同時應(yīng)用兩條授權(quán)?！?這是我的案例里踩到的陷阱。
• 優(yōu)先級問題：當(dāng)通配符授權(quán)和轉(zhuǎn)義通配符授權(quán)混合使用時，MySQL 優(yōu)先應(yīng)用不含通配符的授權(quán)。

然而，在 MySQL 8.0 的測試中，結(jié)果又有所不同：哪個授權(quán)生效取決于 mysql.db 的加載順序，先進行的授權(quán)將優(yōu)先生效。

為更清楚說明這一問題，我在多個 MySQL 版本中進行了進一步測試，結(jié)論如下表所示：

關(guān)于通配符還有一些奇怪的“例外”設(shè)置。

In privilege assignments, MySQL interprets occurrences of unescaped _ and % SQL wildcard characters in database names as literal characters under these circumstances:

• When a database name is not used to grant privileges at the database level, but as a qualifier for granting privileges to some other object such as a table or routine (for example, GRANT ... ON db_name.tbl_name).
• Enabling partial_revokes causes MySQL to interpret unescaped _ and % wildcard characters in database names as literal characters, just as if they had been escaped as \_ and \%. Because this changes how MySQL interprets privileges, it may be advisable to avoid unescaped wildcard characters in privilege assignments for installations where partial_revokes may be enabled. For more information, see Section 8.2.12, “Privilege Restriction Using Partial Revokes”.

換句話說，如果庫名中的 _ 未轉(zhuǎn)義，它會被解釋為通配符；但是庫名只是用作表名（表級授權(quán)場景）、函數(shù)、存儲過程的限定符時，庫名里的 _ 就不再是通配符，而是字面量。

此外，如果你啟用了 MySQL 的部分撤銷授權(quán)參數(shù) partial_revokes，數(shù)據(jù)庫名中的 _ 不需要轉(zhuǎn)義，它會被直接解釋為字面量。

一會兒是字面量，一會兒又是通配符，難怪官方打算放棄這個功能，連他們自己可能都被搞暈了。通配符的設(shè)定確實讓人難以理解

6隱患排查

我們應(yīng)該和官方一樣，放棄使用通配符授權(quán)，使用正確的轉(zhuǎn)義授權(quán)。排查所有使用了 _ 或 % 通配符的情況，統(tǒng)一整改為 \_ 或 \%。

以下 SQL 腳本由 AI 生成，請測試和謹慎使用。

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

    -- 授權(quán)里庫名是否使用了 "_" 或 "%" 通配符
    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 '是'
        ELSE '否'
    END AS '授權(quán)里庫名是否使用了"_"或"%"通配符',

    -- 授權(quán)里庫名是否使用了 "\_" 或 "\%" 轉(zhuǎn)義通配符
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM mysql.db
            WHERE INSTR(Db, '\\_') > 0 OR INSTR(Db, '\\%') > 0
        ) THEN '是'
        ELSE '否'
    END AS '授權(quán)里庫名是否使用了"\\_"或"\\%"轉(zhuǎn)義通配符',

    -- 授權(quán)里是否存在使用了表級授權(quán)的情況（排除指定的兩條記錄）
    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 '是'
        ELSE '否'
    END AS '授權(quán)里是否存在使用了表級授權(quán)的情況';

圖片

7如何規(guī)避

• 不要使用通配符授權(quán)，多數(shù)人不知道這個是通配符，用錯了，要轉(zhuǎn)義！
• 不要使用通配符授權(quán)，這是官方打算放棄的功能。
• 如果仍然需要使用通配符授權(quán)，不要混合使用，包括不要混合使用轉(zhuǎn)義通配符授權(quán)，不要混合使用多個通配符授權(quán)。
• 如果仍然需要使用通配符授權(quán)，仍然打算混合使用，要考慮清楚我文章里的測試結(jié)論，測試清楚，例如我的案例里，我可以保留通配符授權(quán)情況下，這樣授權(quán)。

mysql> SHOW GRANTS FOR app_user;
+-----------------------------------------------------------------------------------------------------+
| Grants for app_user@%                                                                               |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`%`                                                                |
| GRANT SELECT, INSERT, UPDATE, DELETE, 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)

參考資料

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

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