快捷導(dǎo)航

MySQL權(quán)限變更何時生效

更新時間：2023年10月10日 10:20:38 作者：愛可生開源社區(qū)

本文為大家講述了對三種級別權(quán)限的變更后,使其生效的方法,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪<BR>

前言

Uproxy 是愛可生研發(fā)的云樹® DMP 產(chǎn)品的一個高效的讀寫中間件，維護了自身到后端 MySQL 數(shù)據(jù)庫之間的連接池，用以保持到數(shù)據(jù)庫后端的 長連接。

背景

近期客戶反饋，通過 Uproxy 連接數(shù)據(jù)庫，使用 REVOKE 回收全局庫表 *.* 的某個權(quán)限后，卻還能看到?jīng)]有對應(yīng)權(quán)限的庫，并能進行操作，FLUSH PRIVILEGES 也無效，難道這是 MySQL 的 bug？

MySQL 更改權(quán)限

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

1 直接修改授權(quán)表

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

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

2 使用 GRANT/REVOKE 語句

使用 GRANT/REOVKE 來授予及回收權(quán)限（推薦）。

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 來重新加載權(quán)限表。而第二種通過 MySQL 內(nèi)部命令去更新權(quán)限，它會自動去重載權(quán)限表。但值得一提的是，刷新了權(quán)限表并不意味了你就擁有了對應(yīng)的權(quán)限，具體的生效需分為如下三種情況，官方文檔早有說明。

對表級別 db_name.table_name 和列級別，權(quán)限更改將在客戶端下一次請求時生效，也就是立即生效。
對庫級別權(quán)限 db_name.* 更改在客戶端執(zhí)行 USE db_name 語句后生效。
對全局級別權(quán)限 *.* 更改對于已連接的會話中不受影響，僅在新連接的會話中生效。

對表、列和全局級別權(quán)限生效的方式，我本地測試起來沒有問題，大家看上方的文字也十分容易理解，這里就不占用大家的時間，但對庫級權(quán)限的更改，官網(wǎng)說是要 USE db_name ;才能生效，但實際上卻是立即生效的。

驗證

創(chuàng)建 ouyanghan 用戶，此時該用戶只有 usage 權(quán)限，且只能看到 information_schema 庫。

# root 用戶登錄，創(chuàng)建新用戶
mysql> CREATE USER ouyanghan IDENTIFIED by 'oyh123';
# ouyanghan 用戶登錄，查看權(quán)限
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 權(quán)限，發(fā)現(xiàn)對庫級別的更改可以實時生效。

# root 用戶授權(quán)
mysql> GRANT SELECT ON demp.* TO ouyanghan;
Query OK, 0 rows affected (0.00 sec)
# ouyanghan 用戶登錄查看權(quán)限（同一會話）
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)

這是怎么回事，我也有找到官網(wǎng)錯誤的高光時刻了？其實不然，仔細一看，原來官網(wǎng)的說明里面還有一條注意事項：

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

客戶端應(yīng)用程序可以緩存數(shù)據(jù)庫名稱；因此，如果不實際更改到另一個數(shù)據(jù)庫，則可能無法看到此效果。

開啟緩存

那么我們把 MySQL 緩存開啟一下，并賦予一定的緩存大小。

# 查看此時 ouyanghan 用戶的權(quán)限
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 用戶回收權(quán)限。

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

ouyanghan 用戶查看權(quán)限。

# 發(fā)現(xiàn)權(quán)限已經(jīng)被回收
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 失敗，報沒有權(quán)限，但仍能查看到里面的內(nèi)容
mysql> USE db1;
ERROR 1044 (42000): Access denied for user 'ouyanghan'@'%' to database 'db1'
mysql> SELECT * FROM db1.t1;
+----+------+
| id | c    |
+----+------+
|  1 | a    |
+----+------+
# 切換不同的庫后，此時才發(fā)現(xiàn)權(quán)限被真正回收了，不能查看到對應(yīng)的內(nèi)容了
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'

可能有嚴謹?shù)目垂儆幸蓡柫耍?ldquo;你對表、列級別的權(quán)限做更改的時候，也沒見你開啟 MySQL 查詢緩存啊，說不定表級和列級的權(quán)限做更改的生效時間，也需要去 USE db_name 一下呢？”

嘿你還別說，還真是，于是筆者火急火燎又去測試了一下，發(fā)現(xiàn)對表級和列級的權(quán)限做更改，它就是立馬生效的，不信你就去試試吧！

總結(jié)

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

對表級別 db_name.table_name 和列級別，權(quán)限更改將在客戶端下一次請求時生效，也就是立即生效。
對庫級別權(quán)限 db_name.* 的更改在客戶端執(zhí)行 USE db_name 語句后生效（需要開啟 query_cache_type 參數(shù)，當(dāng)然，通常為了 MySQL 性能，這個參數(shù)是不建議開啟的，且在 MySQL 8.0 版本中已經(jīng)被移除了）。
對全局級別權(quán)限 *.* 的更改對于已連接的會話中不受影響，僅在新連接的會話中生效。

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

以上就是MySQL權(quán)限變更何時生效的詳細內(nèi)容，更多關(guān)于MySQL權(quán)限變更生效的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: