在 MySQL 的日常管理過程中，大家或多或少會遇到許可權認證相關的問題。
例如，本來能夠正常執行的操作，可能在新增一個賬號或授權後就突然失敗了。
這種現象往往讓人誤以為是 bug，但很多時候，其實並不是。
下面，將透過兩個案例來闡明 MySQL 許可權認證中的具體優先原則，並在此基礎上，分析以下問題：
- 透過 DML 操作修改許可權表後，為什麼需要執行 FLUSH PRIVILEGES？
- 許可權表中記錄的順序是否會影響許可權認證的結果？
- 在透過 GRANT 或 REVOKE 修改許可權後，是否需要 KILL 已有連線才能使新許可權生效？
案例 1
- 首先，建立一個賬號：
create user u1@'%' identified by 'password1';，此時，在例項本地透過mysql -h10.0.0.108 -uu1 -p'password1'可以登入例項。 - 接著，建立一個新賬號：
create user u1@'10.%' identified by 'password2';，使用者名稱不變，改變的只是主機名。使用之前的密碼登入會報錯，提示 Access denied，需使用 password2 登入。 - 繼續建立一個新賬號：
create user u1@'10.0.0.0/255.255.255.0' identified by 'password3';，此時，使用 password1、password2 登入會報錯，登入密碼只能指定為 password3。 - 繼續建立一個新賬號：
create user u1@'10.0.0.0/24' identified by 'password4';，使用其它密碼會報錯，登入密碼只能指定為 password4。 - 繼續建立一個新賬號：
create user u1@'10.0.0.108' identified by 'password5';，使用其它密碼會報錯，登入密碼只能指定為 password5。
現象就是每建立一個新的賬號，之前的密碼就失效了，只能使用新的密碼來登入。
該案例適用於 MySQL 8.0 及以上版本。如果是在 MySQL 5.7 上測試，只有前三步有效。
案例 2
這個案例演示的是資料庫庫名中包含萬用字元的場景。
create user u2@'%' identified by '123456';
create database my_db;
create table my_db.t1(id int primary key);
insert into my_db.t1 values(1);
grant select on my_db.* to u2@'%';
# mysql -h127.0.0.1 -uu2 -p123456 -e 'select * from my_db.t1;'
+----+
| id |
+----+
| 1 |
+----+
最初的需求是為my_db資料庫授予庫級別的查詢許可權，因此透過上述方式進行了授權。
但實際上，庫名中的_是個萬用字元，它能夠匹配任意一個字元。因此，上面的 SELECT 許可權不僅適用於my_db，同樣也適用於my1db、my2db等名稱相似的資料庫。
鑑於之前的授權不夠嚴謹，我在之後的授權中使用了轉義符\對_進行了轉義，目的是隻針對my_db進行授權。沒想到，授權完成後，再次執行之前的 SELECT 操作會報錯。
grant insert on `my\_db`.* to u2@'%';
# mysql -h127.0.0.1 -uu2 -p123456 -e 'select * from my_db.t1;'
ERROR 1142 (42000) at line 1: SELECT command denied to user 'u2'@'127.0.0.1' for table 't1'
分析案例 1
MySQL 在接收到客戶端連線後，首先會透過cached_acl_users_for_name獲取與該使用者名稱相關的 ACL（訪問控制列表）使用者列表。接著，MySQL 會遍歷該列表，檢查客戶端的使用者名稱和主機名（IP）是否與列表中的記錄匹配。如果匹配，則直接退出迴圈，不再檢查其它記錄。
以案例 1 為例，u1 對應的使用者列表包含 5 條記錄：u1@'%'，u1@'10.%'，u1@'10.0.0.0/255.255.255.0'，u1@'10.0.0.0/24'，u1@'10.0.0.108'。實際上，這 5 條記錄都能與客戶端匹配，但程式碼的處理邏輯是，一旦找到匹配項，MySQL 就不會再檢查其它記錄，即使該匹配項的密碼不正確。所以，使用者列表中記錄的順序很關鍵。
// mysql-8.4.2/sql/auth/sql_authentication.cc
static bool find_mpvio_user(THD *thd, MPVIO_EXT *mpvio) {
...
if (likely(acl_users)) {
list = cached_acl_users_for_name(mpvio->auth_info.user_name);
}
if (list) {
for (auto it = list->begin(); it != list->end(); ++it) {
ACL_USER *acl_user_tmp = (*it);
if ((!acl_user_tmp->user ||
!strcmp(mpvio->auth_info.user_name, acl_user_tmp->user)) &&
acl_user_tmp->host.compare_hostname(mpvio->host, mpvio->ip)) {
...
break;
}
}
}
...
}
下面，我們分析下 ACL 使用者列表的生成邏輯，這個是在rebuild_cached_acl_users_for_name函式中實現的。
// mysql-8.4.2/sql/auth/sql_auth_cache.cc
void rebuild_cached_acl_users_for_name(void) {
...
// 遍歷 acl_users，將每個 ACL_USER 物件根據使用者名稱分組到 name_to_userlist 中。
for (ACL_USER *acl_user = acl_users->begin(); acl_user != acl_users->end();
++acl_user) {
std::string name = acl_user->user ? acl_user->user : "";
(*name_to_userlist)[name].push_back(acl_user);
// 匿名使用者（即使用者名稱為空的物件）會被單獨新增到 anons 列表中。
if (!name.compare("")) anons.push_back(acl_user);
}
// 遍歷 name_to_userlist，將 anons 中的匿名使用者新增到每個非匿名使用者的 ACL 列表中。
for (auto it = name_to_userlist->begin(); it != name_to_userlist->end();
++it) {
std::string name = it->first;
if (!name.compare("")) continue;
auto *list = &it->second;
for (auto it2 = anons.begin(); it2 != anons.end(); ++it2) {
list->push_back(*it2);
}
// 對每個使用者列表進行排序。
list->sort(ACL_USER_compare());
}
}
這個函式的功能比較簡單，就是遍歷 acl_users，將每個 ACL_USER 物件根據使用者名稱分組到 name_to_userlist 中。
name_to_userlist 是一個雜湊表，其鍵是使用者名稱，值是一個列表，列表中儲存所有擁有相同使用者名稱的 ACL_USER 物件。
重點是最後一步，會對每個使用者列表進行排序，這個排序直接影響了列表中 ACL_USER 物件的順序。
排序命令中的ACL_USER_compare()是一個比較函式，用於對 ACL_USER 物件進行排序。
下面我們看看這個函式的實現細節。
// mysql-8.4.2/sql/auth/sql_auth_cache.cc
bool ACL_USER_compare::operator()(const ACL_USER &a, const ACL_USER &b) {
if (a.host.ip != 0) {
if (b.host.ip != 0) {
/* Both elements have specified IPs. The one with the greater mask goes
* first. */
if (a.host.ip_mask_type != b.host.ip_mask_type)
return a.host.ip_mask_type < b.host.ip_mask_type;
if (a.host.ip_mask == b.host.ip_mask) return a.user > b.user;
return a.host.ip_mask > b.host.ip_mask;
}
/* The element with the IP goes first. */
return true;
}
/* The element with the IP goes first. */
if (b.host.ip != 0) return false;
/* None of the elements has IP defined. Use default comparison. */
return a.sort > b.sort;
}
該函式的實現邏輯如下：
- 如果兩個物件都指定了 IP 地址（host.ip != 0），則首先比較掩碼型別（ip_mask_type），其次是掩碼值（ip_mask）。如果掩碼值相等，則會比較使用者名稱（user）。
- 如果只有一個物件指定了 IP 地址，則該物件應該排在前面。
- 如果兩個物件都沒有指定 IP 地址，則比較它們的排序值（sort）。
ip_mask_type 是一個enum_ip_mask_type列舉型別的變數，用於指定當前 ACL 使用者的 IP 掩碼型別。
enum enum_ip_mask_type {
ip_mask_type_implicit,
ip_mask_type_cidr,
ip_mask_type_subnet
};
其中：
- ip_mask_type_implicit：只指定了 IP 地址，沒有掩碼。案例 1 中的
10.0.0.108屬於這個型別。 - ip_mask_type_cidr：以 CIDR 形式指定了 IP 地址和掩碼。案例 1 中的
10.0.0.0/24屬於這個型別。 - ip_mask_type_subnet：以子網掩碼的形式指定了 IP 地址和掩碼。案例 1 中的
10.0.0.0/255.255.255.0屬於這型別。
由於在初始化 ACL_USER 物件時，ip_mask_type 的預設值為 ip_mask_type_implicit，所以u1@'%'和u1@'10.%'這兩個物件的 IP 掩碼型別也是 ip_mask_type_implicit。只不過這兩個物件沒有指定 IP 地址，所以他們的排名比較靠後。
基於上述分析，這些物件在列表中的順序如下：
- u1@'10.0.0.108'
- u1@'10.0.0.0/24'
- u1@'10.0.0.0/255.255.255.0'
- u1@'%'，u1@'10.%'
雖然10.0.0.0/24和10.0.0.0/255.255.255.0表示的是同一個網路範圍，但由於10.0.0.0/24的型別為 ip_mask_type_cidr，而10.0.0.0/255.255.255.0的型別為 ip_mask_type_subnet，因此u1@'10.0.0.0/24'會排在u1@'10.0.0.0/255.255.255.0'前面。
u1@'%' 和 u1@'10.%' 會排在最後，至於它們之間的先後順序，則由它們的排序值（sort）決定。
ACL_USER 物件的排序值是透過get_sort函式獲取的。
user.sort = get_sort(2, user.host.get_host(), user.user);
該函式會根據傳入的字串（IP和使用者名稱）的內容（是否包含萬用字元，以及萬用字元出現的位置）來計算排序權重。簡單來說，萬用字元在字串中出現得越晚，排序值越高。
所以，案例 1 中的 5 個物件在列表中的順序如下：
- u1@'10.0.0.108'
- u1@'10.0.0.0/24'
- u1@'10.0.0.0/255.255.255.0'
- u1@'10.%'
- u1@'%'
無論是新增還是刪除賬號時，都會呼叫rebuild_cached_acl_users_for_name來重建 name_to_userlist。
這就是為什麼，在案例 1 中，當新增一個主機名更具體的賬號後，再使用之前的密碼登入就會失敗，只能使用新設定的密碼。這個測試其實很典型地反映了 MySQL 許可權認證中的具體優先原則。
分析案例 2
在執行select * from my_db.t1時，MySQL 首先會檢查該使用者是否擁有全域性級別的 SELECT 許可權。如果沒有，則會進一步檢查該使用者庫級別的許可權。
獲取使用者庫級別的許可權是在acl_get函式中實現的。
// mysql-8.4.2/sql/auth/sql_auth_cache.cc
Access_bitmask acl_get(THD *thd, const char *host, const char *ip,
const char *user, const char *db, bool db_is_pattern) {
Access_bitmask host_access = ~(Access_bitmask)0, db_access = 0;
...
if (!db_is_pattern) {
// 首先在 db_cache 中查詢使用者庫級別的許可權。如果找到，則直接返回該許可權。
const auto it = db_cache.find(std::string(key, key_length));
if (it != db_cache.end()) {
db_access = it->second->access;
DBUG_PRINT("exit", ("access: 0x%" PRIx32, db_access));
return db_access;
}
}
// 如果未在快取中找到許可權，則遍歷 acl_dbs。
for (ACL_DB *acl_db = acl_dbs->begin(); acl_db != acl_dbs->end(); ++acl_db) {
// 檢查當前條目是否與客戶端的使用者、IP匹配。
if (!acl_db->user || !strcmp(user, acl_db->user)) {
if (acl_db->host.compare_hostname(host, ip)) {
// 檢查庫名是否匹配。
if (!acl_db->db ||
(db &&
(mysqld_partial_revokes()
? (!strcmp(db, acl_db->db))
: (!wild_compare(db, strlen(db), acl_db->db,
strlen(acl_db->db), db_is_pattern))))) {
db_access = acl_db->access;
if (acl_db->host.get_host()) goto exit; // Fully specified. Take it
break; /* purecov: tested */
}
}
}
}
if (!db_access) goto exit; // Can't be better
exit:
...
// 將新許可權條目插入 db_cache 以便後續能夠快速查詢。
insert_entry_in_db_cache(thd, entry);
}
DBUG_PRINT("exit", ("access: 0x%" PRIx32, db_access & host_access));
return db_access & host_access;
}
函式的具體實現如下：
-
首先在 db_cache 中查詢使用者庫級別的許可權。如果找到，則直接返回該許可權。
db_cache 是一個字典，用於快取使用者庫級別的許可權。其鍵由客戶端 IP、使用者名稱和要訪問的資料庫名（以
\0分隔）組成，例如案例 2 中的鍵是127.0.0.1\0u2\0my_db，值是對應的庫級別許可權資訊。透過這個快取，MySQL 能夠快速查詢使用者對特定資料庫的訪問許可權，而無需每次都遍歷 acl_dbs。acl_dbs 是一個陣列，用於儲存使用者庫級別的許可權，這些許可權的資訊來自於
mysql.db表。 -
如果未在快取中找到許可權，則遍歷 acl_dbs。
檢查當前條目是否與客戶端的使用者、IP 匹配。如果匹配，則進一步判斷庫名是否匹配。
如果引數
partial_revokes設定為 ON，則會直接比較庫名是否相等；如果為 OFF，則支援使用萬用字元來判斷庫名是否匹配。 -
將新許可權條目插入 db_cache 以便後續能夠快速查詢。
在案例 2 中，第一次 SELECT 查詢成功，使用者的庫級別許可權會快取到 db_cache 中。理論上，第二次查詢應該也沒問題，但卻報錯了。
為什麼會報錯呢？
實際上，在執行grant insert on `my\_db`.* to u2@'%'時，db_cache 會被清空，並且新增的許可權也會插入到 acl_dbs 中。
插入操作是在acl_insert_db中實現的。
// mysql-8.4.2/sql/auth/sql_auth_cache.cc
void acl_insert_db(const char *user, const char *host, const char *db,
Access_bitmask privileges) {
ACL_DB acl_db;
assert(assert_acl_cache_write_lock(current_thd));
acl_db.set_user(&global_acl_memory, user);
acl_db.set_host(&global_acl_memory, host);
acl_db.db = strdup_root(&global_acl_memory, db);
acl_db.access = privileges;
acl_db.sort = get_sort(3, acl_db.host.get_host(), acl_db.db, acl_db.user);
auto upper_bound =
std::upper_bound(acl_dbs->begin(), acl_dbs->end(), acl_db, ACL_compare());
acl_dbs->insert(upper_bound, acl_db);
}
可以看到，在插入之前，會先透過 get_sort 獲取 ACL_DB 物件的排序值。然後，使用std::upper_bound在 acl_dbs 中找到 ACL_DB 的插入位置。std::upper_bound會根據ACL_compare()的規則進行排序比較，以確保新元素插入後整個陣列依然有序。
ACL_compare::operator()的實現邏輯與ACL_USER_compare::operator()類似，當兩個物件 IP 都一樣的情況下，實際上比較的就是排序值（sort）。
// mysql-8.4.2/sql/auth/sql_auth_cache.cc
bool ACL_compare::operator()(const ACL_ACCESS &a, const ACL_ACCESS &b) {
if (a.host.ip != 0) {
if (b.host.ip != 0) {
/* Both elements have specified IPs. The one with the greater mask goes
* first. */
if (a.host.ip_mask_type != b.host.ip_mask_type)
return a.host.ip_mask_type < b.host.ip_mask_type;
/* if masks are not equal compare these */
if (a.host.ip_mask != b.host.ip_mask)
return a.host.ip_mask > b.host.ip_mask;
/* otherwise stick with the sort value */
return a.sort > b.sort;
}
/* The element with the IP goes first. */
return true;
}
/* The element with the IP goes first. */
if (b.host.ip != 0) return false;
/* None of the elements has IP defined. Use default comparison. */
return a.sort > b.sort;
}
grant select on my_db.* to u2@'%'和 grant insert on `my\_db`.* to u2@'%'這兩個操作對應的 ACL_DB 物件在 IP 和使用者名稱上是相同的，但庫名不同。由於第二個操作中的my\_db沒有使用萬用字元，因此其排序值更高，這就導致在 acl_dbs 中，第二個 GRANT 操作的 ACL_DB 物件的位置會比第一個操作靠前。
這就是為什麼在執行完第二個 GRANT 後，再次執行之前的 SELECT 操作會報錯。
透過 DML 操作修改了許可權表，為什麼要執行 FLUSH PRIVILEGES？
為了提高許可權的驗證效率，MySQL 會將許可權表的資料快取在記憶體中，具體包括：
- mysql.user 的資料儲存在 acl_users 中。
- mysql.db 的資料儲存在 acl_dbs 中。
- mysql.tables_priv、mysql.columns_priv 的資料儲存在 column_priv_hash 中。
- mysql.procs_priv 的資料儲存在 proc_priv_hash、func_priv_hash 中。
- mysql.proxies_priv 的資料儲存在 acl_proxy_users 中。
在驗證許可權時，MySQL 會基於記憶體中的資料進行驗證，不會直接訪問許可權表。
如果透過 DML 操作修改了許可權表，記憶體中的許可權資料不會自動更新。此時，需要執行FLUSH PRIVILEGES，該命令會清空記憶體中的許可權資料並重新載入許可權表中的內容。
相反，當透過 GRANT 或 REVOKE 命令調整許可權時，就無需執行FLUSH PRIVILEGES，因為這些操作會同步更新許可權表和記憶體中的許可權資料。
許可權表中記錄的順序會影響許可權認證的結果嗎？
基本不影響。
在將許可權表中的資料載入到記憶體對應的資料結構時，一般都會呼叫ACL_USER_compare()或ACL_compare()對資料結構進行重新排序。
以下是載入mysql.user表時的實現細節。
// mysql-8.4.2/sql/auth/acl_table_user.cc
bool Acl_table_user_reader::driver() {
...
// 將 mysql.user 的內容載入到 acl_users 中
while (!(read_rec_errcode = m_iterator->Read())) {
if (read_row(is_old_db_layout, super_users_with_empty_plugin)) return true;
}
m_iterator.reset();
if (read_rec_errcode > 0) return true;
// 基於 ACL_USER_compare() 中的規則對 acl_users 進行重新排序。
std::sort(acl_users->begin(), acl_users->end(), ACL_USER_compare());
acl_users->shrink_to_fit();
// 重建 name_to_userlist。
rebuild_cached_acl_users_for_name();
...
return false;
}
需要注意的是，在 MySQL 8.0.34 之前的小版本中，如果在案例 2 中建立的賬號的主機名不是%，而是一個具體的 IP（例如10.0.0.0/255.255.255.0、10.0.0.0/24、10.0.0.108），那麼第二次執行 SELECT 操作時將不會報錯。
為什麼又不會報錯呢？
我們之前提到的排序規則（ACL_compare()）是從 MySQL 8.0.34 版本開始引入的。在此之前，當兩個物件的 IP 相同時，排序規則並不會進一步比較它們的排序值。以下是具體的實現細節：
// mysql-8.0.33/sql/auth/sql_auth_cache.cc
bool ACL_compare::operator()(const ACL_ACCESS &a, const ACL_ACCESS &b) {
if (a.host.ip != 0) {
if (b.host.ip != 0) {
/* Both elements have specified IPs. The one with the greater mask goes
* first. */
if (a.host.ip_mask_type != b.host.ip_mask_type)
return a.host.ip_mask_type < b.host.ip_mask_type;
return a.host.ip_mask > b.host.ip_mask;
}
/* The element with the IP goes first. */
return true;
}
/* The element with the IP goes first. */
if (b.host.ip != 0) return false;
/* None of the elements has IP defined. Use default comparison. */
return a.sort > b.sort;
}
因此，案例 2 中第二個 GRANT 操作對應的 ACL_DB 物件在 acl_dbs 中的位置仍位於第一個 GRANT 操作之後，這也就導致了第二次執行 SELECT 操作時不會報錯。
在這種規則下，許可權表中記錄的順序還會影響許可權驗證的結果。簡單來說，案例 2 中的兩個 GRANT 操作，誰先執行，誰將決定該賬號對於my_db庫的許可權。
上述問題在 MySQL 5.7 中不會出現，因為 MySQL 5.7 中的排序規則會比較物件的排序值。
// mysql-5.7.44/sql/auth/sql_auth_cache.cc
class ACL_compare :
public std::binary_function<ACL_ACCESS, ACL_ACCESS, bool>
{
public:
bool operator()(const ACL_ACCESS &a, const ACL_ACCESS &b)
{
return a.sort > b.sort;
}
};
透過 GRANT/REVOKE 修改許可權後，是否需要 KILL 已有連線？
首先，我們以案例 2 中的select * from my_db.t1語句為例，看看 MySQL 中的許可權檢查流程。
// mysql-8.4.2/sql/sql_select.cc
bool Sql_cmd_select::precheck(THD *thd) {
...
bool res;
if (tables)
res = check_table_access(thd, SELECT_ACL, tables, false, UINT_MAX, false);
else
res = check_access(thd, SELECT_ACL, any_db, nullptr, nullptr, false, false);
return res || check_locking_clause_access(thd, Global_tables_list(tables));
}
如果 tables 不為空（表示有具體的表要查詢），則呼叫check_table_access來檢查使用者是否對 tables 中的所有表都擁有 SELECT 許可權。
下面是具體的許可權檢查流程：
-
首先檢查該使用者是否有全域性級別的 SELECT 許可權，此時的許可權資訊來自於
m_master_access。 -
如果使用者沒有全域性級別的 SELECT 許可權，MySQL 會繼續檢查使用者是否有對
my_db庫的 SELECT 許可權，此時的許可權資訊來自於acl_dbs。 -
若庫級別的 SELECT 許可權也不存在，MySQL 會繼續檢查使用者是否有對
my_db.t1表的 SELECT 許可權，此時的許可權資訊來自 column_priv_hash。
acl_dbs 我們之前介紹過，用來快取mysql.db表的許可權資料。當透過 GRANT 或 REVOKE 命令調整許可權時，會同步更新mysql.db表和 acl_dbs 中的資料。column_priv_hash 也同樣如此。所以如果修改的是庫級別或表級別的許可權，不需要KILL現有連線，新許可權會自動生效。
但m_master_access不一樣，它是在連線建立時設定的，即使該使用者的全域性許可權後續發生了變化，m_master_access也不會自動更新。這也就意味著，如果修改的是全域性許可權，要想新許可權對使用者馬上生效，需KILL該使用者的已有連線。
sctx->set_master_access(acl_user->access, *(mpvio.restrictions));