基于案例分析 MySQL 权限认证中的具体优先原则
在 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));