Location>code7788 >text

Specific Prioritization Principles in MySQL Privilege Authentication Based on Case Studies

Popularity：423 ℃/2024-10-28 11:00:23

In the daily administration of MySQL, you may encounter problems related to privilege authentication.

For example, an operation that was able to be performed properly may suddenly fail after adding a new account or authorization.

This phenomenon is often mistaken for a bug, but many times, it's not.

In the following, two case studies will be used to illustrate specific prioritization principles in MySQL privilege authentication:

  • Why do I need to perform FLUSH PRIVILEGES after modifying the privilege table through DML operation?
  • Does the order of the records in the permissions table affect the results of permissions authentication?
  • After modifying permissions via GRANT or REVOKE, do I need to KILL existing connections for the new permissions to take effect?

Case 1

  1. First, create an account:create user u1@'%' identified by 'password1';, at this point, the instance locally passes themysql -h10.0.0.108 -uu1 -p'password1'You can log in to the instance.
  2. Next, create a new account:create user u1@'10.%' identified by 'password2';The user name remains the same, only the host name is changed. If you log in with the previous password, you will get an error saying Access denied and you need to log in with password2.
  3. Go ahead and create a new account:create user u1@'10.0.0.0/255.255.255.0' identified by 'password3';In this case, logging in with password1 and password2 will result in an error, and the login password can only be specified as password3.
  4. Go ahead and create a new account:create user u1@'10.0.0.0/24' identified by 'password4';If you use a different password, you will get an error, and the login password can only be specified as password4.
  5. Go ahead and create a new account:create user u1@'10.0.0.108' identified by 'password5';If you use a different password, you will get an error, and you can only specify password5 as the login password.

The phenomenon is that every time a new account is created, the previous password becomes invalid and you can only log in with the new password.

This case is for MySQL 8.0 and above. If you are testing on MySQL 5.7, only the first three steps are valid.

Case 2

This case demonstrates a scenario where the database library name contains wildcards.

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 |
+----+

The initial need was formy_dbThe database grants query privileges at the library level, so authorization is done in the above manner.

But in reality, the library name in the_is a wildcard that matches any character. Therefore, the SELECT permission above does not only apply to themy_dbThe same applies tomy1db、my2dbetc. databases with similar names.

Given the lack of rigor in the previous authorization, I used the escape character in the subsequent authorization\treat (sb a certain way)_was escaped with the intention of targeting only themy_dbAuthorization. Unexpectedly, after the authorization is completed, executing the previous SELECT operation again will report an error.

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'

Analytical case 1

When MySQL receives a client connection, it first passes thecached_acl_users_for_nameGet a list of ACL (Access Control List) users associated with this username. Next, MySQL iterates through the list, checking to see if the client's username and hostname (IP) match the records in the list. If it does, it simply exits the loop without checking any other records.

In Case 1, for example, the list of users corresponding to u1 contains 5 records:u1@'%'，u1@'10.%'，u1@'10.0.0.0/255.255.255.0'，u1@'10.0.0.0/24'，u1@'10.0.0.108'The code is actually able to match all five records with the client. In fact, all 5 records match the client, but the code's processing logic is such that once a match is found, MySQL won't check any other records, even if the password for that match is incorrect. So the order of the records in the user list is critical.

// 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;
      }
    }
  }
  ...
}

Below, we analyze the logic of generating the ACL user list, which is done in therebuild_cached_acl_users_for_namefunction is implemented.

// mysql-8.4.2/sql/auth/sql_auth_cache.cc 
void rebuild_cached_acl_users_for_name(void) {
  ...
  // Iterate through acl_users, grouping each ACL_USER object into name_to_userlist based on username.
  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);

    // Anonymous users (i.e. objects with empty usernames) are added to the anons list separately.
    if (!("")) anons.push_back(acl_user);
  }

  // Iterate through the name_to_userlist to add the anonymous users in anons to the ACL list for each non-anonymous user.
  for (auto it = name_to_userlist->begin(); it != name_to_userlist->end();
       ++it) {
    std::string name = it->first;
    if (!("")) continue;

    auto *list = &it->second;
    for (auto it2 = (); it2 != (); ++it2) {
      list->push_back(*it2);
    }
    // Sort the per-user list.
    list->sort(ACL_USER_compare());
  }
}

This function is a simple function that iterates over acl_users and groups each ACL_USER object into name_to_userlist based on the username.

