发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

基于案例分析 MySQL 权限认证中的具体优先原则

周永鹏

9

主题

9

帖子

27

积分

新手上路

Rank: 1

积分
27
显示全部楼层
在 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

这个案例演示的是数据库库名中包含通配符的场景。
  1. create user u2@'%' identified by '123456';
  2. create database my_db;
  3. create table my_db.t1(id int primary key);
  4. insert into my_db.t1 values(1);
  5. grant select on my_db.* to u2@'%';

  7. # mysql -h127.0.0.1 -uu2 -p123456 -e 'select * from my_db.t1;'
  8. +----+
  9. | id |
  10. +----+
  11. |  1 |
  12. +----+
复制代码
最初的需求是为my_db数据库授予库级别的查询权限，因此通过上述方式进行了授权。
但实际上，库名中的_是个通配符，它能够匹配任意一个字符。因此，上面的 SELECT 权限不仅适用于my_db，同样也适用于my1db、my2db等名称相似的数据库。
鉴于之前的授权不够严谨，我在之后的授权中使用了转义符\对_进行了转义，目的是只针对my_db进行授权。没想到，授权完成后，再次执行之前的 SELECT 操作会报错。
  1. grant insert on `my\_db`.* to u2@'%';

  3. # mysql -h127.0.0.1 -uu2 -p123456 -e 'select * from my_db.t1;'
  4. 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 就不会再检查其它记录，即使该匹配项的密码不正确。所以，用户列表中记录的顺序很关键。
  1. // mysql-8.4.2/sql/auth/sql_authentication.cc
  2. static bool find_mpvio_user(THD *thd, MPVIO_EXT *mpvio) {
  3.   ...
  4.   if (likely(acl_users)) {
  5.     list = cached_acl_users_for_name(mpvio->auth_info.user_name);
  6.   }
  7.   if (list) {
  8.     for (auto it = list->begin(); it != list->end(); ++it) {
  9.       ACL_USER *acl_user_tmp = (*it);

  11.       if ((!acl_user_tmp->user ||
  12.            !strcmp(mpvio->auth_info.user_name, acl_user_tmp->user)) &&
  13.           acl_user_tmp->host.compare_hostname(mpvio->host, mpvio->ip)) {
  14.          ...
  15.         break;
  16.       }
  17.     }
  18.   }
  19.   ...
  20. }
复制代码
下面，我们分析下 ACL 用户列表的生成逻辑，这个是在rebuild_cached_acl_users_for_name函数中实现的。
  1. // mysql-8.4.2/sql/auth/sql_auth_cache.cc 
  2. void rebuild_cached_acl_users_for_name(void) {
  3.   ...
  4.   // 遍历 acl_users，将每个 ACL_USER 对象根据用户名分组到 name_to_userlist 中。
  5.   for (ACL_USER *acl_user = acl_users->begin(); acl_user != acl_users->end();
  6.        ++acl_user) {
  7.     std::string name = acl_user->user ? acl_user->user : "";
  8.     (*name_to_userlist)[name].push_back(acl_user);

  10.     // 匿名用户（即用户名为空的对象）会被单独添加到 anons 列表中。
  11.     if (!name.compare("")) anons.push_back(acl_user);
  12.   }

  14.   // 遍历 name_to_userlist，将 anons 中的匿名用户添加到每个非匿名用户的 ACL 列表中。
  15.   for (auto it = name_to_userlist->begin(); it != name_to_userlist->end();
  16.        ++it) {
  17.     std::string name = it->first;
  18.     if (!name.compare("")) continue;

  20.     auto *list = &it->second;
  21.     for (auto it2 = anons.begin(); it2 != anons.end(); ++it2) {
  22.       list->push_back(*it2);
  23.     }
  24.     // 对每个用户列表进行排序。
  25.     list->sort(ACL_USER_compare());
  26.   }
  27. }
复制代码
这个函数的功能比较简单，就是遍历 acl_users，将每个 ACL_USER 对象根据用户名分组到 name_to_userlist 中。
name_to_userlist 是一个哈希表，其键是用户名，值是一个列表，列表中存储所有拥有相同用户名的 ACL_USER 对象。
重点是最后一步，会对每个用户列表进行排序，这个排序直接影响了列表中 ACL_USER 对象的顺序。
排序命令中的ACL_USER_compare()是一个比较函数，用于对 ACL_USER 对象进行排序。
下面我们看看这个函数的实现细节。
  1. // mysql-8.4.2/sql/auth/sql_auth_cache.cc
  2. bool ACL_USER_compare::operator()(const ACL_USER &a, const ACL_USER &b) {
  3.   if (a.host.ip != 0) {
  4.     if (b.host.ip != 0) {
  5.       /* Both elements have specified IPs. The one with the greater mask goes
  6.        * first. */
  7.       if (a.host.ip_mask_type != b.host.ip_mask_type)
  8.         return a.host.ip_mask_type < b.host.ip_mask_type;

  10.       if (a.host.ip_mask == b.host.ip_mask) return a.user > b.user;

  12.       return a.host.ip_mask > b.host.ip_mask;
  13.     }
  14.     /* The element with the IP goes first. */
  15.     return true;
  16.   }

  18.   /* The element with the IP goes first. */
  19.   if (b.host.ip != 0) return false;

  21.   /* None of the elements has IP defined. Use default comparison. */
  22.   return a.sort > b.sort;
  23. }
