MySQL -- 許可權
CREATE USER 'ua'@'%' IDENTIFIED BY 'pa';
-
使用者名稱+地址才表示一個使用者,
ua@ip1
和ua@ip2
代表的是兩個不同的使用者 -
在磁碟上,往
mysql.user
表裡插入一行,由於沒有指定許可權,所有表示許可權的欄位都是N -
在記憶體裡,往陣列
acl_users
裡插入一個acl_user
物件,該物件的access
欄位的值為0
mysql> SELECT * FROM mysql.user WHERE user = 'ua'\G; *************************** 1. row *************************** Host: % User: ua Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: caching_sha2_password password_expired: N password_last_changed: 2019-03-17 14:33:56 password_lifetime: NULL account_locked: N Create_role_priv: N Drop_role_priv: N Password_reuse_history: NULL Password_reuse_time: NULL
許可權範圍
全域性許可權
作用於整個MySQL例項,許可權資訊儲存在mysql.user
,要給使用者ua賦一個最高許可權的語句如下
GRANT ALL PRIVILEGES ON *.* to 'ua'@'%' WITH GRANT OPTION;
-
在磁碟上,將
mysql.user
表裡的使用者'ua'@'%'
這一行中所有表示許可權的欄位都修改為Y -
在記憶體裡,從陣列
acl_users
中找到這個使用者對應的物件,將access
值(許可權位 )修改為二進位制的全1 -
這個
GRANT
命令執行完成後,如果有新的客戶端使用使用者ua登入成功- MySQL會為新連線 維護一個執行緒物件
-
然後從
acl_users
數組裡查到使用者ua的許可權,並將許可權值拷貝到這個執行緒物件 中 - 之後在這個連線中執行的語句,所有關於全域性許可權的判斷,都直接使用執行緒物件內部儲存的許可權位
-
GRANT
命令對於全域性許可權,同時更新了磁碟和記憶體,命令完成後即時生效- 接下來新建立的連線會使用新的許可權
- 對於一個已經存在的連線 ,它的全域性許可權不受影響 (因為判斷時採用的是執行緒物件內部的許可權值)
回收許可權
REVOKE ALL PRIVILEGES ON *.* FROM 'ua'@'%';
-
在磁碟上,將
mysql.user
表裡的使用者'ua'@'%'
這一行中所有表示許可權的欄位都修改為N -
在記憶體裡,從陣列
acl_users
中找到這個使用者對應的acl_user
物件,將access
的值修改為0
DB許可權
讓使用者'ua'@'%'
擁有庫db1的所有許可權
GRANT ALL PRIVILEGES ON db1.* to 'ua'@'%' WITH GRANT OPTION;
-
基於庫的許可權記錄儲存在
mysql.db
中,在記憶體裡則儲存在陣列acl_dbs
中 -
在磁碟上,往
mysql.db
表中插入一行記錄,所有許可權位的欄位設定為Y -
在記憶體中,增加一個物件到陣列
acl_dbs
,該物件的許可權位為全1 -
每次需要判斷一個使用者對一個數據庫讀寫許可權的時候,都需要遍歷一遍
acl_dbs
陣列(多執行緒共享)-
根據
user
、host
和db
找到匹配的物件,然後根據物件的許可權位來判斷
-
根據
-
GRANT
命令對於已經存在的連線 的影響,全域性許可權和基於DB的許可權是不一樣的- 全域性許可權:執行緒私有
- 基於DB的許可權:執行緒共享
mysql> SELECT * FROM mysql.db WHERE user = 'ua'\G; *************************** 1. row *************************** Host: % Db: db1 User: ua Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Execute_priv: Y Event_priv: Y Trigger_priv: Y
操作序列
時刻 | session A | session B | session C |
---|---|---|---|
T1 |
CONNECT(root,root); CREATE DATABASE db1; CREATE USER ‘ua‘@’%’ IDENTIFIED BY ‘pa’; GRANT SUPER ON *.* TO ‘ua‘@’%’; GRANT ALL PRIVILEGES ON db1.* TO ‘ua‘@’%’; |
||
T2 |
CONNECT(ua,pa) SET GLOBAL sync_binlog=1; (Query OK) CREATE TABLE db1.t(c INT); (Query OK) |
CONNECT(ua,pa) USE db1; |
|
T3 | REVOKE SUPER ON *.* FROM ‘ua‘@’%’; | ||
T4 |
SET GLOBAL sync_binlog=1; (Query OK) ALTER TABLE db1.t ENGINE=InnoDB; (Query OK) |
ALTER TABLE t ENGINE=InnoDB; (Query OK) |
|
T5 | REVOKE ALL PRIVILEGES ON db1.* FROM ‘ua‘@’%’; | ||
T6 |
SET GLOBAL sync_binlog=1; (Query OK) ALTER TABLE db1.t ENGINE=InnoDB; (ALTER command denied) |
ALTER TABLE t ENGINE=InnoDB;(Query OK) |
-
SET GLOBAL sync_binlog=1;
這個操作需要SUPER
許可權 -
雖然使用者ua的
SUPER
許可權在T3時刻被回收了,但在T4時刻執行SET GLOBAL
的時候,許可權驗證還是通過了-
這是因為
SUPER
是全域性許可權,這個資訊在執行緒物件 中,而REVOKE
操作影響不了執行緒物件
-
這是因為
-
在T4時刻去掉使用者ua對db1庫的所有許可權後,session B在T6時刻再操作db1的表時,就會報許可權不足
-
這是因為
acl_dbs
是一個全域性陣列 ,所有執行緒判斷db許可權都會用該陣列 -
因此
REVOKE
操作會立馬影響到session
-
這是因為
-
特殊邏輯
- 如果當前會話已經在某個db裡面,之前use這個db時拿到的庫許可權就會儲存在會話變數 中
-
session C在T2執行了
USE db1
,拿到這個庫的許可權,在切換出db1之前,一直對db1有許可權
表許可權和列許可權
CREATE TABLE db1.t1(id INT, a INT); GRANT ALL PRIVILEGES ON db1.t1 TO 'ua'@'%' WITH GRANT OPTION; GRANT SELECT(id), INSERT(id,a) ON db1.t1 TO 'ua'@'%' WITH GRANT OPTION;
-
表許可權定義在表
mysql.tables_priv
,列許可權定義在表mysql.columns_priv
-
這兩類許可權組合起來存放在記憶體的hash結構
:
column_priv_hash
-
這兩類許可權組合起來存放在記憶體的hash結構
:
-
跟DB許可權類似,這兩個許可權在每次
GRANT
的時候都會修改資料表,也會同步修改記憶體的hash結構 - 因此這兩類許可權的操作,也會立馬影響到已經存在的連線
FLUSH PRIVILEGES
-
FLUSH PRIVILEGES
命令會清空acl_users
陣列( 全域性許可權 )-
然後從
mysql.user
表中讀取資料重新載入,重新構造一個acl_users
陣列 - 以資料表中的資料為準,會將全域性許可權 的記憶體陣列重新載入一遍
-
然後從
- 同樣的,對於DB許可權 、表許可權和列許可權 ,MySQL也做了同樣的處理
-
如果記憶體的許可權資料
和磁碟資料表的許可權資料
相同的話,不需要執行
FLUSH PRIVILEGES
-
如果都是用
GRANT/REVOKE
語句執行的話,記憶體和資料表的資料應該保持同步更新 的 -
正常情況下,在執行
GRANT
命令之後,沒有必要跟著執行FLUSH PRIVILEGES
命令
-
如果都是用
使用場景
-
當資料表的許可權資料與記憶體中的許可權資料不一致
,通過
FLUSH PRIVILEGES
來重建記憶體資料 ,達到一致狀態 - 這種不一致的狀態往往由於不規範的操作 導致的,例如直接用DML語句作業系統許可權表
不規範操作1
時刻 | client A | client B |
---|---|---|
T1 |
CONNECT(root,root) CREATE USER ‘ua‘@’%’ IDENTIFIED BY ‘pa’; |
|
T2 |
CONNECT(ua,pa) (Connect OK) DISCONNECT |
|
T3 | DELETE FROM mysql.user WHERE user=’ua’; | |
T4 |
CONNECT(ua,pa) (Connect OK) DISCONNECT |
|
T5 | FLUSH PRIVILEGES; | |
T6 |
CONNECT(ua,pa) (Access Denied) |
-
T3時刻雖然使用了
DELETE
語句刪除了使用者ua,但在T4時刻,仍然可以用使用者ua連線成功-
因為記憶體中
acl_users
陣列中還有這個使用者,系統判斷時認為使用者還正常存在的
-
因為記憶體中
-
T5時刻執行過
FLUSH PRIVILEGES
命令後,記憶體更新,T6時刻就會報Access Denied
錯誤 - 直接作業系統許可權表是很不規範的操作
不規範操作2
時刻 | client A |
---|---|
T1 |
CONNECT(root,root) CREATE USER ‘ua‘@’%’ IDENTIFIED BY ‘pa’; |
T2 | DELETE FROM mysql.user WHERE user=’ua’; |
T3 |
GRANT SUPER ON *.* TO ‘ua‘@’%’ WITH GRANT OPTION; (ERROR 1133 (42000): Can’t find any matching row in the user table) |
T4 |
CREATE USER ‘ua‘@’%’ IDENTIFIED BY ‘pa’; (ERROR 1396 (HY000): Operation CREATE USER failed for ‘ua‘@’%’) |
mysql.user
參考資料
《MySQL實戰45講》
轉載請註明出處:http://zhongmingmao.me/2019/03/17/mysql-grant-flush-privileges/
訪問原文「MySQL -- 許可權」獲取最佳閱讀體驗並參與討論