name_to_userlist is a hash table whose key is the username and whose value is a list that stores all ACL_USER objects that have the same username.

The focus is on the last step, which sorts each user list, and this sorting directly affects the order of the ACL_USER objects in the list.

in the sort commandACL_USER_compare()is a comparison function to sort ACL_USER objects.

Let's look at the implementation details of this function.

// mysql-8.4.2/sql/auth/sql_auth_cache.cc
bool ACL_USER_compare::operator()(const ACL_USER &a, const ACL_USER &b) {
  if ( != 0) {
    if ( != 0) {
      /* Both elements have specified IPs. The one with the greater mask goes
       * first. */
      if (.ip_mask_type != .ip_mask_type)
        return .ip_mask_type < .ip_mask_type;

      if (.ip_mask == .ip_mask) return  > ;

      return .ip_mask > .ip_mask;
    }
    /* The element with the IP goes first. */
    return true;
  }

  /* The element with the IP goes first. */
  if ( != 0) return false;

  /* None of the elements has IP defined. Use default comparison. */
  return  > ;
}

The function is implemented with the following logic:

  1. If both objects specify an IP address ( ! = 0), the mask type (ip_mask_type) is compared first, followed by the mask value (ip_mask). If the mask values are equal, the username (user) is compared.
  2. If only one object specifies an IP address, that object should come first.
  3. If neither object specifies an IP address, their sort values (sort) are compared.

ip_mask_type is aenum_ip_mask_typeEnumeration type variable that specifies the IP mask type of the current ACL user.

enum enum_ip_mask_type {
  ip_mask_type_implicit,
  ip_mask_type_cidr,
  ip_mask_type_subnet
};

Among them:

  • ip_mask_type_implicit: only the IP address is specified, no mask. Case 1 in the10.0.0.108It falls into this category.
  • ip_mask_type_cidr: Specifies the IP address and mask in CIDR form. In Case 1, the10.0.0.0/24It falls into this category.
  • ip_mask_type_subnet: specifies the IP address and mask in the form of a subnet mask. Case 1 in the10.0.0.0/255.255.255.0It falls into this category.

Since the default value of ip_mask_type is ip_mask_type_implicit when initializing the ACL_USER object, theu1@'%'cap (a poem)u1@'10.%'The IP mask type for these two objects is also ip_mask_type_implicit. except that these two objects do not specify an IP address, so they rank lower.

Based on the above analysis, the order of these objects in the list is as follows:

  • u1@'10.0.0.108'
  • u1@'10.0.0.0/24'
  • u1@'10.0.0.0/255.255.255.0'
  • u1@'%'，u1@'10.%'

even though10.0.0.0/24cap (a poem)10.0.0.0/255.255.255.0represents the same network range, but since the10.0.0.0/24is of type ip_mask_type_cidr, and the10.0.0.0/255.255.255.0is of type ip_mask_type_subnet, so that theu1@'10.0.0.0/24'It will be rankedu1@'10.0.0.0/255.255.255.0'Front.

u1@'%' and u1@'10.%' will come last, and the order between them is determined by their sort values.

The sorted value of the ACL_USER object is passed through theget_sortfunction gets it.

 = get_sort(2, .get_host(), );

This function calculates the sort weight based on the contents of the incoming strings (IP and username) (whether they contain wildcards, and where the wildcards appear). Simply put, the later the wildcard appears in the string, the higher the sort value.

So, the order of the 5 objects in Case 1 in the list is as follows:

  • u1@'10.0.0.108'
  • u1@'10.0.0.0/24'
  • u1@'10.0.0.0/255.255.255.0'
  • u1@'10.%'
  • u1@'%'

When either adding or deleting an account, it will call therebuild_cached_acl_users_for_nameto rebuild the name_to_userlist.

That's why, in Case 1, when a new account with a more specific hostname is added, logging in with the previous password fails and you have to use the newly set password. This test actually typifies the principle of specific precedence in MySQL privilege authentication.

Analysis of cases 2

in implementingselect * from my_db.t1MySQL first checks to see if the user has global-level SELECT privileges. If not, the user's library-level privileges are checked further.

Getting user library level permissions is done in theacl_getfunction is implemented.

