在 MySQL 的日常管理过程中，大家或多或少会遇到权限认证相关的问题。

例如，本来能够正常执行的操作，可能在新增一个账号或授权后就突然失败了。

这种现象往往让人误以为是 bug，但很多时候，其实并不是。

下面，将通过两个案例来阐明 MySQL 权限认证中的具体优先原则，并在此基础上，分析以下问题：

• 通过 DML 操作修改权限表后，为什么需要执行 FLUSH PRIVILEGES？
• 权限表中记录的顺序是否会影响权限认证的结果？
• 在通过 GRANT 或 REVOKE 修改权限后，是否需要 KILL 已有连接才能使新权限生效？

案例 1

1. 首先，创建一个账号：create user u1@'%' identified by 'password1';，此时，在实例本地通过mysql -h10.0.0.108 -uu1 -p'password1'可以登录实例。
2. 接着，创建一个新账号：create user u1@'10.%' identified by 'password2';，用户名不变，改变的只是主机名。使用之前的密码登录会报错，提示 Access denied，需使用 password2 登录。
3. 继续创建一个新账号：create user u1@'10.0.0.0/255.255.255.0' identified by 'password3';，此时，使用 password1、password2 登录会报错，登录密码只能指定为 password3。
4. 继续创建一个新账号：create user u1@'10.0.0.0/24' identified by 'password4';，使用其它密码会报错，登录密码只能指定为 password4。
5. 继续创建一个新账号：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;
}

该函数的实现逻辑如下：

1. 如果两个对象都指定了 IP 地址（host.ip != 0），则首先比较掩码类型（ip_mask_type），其次是掩码值（ip_mask）。如果掩码值相等，则会比较用户名（user）。
2. 如果只有一个对象指定了 IP 地址，则该对象应该排在前面。
3. 如果两个对象都没有指定 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;
}

函数的具体实现如下：

1. 首先在 db_cache 中查找用户库级别的权限。如果找到，则直接返回该权限。db_cache 是一个字典，用于缓存用户库级别的权限。其键由客户端 IP、用户名和要访问的数据库名（以 \0 分隔）组成，例如案例 2 中的键是 127.0.0.1\0u2\0my_db，值是对应的库级别权限信息。通过这个缓存，MySQL 能够快速查找用户对特定数据库的访问权限，而无需每次都遍历 acl_dbs。acl_dbs 是一个数组，用于存储用户库级别的权限，这些权限的信息来自于mysql.db表。
2. 如果未在缓存中找到权限，则遍历 acl_dbs。检查当前条目是否与客户端的用户、IP 匹配。如果匹配，则进一步判断库名是否匹配。如果参数partial_revokes设置为 ON，则会直接比较库名是否相等；如果为 OFF，则支持使用通配符来判断库名是否匹配。
3. 将新权限条目插入 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 权限。

下面是具体的权限检查流程：

1. 首先检查该用户是否有全局级别的 SELECT 权限，此时的权限信息来自于m_master_access。
2. 如果用户没有全局级别的 SELECT 权限，MySQL 会继续检查用户是否有对my_db库的 SELECT 权限，此时的权限信息来自于acl_dbs。
3. 若库级别的 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));