基于案例分析 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).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));
来源:https://www.cnblogs.com/ivictor/p/18509929
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]