// 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) {
    // First look in the db_cache for the user's library-level permissions. If found, the permission is returned directly.
    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;
    }
  }
  // Iterate over acl_dbs if permissions are not found in the cache.
  for (ACL_DB *acl_db = acl_dbs->begin(); acl_db != acl_dbs->end(); ++acl_db) {
    // Check if the current entry matches the client's user, IP.
    if (!acl_db->user || !strcmp(user, acl_db->user)) {
      if (acl_db->host.compare_hostname(host, ip)) {
        // Check for library name matches.
        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:
    ...
    // Insert the new permission entry into the db_cache so that it can be quickly queried later.
    insert_entry_in_db_cache(thd, entry);
  }
  DBUG_PRINT("exit", ("access: 0x%" PRIx32, db_access & host_access));
  return db_access & host_access;
}

The function is implemented as follows:

  1. First look for the user's library level permissions in db_cache. If found, the permission is returned directly.

    db_cache is a dictionary for caching user library level permissions. Its keys consist of the client IP, username, and the name of the database to be accessed (starting with the\0 (separated by) is composed, for example, the key in Case 2 is127.0.0.1\0u2\0my_dbThe value of acl_dbs is the corresponding library-level permission information. With this cache, MySQL can quickly find out a user's access rights to a particular database without having to traverse acl_dbs each time.

    acl_dbs is an array that stores user library level permissions, information about these permissions from theTable.

  2. If the permission is not found in the cache, traverse acl_dbs.

    Check if the current entry matches the user and IP of the client. If it matches, further determine if the library name matches.

    If the parameterpartial_revokesSet to ON, the library names will be compared directly to see if they are equal; if OFF, it supports the use of wildcards to determine if the library names match.

  3. Insert the new permission entry into the db_cache so that it can be quickly queried later.

In Case 2, the first SELECT query succeeds and the user's library level permissions are cached in the db_cache. Theoretically, the second query should also be fine, but it reports an error.

Why is it reporting an error?

In fact, the implementation ofgrant insert on `my\_db`.* to u2@'%'The db_cache is emptied and the new permissions are inserted into acl_dbs.

The insert operation is performed on theacl_insert_dbRealized in.

// 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);
}

As you can see, the sorted value of the ACL_DB object is obtained via get_sort before insertion. Then, the sort value is retrieved using thestd::upper_boundFind the insertion location of ACL_DB in acl_dbs.std::upper_boundwill be based onACL_compare()to ensure that the entire array remains ordered after the new element is inserted.

ACL_compare::operator()The implementation logic of theACL_USER_compare::operator()Similarly, when two objects have the same IP, what is actually being compared is the sort value.

// mysql-8.4.2/sql/auth/sql_auth_cache.cc
bool ACL_compare::operator()(const ACL_ACCESS &a, const ACL_ACCESS &b) {
  if ( != 0) {
    if ( != 0) {
      /* Both elements have specified IPs. The one with the greater mask goes
       * first. */
      if (.ip_mask_type != .ip_mask_type)
        return .ip_mask_type < .ip_mask_type;

      /* if masks are not equal compare these */
      if (.ip_mask != .ip_mask)
        return .ip_mask > .ip_mask;

      /* otherwise stick with the sort value */
      return  > ;
    }
    /* The element with the IP goes first. */
    return true;
  }

  /* The element with the IP goes first. */
  if ( != 0) return false;

  /* None of the elements has IP defined. Use default comparison. */
  return  > ;
}

grant select on my_db.* to u2@'%'cap (a poem)grant insert on `my\_db`.* to u2@'%'The ACL_DB objects corresponding to these two operations are identical in IP and username, but have different library names. Since the second operation in themy\_dbNo wildcards are used, so their sort value is higher, which results in the ACL_DB object of the second GRANT operation being positioned higher than the first in acl_dbs.

This is why, after the second GRANT, the previous SELECT operation is reported as an error.

Why do I need to perform FLUSH PRIVILEGES when I have modified the privilege table through a DML operation?

To improve the efficiency of validating permissions, MySQL caches the data from the permissions table in memory, specifically:

  • is stored in acl_users.
  • is stored in acl_dbs.
  • The data for mysql.tables_priv, mysql.columns_priv is stored in column_priv_hash.
  • Data for mysql.procs_priv is stored in proc_priv_hash, func_priv_hash.
  • The data for mysql.proxies_priv is stored in acl_proxy_users.

When validating permissions, MySQL validates based on in-memory data and does not access the permission table directly.

If the permission table is modified by a DML operation, the permission data in memory is not automatically updated. In this case, you need to execute theFLUSH PRIVILEGESThis command clears the permissions data from memory and reloads the contents of the permissions table.

Instead, when adjusting permissions via the GRANT or REVOKE commands, there is no need to execute theFLUSH PRIVILEGES, because these operations synchronize the update of the permissions table and the permissions data in memory.

Does the order of the records in the permissions table affect the results of permissions authentication?

Essentially unaffected.

When loading data from the permissions table into the corresponding data structure in memory, it is common to call theACL_USER_compare()maybeACL_compare()Reorder the data structure.

The following is loadedTable-time implementation details.

// mysql-8.4.2/sql/auth/acl_table_user.cc
bool Acl_table_user_reader::driver() {
  ...
  // load the contents of acl_users into 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;
  // Reorder acl_users based on the rules in ACL_USER_compare().
  std::sort(acl_users->begin(), acl_users->end(), ACL_USER_compare());
  acl_users->shrink_to_fit();
  // Rebuild the name_to_userlist.
  rebuild_cached_acl_users_for_name();
  ...
  return false;
}

Note that in minor versions of MySQL prior to 8.0.34, if the hostname of the account created in Case 2 is not%but rather a specific IP (e.g.10.0.0.0/255.255.255.0、10.0.0.0/24、10.0.0.108), then the second SELECT operation will not report an error.

Why wouldn't it report an error again?

The sorting rules we mentioned earlier (ACL_compare()) has been introduced since MySQL version 8.0.34. Prior to that, when two objects had the same IP, the sorting rules did not further compare their sorted values. Here are the implementation details:

// mysql-8.0.33/sql/auth/sql_auth_cache.cc
bool ACL_compare::operator()(const ACL_ACCESS &a, const ACL_ACCESS &b) {
  if ( != 0) {
    if ( != 0) {
      /* Both elements have specified IPs. The one with the greater mask goes
       * first. */
      if (.ip_mask_type != .ip_mask_type)
        return .ip_mask_type < .ip_mask_type;

      return .ip_mask > .ip_mask;
    }
    /* The element with the IP goes first. */
    return true;
  }

  /* The element with the IP goes first. */
  if ( != 0) return false;

  /* None of the elements has IP defined. Use default comparison. */
  return  > ;
}

As a result, the ACL_DB object corresponding to the second GRANT operation in Case 2 is still located in acl_dbs after the first GRANT operation, which means that the second SELECT operation will not report an error.

Under this rule, the order of the records in the permissions table also affects the results of permissions validation. Simply put, whoever executes the two GRANT operations in Case 2 first will determine the order in which the account is validated for themy_dblibrary permissions.

The above problem does not occur in MySQL 5.7 because the sorting rules in MySQL 5.7 compare the sorted values of objects.

// 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  > ;
  }
};

