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

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

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

GRANT ALL ON `db_1`.* TO  test_user;

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

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

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

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

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

3如何避免這個(gè)問(wèn)題？

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

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

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

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

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

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

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

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

4整改過(guò)程中的風(fēng)險(xiǎn)

在你意識(shí)到這個(gè)問(wèn)題后，可能會(huì)急于對(duì)現(xiàn)有授權(quán)進(jìn)行整改，但需要注意兩種場(chǎng)景：

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

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

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

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

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

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

然后，隨著業(yè)務(wù)的擴(kuò)展，你意識(shí)到應(yīng)用程序需要自動(dòng)維護(hù)分區(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)義通配符（\_）混合使用的場(chǎng)景。

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

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

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

權(quán)限測(cè)試

我們來(lái)實(shí)際測(cè)試一下授權(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)限生效了，但原來(lái)的 SELECT、INSERT、UPDATE 和 DELETE

解釋與分析

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

那么這是一個(gè) MySQL 的 Bug 嗎？

最初，我也認(rèn)為這可能是個(gè) S2 級(jí)別的 Bug，并向官方提交了報(bào)告[1]。

但深入調(diào)查后發(fā)現(xiàn)，這實(shí)際上是 MySQL 授權(quán)機(jī)制的一個(gè)已知行為，而不是 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.

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

這意味著 MySQL 未來(lái)會(huì)徹底廢棄通配符在授權(quán)中的使用。更進(jì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)多個(gè)授權(quán)中涉及通配符時(shí)，MySQL 只會(huì)考慮第一個(gè)匹配的授權(quán)。

我的案例

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

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

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

更進(jìn)一步測(cè)試

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

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

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

為更清楚說(shuō)明這一問(wèn)題，我在多個(gè) MySQL 版本中進(jìn)行了進(jìn)一步測(cè)試，結(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”.

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

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

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

6隱患排查

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

以下 SQL 腳本由 AI 生成，請(qǐng)測(cè)試和謹(jǐn)慎使用。

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

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

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

    -- 授權(quán)里是否存在使用了表級(jí)授權(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)里是否存在使用了表級(jí)授權(quán)的情況';

圖片

7如何規(guī)避

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