复制代码
该函数的实现逻辑如下：

  • 如果两个对象都指定了 IP 地址（host.ip != 0），则首先比较掩码类型（ip_mask_type），其次是掩码值（ip_mask）。如果掩码值相等，则会比较用户名（user）。
  • 如果只有一个对象指定了 IP 地址，则该对象应该排在前面。
  • 如果两个对象都没有指定 IP 地址，则比较它们的排序值（sort）。
ip_mask_type 是一个enum_ip_mask_type枚举类型的变量，用于指定当前 ACL 用户的 IP 掩码类型。
  1. enum enum_ip_mask_type {
  2.   ip_mask_type_implicit,
  3.   ip_mask_type_cidr,
  4.   ip_mask_type_subnet
  5. };
复制代码
其中：

  • 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函数获取的。
  1. 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函数中实现的。
  1. // mysql-8.4.2/sql/auth/sql_auth_cache.cc
  2. Access_bitmask acl_get(THD *thd, const char *host, const char *ip,
  3.                        const char *user, const char *db, bool db_is_pattern) {
  4.   Access_bitmask host_access = ~(Access_bitmask)0, db_access = 0;
  5.   ...
  6.   if (!db_is_pattern) {
  7.     // 首先在 db_cache 中查找用户库级别的权限。如果找到，则直接返回该权限。
  8.     const auto it = db_cache.find(std::string(key, key_length));
  9.     if (it != db_cache.end()) {
  10.       db_access = it->second->access;
  11.       DBUG_PRINT("exit", ("access: 0x%" PRIx32, db_access));
  12.       return db_access;
  13.     }
  14.   }
  15.   // 如果未在缓存中找到权限，则遍历 acl_dbs。
  16.   for (ACL_DB *acl_db = acl_dbs->begin(); acl_db != acl_dbs->end(); ++acl_db) {
  17.     // 检查当前条目是否与客户端的用户、IP匹配。
  18.     if (!acl_db->user || !strcmp(user, acl_db->user)) {
  19.       if (acl_db->host.compare_hostname(host, ip)) {
  20.         // 检查库名是否匹配。
  21.         if (!acl_db->db ||
  22.             (db &&
  23.              (mysqld_partial_revokes()
  24.                   ? (!strcmp(db, acl_db->db))
  25.                   : (!wild_compare(db, strlen(db), acl_db->db,
  26.                                    strlen(acl_db->db), db_is_pattern))))) {
  27.           db_access = acl_db->access;
  28.           if (acl_db->host.get_host()) goto exit;  // Fully specified. Take it
  29.           break;                                   /* purecov: tested */
  30.         }
  31.       }
  32.     }
  33.   }
  34.   if (!db_access) goto exit;  // Can't be better

  36. exit:
  37.     ...
  38.     // 将新权限条目插入 db_cache 以便后续能够快速查询。
  39.     insert_entry_in_db_cache(thd, entry);
  40.   }
  41.   DBUG_PRINT("exit", ("access: 0x%" PRIx32, db_access & host_access));
  42.   return db_access & host_access;
  43. }
复制代码
函数的具体实现如下：

  • 首先在 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中实现的。
  1. // mysql-8.4.2/sql/auth/sql_auth_cache.cc
  2. void acl_insert_db(const char *user, const char *host, const char *db,
  3.                    Access_bitmask privileges) {
  4.   ACL_DB acl_db;
  5.   assert(assert_acl_cache_write_lock(current_thd));
  6.   acl_db.set_user(&global_acl_memory, user);
  7.   acl_db.set_host(&global_acl_memory, host);
  8.   acl_db.db = strdup_root(&global_acl_memory, db);
  9.   acl_db.access = privileges;
  10.   acl_db.sort = get_sort(3, acl_db.host.get_host(), acl_db.db, acl_db.user);
  11.   auto upper_bound =
  12.       std::upper_bound(acl_dbs->begin(), acl_dbs->end(), acl_db, ACL_compare());
  13.   acl_dbs->insert(upper_bound, acl_db);
  14. }
