MySQL 權限變更，何時生效？詳情 - mysql,權限愛可生開源社區博客

本文講述了對三種級別權限的變更後，使其生效的方法。

作者：歐陽涵，愛可生團隊 DBA 成員，看到下雨知道要打傘的 2.5 次元愛好者~

愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。

本文約 1200 字，預計閲讀需要 4 分鐘。

前言

Uproxy 是愛可生研發的雲樹® DMP 產品的一個高效的讀寫中間件，維護了自身到後端 MySQL 數據庫之間的連接池，用以保持到數據庫後端的 長連接。

背景

近期客户反饋，通過 Uproxy 連接數據庫，使用 REVOKE 回收全局庫表 *.* 的某個權限後，卻還能看到沒有對應權限的庫，並能進行操作，FLUSH PRIVILEGES 也無效，難道這是 MySQL 的 bug？

MySQL 更改權限

其實不然，在筆者進行闡述前，先來説明一下 MySQL 更改權限的兩種方式：

1 直接修改授權表

使用 INSERT、UPDATE 或 DELETE 等語句直接修改授權表（不推薦）。

update mysql.user set Select_priv='N' where user='ouyanghan' and host='%';

2 使用 GRANT/REVOKE 語句

使用 GRANT/REOVKE 來授予及回收權限（推薦）。

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

其中，第一種需要通過 FLUSH PRIVILEGES 來重新加載權限表。而第二種通過 MySQL 內部命令去更新權限，它會自動去重載權限表。但值得一提的是，刷新了權限表並不意味了你就擁有了對應的權限，具體的生效需分為如下三種情況，官方文檔早有説明。

對錶級別 db_name.table_name 和列級別，權限更改將在客户端下一次請求時生效，也就是立即生效。
對庫級別權限 db_name.* 更改在客户端執行 USE db_name 語句後生效。
對全局級別權限 *.* 更改對於已連接的會話中不受影響，僅在新連接的會話中生效。

對錶、列和全局級別權限生效的方式，我本地測試起來沒有問題，大家看上方的文字也十分容易理解，這裏就不佔用大家的時間，但對庫級權限的更改，官網説是要 USE db_name 才能生效，但實際上卻是立即生效的。

驗證

創建 ouyanghan 用户，此時該用户只有 usage 權限，且只能看到 information_schema 庫。

# root 用户登錄，創建新用户
mysql> CREATE USER ouyanghan IDENTIFIED by 'oyh123';

# ouyanghan 用户登錄，查看權限
mysql> SHOW GRANTS;
+---------------------------------------+
| Grants for ouyanghan@%                |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'ouyanghan'@'%' |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

給 ouyanghan 用户授予庫級的 SELECT 權限，發現對庫級別的更改可以實時生效。

# root 用户授權
mysql> GRANT SELECT ON demp.* TO ouyanghan;
Query OK, 0 rows affected (0.00 sec)

# ouyanghan 用户登錄查看權限（同一會話）
mysql> SHOW GRANTS;
+---------------------------------------------+
| Grants for ouyanghan@%                      |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'ouyanghan'@'%'       |
| GRANT SELECT ON `demp`.* TO 'ouyanghan'@'%' |
+---------------------------------------------+
2 rows in set (0.00 sec)

# 並且能查看到 demp 庫
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demp               |
+--------------------+
2 rows in set (0.00 sec)

這是怎麼回事，我也有找到官網錯誤的高光時刻了？其實不然，仔細一看，原來官網的説明裏面還有一條注意事項：

Client applications may cache the database name; thus, this effect may not be visible to them without actually changing to a different database.

客户端應用程序可以緩存數據庫名稱；因此，如果不實際更改到另一個數據庫，則可能無法看到此效果。

開啓緩存

那麼我們把 MySQL 緩存開啓一下，並賦予一定的緩存大小。

# 查看此時 ouyanghan 用户的權限
mysql> SHOW GRANTS FOR demo;
+----------------------------------------+
| Grants for demo@%                      |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'demo'@'%'       |
| GRANT SELECT ON `demp`.* TO 'demo'@'%' |
| GRANT SELECT ON `db1`.* TO 'demo'@'%'  |
+----------------------------------------+
3 rows in set (0.00 sec)

# 開啓緩存，並賦予大小
mysql> SET GLOBAL query_cache_type = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SET GLOBAL query_cache_size = 1000000;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

ouyanghan 用户登錄 MySQL，此時能查看到 db1 庫下表的具體信息。

mysql> USE db1;
Database changed

mysql> SELECT * FROM t1;
+----+------+
| id | c    |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

root 用户回收權限。

mysql> REVOKE SELECT ON db1.* FROM ouyanghan;
Query OK, 0 rows affected (0.00 sec)

ouyanghan 用户查看權限。

# 發現權限已經被回收
mysql> SHOW GRANTS FOR ouyanghan;
+---------------------------------------------+
| Grants for ouyanghan@%                      |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'ouyanghan'@'%'       |
| GRANT SELECT ON `demp`.* TO 'ouyanghan'@'%' |
+---------------------------------------------+
2 rows in set (0.00 sec)

# use db1 失敗，報沒有權限，但仍能查看到裏面的內容
mysql> USE db1;
ERROR 1044 (42000): Access denied for user 'ouyanghan'@'%' to database 'db1'

mysql> SELECT * FROM db1.t1;
+----+------+
| id | c    |
+----+------+
|  1 | a    |
+----+------+

# 切換不同的庫後，此時才發現權限被真正回收了，不能查看到對應的內容了
mysql> USE demp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM db1.t1;
ERROR 1142 (42000): SELECT command denied to user 'ouyanghan'@'localhost' for table 't1'

可能有嚴謹的看官有疑問了：“你對錶、列級別的權限做更改的時候，也沒見你開啓 MySQL 查詢緩存啊，説不定表級和列級的權限做更改的生效時間，也需要去 USE db_name 一下呢？”

嘿你還別説，還真是，於是筆者火急火燎又去測試了一下，發現對錶級和列級的權限做更改，它就是立馬生效的，不信你就去試試吧！

總結

不管是使用語句直接修改授權表，還是用 MySQL 內部命令去更改權限，都要遵守下面的生效規則：

對錶級別 db_name.table_name 和列級別，權限更改將在客户端下一次請求時生效，也就是立即生效。
對庫級別權限 db_name.* 的更改在客户端執行 USE db_name 語句後生效（需要開啓 query_cache_type 參數，當然，通常為了 MySQL 性能，這個參數是不建議開啓的，且在 MySQL 8.0 版本中已經被移除了）。
對全局級別權限 *.* 的更改對於已連接的會話中不受影響，僅在新連接的會話中生效。

最後，相信在座各位，已經知道如何解決筆者開始遇到的權限不生效的問題了吧？那就是刷新 Uproxy 連接池。

愛可生開源社區博客

愛可生開源社區博客

博客 / 詳情