MySQL存取控制實現原理

標籤：

MySQL存取控制實現原理

 

MySQL 存取控制實際上由兩個功能模組共同組成，從第一篇的第二章架構組成中可以看
到，一個是負責 “看守 MySQL 大門”的使用者管理模組，另一個就是負責監控來訪者每一個動
作的存取控制模組。使用者管理模組決定造訪客人能否進門，而存取控制模組則決定每個客人
進門能拿什麼不能拿什麼。下面是一張 MySQL 中實現存取控制的簡單流程圖(見圖 4-2)：

1、 使用者管理
我們先看看使用者管理模組是如何工作的。在 MySQL 中，使用者存取控制部分的實現比較簡
單，所有授權使用者都存放在一個系統資料表中： mysql.user，當然這個表不僅僅存放了授權使用者
的基本資料，還存放有部分細化的許可權資訊。使用者管理模組需要使用的資訊很少，主要就是
Host，User，Password 這三項，都在 mysql.user 表中，如下：
sky@localhost : (none) 12:35:04> USE mysql;
Database changed
sky@localhost : mysql 12:35:08> DESC user;
+---------------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
... ...
+---------------+--------------------+------+-----+---------+-------+
一個使用者要想訪問 MySQL，至少需要提供上面列出的這三項資料，MySQL 才能判斷是否
該讓他 “進門”。這三項實際上由量部分組成：訪問者來源的主機名稱（或者主機 IP 位址資訊 ）
和訪問者的來訪 “暗號”（登入使用者名稱和登入密碼），這兩部分中的任何一個沒有能夠匹配上
都無法讓看守大門的使用者管理模組乖乖開門。其中 Host 資訊存放的是 MySQL 允許所對應的
User 的信任主機，可以是某個具體的主機名稱（如： mytest）或網域名稱（如：www.domain.com），
也可以是以“%”來充當萬用字元的某個網域名稱集合（如：%.domain.com）；也可以是一個具體的
IP 位址(如：1.2.3.4)，同樣也可以是存在萬用字元的網域名稱集合(如：1.2.3.%)；還可以用“%”
來代表任何主機，就是不對訪問者的主機做任何限制。如以下設定：
root@localhost : mysql 01:18:12> SELECT host,user,password FROM user ORDER BY
user;
+--------------------+------+-------------------------------------------+
| host | user | password |
+--------------------+------+-------------------------------------------+
| % | abc | |
| *.jianzhaoyang.com | abc | |
| localhost | abc | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 1.2.3.4 | abc | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 1.2.3.* | def | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| % | def | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| localhost | def | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
... ...
+--------------------+------+-------------------------------------------+
但是這裡有一個比較特殊的訪問限制，如果要通過 localhost 訪問的話，必須要有一條
專門針對 localhost 的授權資訊，即使不對任何主機做限制也不行。如下例所示，存在 def@%
的使用者佈建，但是如果不使用-h 參數來訪問，則登入會被拒絕，因為 mysql 在預設情況下
會串連 localhost：
sky@sky:~$ mysql -u def -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘def‘@‘localhost‘ (using
password: YES)
但是當通過-h 參數，明確指定了訪問的主機地址之後就沒問題了，如下：
sky@sky:~$ mysql -u def -p -h 127.0.0.1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.0.51a-log Source distribution
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the buffer.
def@127.0.0.1 : (none) 01:26:04>
如果我們有一條 localhost 的訪問授權則可以不使用-h 參數來指定登入 host 而串連默
認的 localhost：
sky@sky:~$ mysql -u abc -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.0.51a-log Source distribution
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the buffer.
abc@localhost : (none) 01:27:19> exit
Bye
如果 MySQL 正在運行之中的時候，我們對系統做了許可權調整，那調整之後的許可權什麼時
候會生效呢？
我們先瞭解何時 MySQL 存放於記憶體結構中的許可權資訊被更新：FLUSH PRIVILEGES 會強
行讓 MySQL 更新 Load 到記憶體中的許可權資訊； GRANT、REVOKE 或者 CREATE USER 和 DROP USER
操作會直接更新記憶體中俄許可權資訊；重啟 MySQL 會讓 MySQL 完全從 grant tables 中讀取權
限資訊。
那記憶體結構中的許可權資訊更新之後對已經串連上的使用者何時生效呢？
對於 Global Level 的許可權資訊的修改，僅僅只有更改之後建立串連才會用到，對於已
經串連上的 session 並不會受到影響。而對於 Database Level 的許可權資訊的修改，只有當
用戶端請求執行了“USE database_name”命令之後，才會在重新校正中使用到新的許可權信
息。所以有些時候如果在做了比較緊急的 Global 和 Database 這兩個 Level 的許可權變更之後 ，
可能需要通過“KILL”命令將已經串連在 MySQL 中的 session 殺掉強迫他們重新串連以使
用更新後的許可權。對於 Table Level 和 Column Level 的許可權，則會在下一次需要使用到該
許可權的 Query 被請求的時候生效，也就是說，對於應用來講，這兩個 Level 的許可權，更新之
後立刻就生效了，而不會需要執行“KILL”命令。
2、 存取控制
當用戶端串連通過使用者管理模組的驗證，可串連上 MySQL Server 之後，就會發送各種
Query 和 Command 給 MySQL Server，以實現用戶端應用的各種功能。當 MySQL 接收到客戶
端的請求之後，存取控制模組是需要校正該使用者是否滿足提交的請求所需要的許可權。許可權校
驗過程是從最大範圍的許可權往最小範圍的許可權開始依次校正所涉及到的每個對象的每個權
限。
在驗證所有要求的權限的時候， MySQL 首先會尋找儲存在記憶體結構中的許可權資料，首先查
找 Global Level 許可權，如果要求的權限在 Global Level 都有定義（GRANT 或者 REVOKE），
則完成許可權校正（通過或者拒絕），如果沒有找到所有許可權的定義，則會繼續往後尋找
Database Level 許可權，進行 Global Level 未定義的要求的權限的校正，如果仍然沒有能夠
找到所有要求的權限的定義，MySQL 會繼續往更小範圍的許可權定義域尋找，也就是 Table
Level，最後則是 Column Level 或者 Routine Level。
下面我們就以用戶端通過 abc@localhost 串連後請求如下 Query 我為例：
SELECT id,name FROM test.t4 where status = ‘deleted‘;