复制代码
可以看到，在插入之前，会先通过 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）。
  1. // mysql-8.4.2/sql/auth/sql_auth_cache.cc
  2. bool ACL_compare::operator()(const ACL_ACCESS &a, const ACL_ACCESS &b) {
  3.   if (a.host.ip != 0) {
  4.     if (b.host.ip != 0) {
  5.       /* Both elements have specified IPs. The one with the greater mask goes
  6.        * first. */
  7.       if (a.host.ip_mask_type != b.host.ip_mask_type)
  8.         return a.host.ip_mask_type < b.host.ip_mask_type;

  10.       /* if masks are not equal compare these */
  11.       if (a.host.ip_mask != b.host.ip_mask)
  12.         return a.host.ip_mask > b.host.ip_mask;

  14.       /* otherwise stick with the sort value */
  15.       return a.sort > b.sort;
  16.     }
  17.     /* The element with the IP goes first. */
  18.     return true;
  19.   }

  21.   /* The element with the IP goes first. */
  22.   if (b.host.ip != 0) return false;

  24.   /* None of the elements has IP defined. Use default comparison. */
  25.   return a.sort > b.sort;
  26. }
复制代码
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表时的实现细节。
  1. // mysql-8.4.2/sql/auth/acl_table_user.cc
  2. bool Acl_table_user_reader::driver() {
  3.   ...
  4.   // 将 mysql.user 的内容加载到 acl_users 中
  5.   while (!(read_rec_errcode = m_iterator->Read())) {
  6.     if (read_row(is_old_db_layout, super_users_with_empty_plugin)) return true;
  7.   }

  9.   m_iterator.reset();
  10.   if (read_rec_errcode > 0) return true;
  11.   // 基于 ACL_USER_compare() 中的规则对 acl_users 进行重新排序。 
  12.   std::sort(acl_users->begin(), acl_users->end(), ACL_USER_compare());
  13.   acl_users->shrink_to_fit();
  14.   // 重建 name_to_userlist。
  15.   rebuild_cached_acl_users_for_name();
  16.   ...
  17.   return false;
  18. }
复制代码
需要注意的是，在 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 相同时，排序规则并不会进一步比较它们的排序值。以下是具体的实现细节：
  1. // mysql-8.0.33/sql/auth/sql_auth_cache.cc
  2. bool ACL_compare::operator()(const ACL_ACCESS &a, const ACL_ACCESS &b) {
  3.   if (a.host.ip != 0) {
  4.     if (b.host.ip != 0) {
  5.       /* Both elements have specified IPs. The one with the greater mask goes
  6.        * first. */
  7.       if (a.host.ip_mask_type != b.host.ip_mask_type)
  8.         return a.host.ip_mask_type < b.host.ip_mask_type;

  10.       return a.host.ip_mask > b.host.ip_mask;
  11.     }
  12.     /* The element with the IP goes first. */
  13.     return true;
  14.   }

  16.   /* The element with the IP goes first. */
  17.   if (b.host.ip != 0) return false;

  19.   /* None of the elements has IP defined. Use default comparison. */
  20.   return a.sort > b.sort;
  21. }
复制代码
因此，案例 2 中第二个 GRANT 操作对应的 ACL_DB 对象在 acl_dbs 中的位置仍位于第一个 GRANT 操作之后，这也就导致了第二次执行 SELECT 操作时不会报错。
在这种规则下，权限表中记录的顺序还会影响权限验证的结果。简单来说，案例 2 中的两个 GRANT 操作，谁先执行，谁将决定该账号对于my_db库的权限。
上述问题在 MySQL 5.7 中不会出现，因为 MySQL 5.7 中的排序规则会比较对象的排序值。
  1. // mysql-5.7.44/sql/auth/sql_auth_cache.cc
  2. class ACL_compare :
  3.   public std::binary_function<ACL_ACCESS, ACL_ACCESS, bool>
  4. {
  5. public:
  6.   bool operator()(const ACL_ACCESS &a, const ACL_ACCESS &b)
  7.   {
  8.     return a.sort > b.sort;
  9.   }
  10. };
复制代码
通过 GRANT/REVOKE 修改权限后，是否需要 KILL 已有连接？

首先，我们以案例 2 中的select * from my_db.t1语句为例，看看 MySQL 中的权限检查流程。
  1. // mysql-8.4.2/sql/sql_select.cc
  2. bool Sql_cmd_select::precheck(THD *thd) {
  3.   ...
  4.   bool res;
  5.   if (tables)
  6.     res = check_table_access(thd, SELECT_ACL, tables, false, UINT_MAX, false);
  7.   else
  8.     res = check_access(thd, SELECT_ACL, any_db, nullptr, nullptr, false, false);

  10.   return res || check_locking_clause_access(thd, Global_tables_list(tables));
  11. }
复制代码
如果 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该用户的已有连接。
  1. sctx->set_master_access(acl_user->access, *(mpvio.restrictions));
复制代码
来源:https://www.cnblogs.com/ivictor/p/18509929
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

上一篇: 【2024有效】WordPress忘记密码找回登录密码的最简单有效的方法

下一篇: 第三十讲：误删数据后除了跑路，还能怎么办？

发表于 2024-10-28 18:42:41 来自手机

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4