Do I need to KILL existing connections after modifying permissions via GRANT/REVOKE?

First, let's take the case in Case 2 as an example.select * from my_db.t1statement as an example of the permission checking process in 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));
}

If tables is not empty (indicating that there is a specific table to query), then call thecheck_table_accessto check if the user has SELECT privileges on all tables in tables.

Here is the specific permission checking process:

  1. First check that the user has global-level SELECT privileges, at which point the privilege information comes from them_master_access。

  2. If the user does not have global-level SELECT privileges, MySQL proceeds to check whether the user has any rights to themy_dbSELECT permission for the library, at which point the permission information comes from acl_dbs.

  3. If the library-level SELECT privilege does not exist either, MySQL continues to check whether the user has any rights to themy_db.t1 SELECT privileges for the table, where the privilege information comes from column_priv_hash.

acl_dbs We've covered this before, it's used to cache thetable's permission data. When permissions are adjusted with the GRANT or REVOKE commands, they are synchronized to update thetable and the data in acl_dbs. column_priv_hash does the same. So if you are modifying library-level or table-level permissions, you don't need theKILLexisting connections, the new privileges will automatically take effect.

butm_master_accessis different; it is set when the connection is established, even if that user's global permissions subsequently change.m_master_accessIt will not be updated automatically. This also means that if the modification is a global permission, for the new permission to take effect for the user right away, theKILLExisting connections for this user.

sctx->set_master_access(acl_user->access, *());
<Previous Next>