快捷導(dǎo)航

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

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

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

前言

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

背景

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

MySQL 更改權(quán)限

其實(shí)不然，在筆者進(jì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)限，它會(huì)自動(dòng)去重載權(quán)限表。但值得一提的是，刷新了權(quán)限表并不意味了你就擁有了對(duì)應(yīng)的權(quán)限，具體的生效需分為如下三種情況，官方文檔早有說明。

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

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

驗(yàn)證

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

# 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 用戶授予庫(kù)級(jí)的 SELECT 權(quán)限，發(fā)現(xiàn)對(duì)庫(kù)級(jí)別的更改可以實(shí)時(shí)生效。

# root 用戶授權(quán)
mysql> GRANT SELECT ON demp.* TO ouyanghan;
Query OK, 0 rows affected (0.00 sec)
# ouyanghan 用戶登錄查看權(quán)限（同一會(huì)話）
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 庫(kù)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demp               |
+--------------------+
2 rows in set (0.00 sec)

這是怎么回事，我也有找到官網(wǎng)錯(cuò)誤的高光時(shí)刻了？其實(shí)不然，仔細(xì)一看，原來官網(wǎng)的說明里面還有一條注意事項(xià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ù)庫(kù)名稱；因此，如果不實(shí)際更改到另一個(gè)數(shù)據(jù)庫(kù)，則可能無法看到此效果。

開啟緩存

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

# 查看此時(shí) 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，此時(shí)能查看到 db1 庫(kù)下表的具體信息。

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 失敗，報(bào)沒有權(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    |
+----+------+
# 切換不同的庫(kù)后，此時(shí)才發(fā)現(xiàn)權(quán)限被真正回收了，不能查看到對(duì)應(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'

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

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

總結(jié)

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

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

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

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

您可能感興趣的文章: