在 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));