在前面我們瞭解到 MySQL 的 grant tables 有 mysql.user，mysql.db，mysql.host，
mysql.table_priv 和 mysql.column_priv 這五個，我想出了 mysql.host 之外的四個都是非
常容易理解的，每一個錶針對 MySQL 中的一種邏輯對象，存放某一特定 Level 的許可權，唯獨
mysql.host 稍有區別。我們現在就來看看 mysql.host 許可權表到底在 MySQL 的存取控制中充
當了一個什麼樣的角色呢？
mysql.host 在 MySQL 存取控制模組中所實現的功能比較特殊，和其他幾個 grant tables
不太一樣。首先是 mysql.host 中的許可權資料不是（也不能）通過 GRANT 或者 REVOKE 來授予
或者去除，必須通過手工通過 INSERT、UPDATE 和 DELETE 命令來修改其中的資料。其次是
其中的許可權資料無法單獨生效，必須通過和 mysql.db 許可權表的資料一起才會生效。而且僅
當 mysql.db 中存在不完整（某些情境下的特殊設定）的時候，才會促使存取控制模組再結
合 mysql.host 中尋找是否有相應的補充許可權資料實現以達到許可權校正的目的，就比如
中所示。在 mysql.db 中無法找到滿足許可權校正的所有條件的資料（db.User = ‘abc‘ AND
db.host = ‘localhost‘ AND db.Database_name = ‘test‘），則說明在 mysql.db 中無法完
成許可權校正，所以也不會直接就校正 db.Select_priv 的值是否為‘Y‘。但是 mysql.db 中有
db.User = ‘abc‘ AND db.Database_name = ‘test‘ AND db.host = ‘‘ 這樣一條許可權資訊
存在，大家可能注意到了這條許可權資訊中的 db.host 中是空值，注意是空值而不是‘%‘這個
萬用字元哦。當 MySQL 注意到有這樣一條許可權資訊存在的時候，就該是 mysql.host 中所存放
的許可權資訊出場的時候了。這時候，MySQL 會檢測 mysql.host 中是否存在滿足如下條件的
許可權資訊：host.Host = ‘localhost‘ AND host.Db = ‘test‘。如果存在，則開始進行
Select_priv 許可權的校正。由於許可權資訊存在於 mysql.db 和 mysql.host 兩者之中，而且是
兩者資訊合并才能滿足要求，所以 Select_priv 的校正也需要兩表都為‘Y‘才能滿足要求，
通過校正。
我們已經清楚，MySQL 的許可權是授予“username@hostname”的，也就是說，至少需要
使用者名稱和主機名稱二者才能確定一個訪問者的許可權。又由於 hostname 可以是一個含有萬用字元
的網域名稱，也可以是一個含有萬用字元的 IP 位址段。那麼如果同一個使用者有兩條許可權資訊，一
條是針對特定網域名稱的，另外一個是含有萬用字元的網域名稱，而且前者屬於後者包含。這時候 MySQL
如何來確定許可權資訊呢？實際上 MySQL 永遠優先考慮更精確範圍的許可權。在 MySQL 內部會按
照 username 和 hostname 作一個排序，對於相同 username 的許可權，其 host 資訊越接近訪問
者的來源 host，則排序位置越靠前，則越早被校正使用到。而且， MySQL 在許可權校正過程中 ，
只要找到匹配的許可權之後，就不會再繼續往後尋找是否還有匹配的許可權資訊，而直接完成校
驗過程。
大家應該也看到了在 mysql.user 這個許可權表中有 max_questions，max_updates，
max_connections，max_user_connections 這四列，前面三列是從 MySQL4.0.2 版本才開始
有的，其功能是對訪問使用者進行每小時所使用資源的限制，而最後的 max_user_connections
則是從 MySQL5.0.3 版本才開始有的，他和 max_connections 的區別是限制耽擱使用者的串連
總次數，而不是每小時的串連次數。而要使這四項限制生效，需要在建立使用者或者給使用者授
權的時候加上以下四種子句：
max_questions ： WITH MAX_QUERIES_PER_HOUR n；
max_updates ： WITH MAX_UPDATES_PER_HOUR n；
max_connections ： WITH MAX_CONNECTIONS_PER_HOUR n；
max_user_connections： MAX_USER_CONNECTIONS。
四個子句可以同時使用，如：
“ WITH MAX_QUERIES_PER_HOUR 5000 MAX_CONNECTIONS_PER_HOUR 10
MAX_USER_CONNECTIONS 10000”。

 

 

MySQL存取控制實現原理