Bug #114518 Privileges conflict on db

• Submitted: 1 Apr 2024 6:44
• Modified: 2 Apr 2024 9:23
• Reporter: George Ma (OCA)
• Status: Verified
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 8.0.36
• OS: Any
• CPU Architecture: Any

[1 Apr 2024 6:44] George Ma

Description:
For a database whose name including `_`, for example: `test_db`, MySQL allow to grant the privileges of db with `test_db` or `test\_db`. But if we grant the privileges mixed, the final privileges can be conflicted.

How to repeat:
Connection 1(root user):

mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

mysql> create user 'test001'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on `test_db`.* to 'test001'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on `test\_db`.* to 'test001'@'%';
Query OK, 0 rows affected (0.00 sec)

Connection 2(user test001):

mysql> show grants for test001;
+------------------------------------------------------+
| Grants for test001@%                                 |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `test001`@`%`                  |
| GRANT SELECT ON `test\_db`.* TO `test001`@`%`        |
| GRANT ALL PRIVILEGES ON `test_db`.* TO `test001`@`%` |
+------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> use test_db;
Database changed
mysql> create table t1(id int);
ERROR 1142 (42000): CREATE command denied to user 'test001'@'127.0.0.1' for table 't1'

[2 Apr 2024 2:03] George Ma

Maybe this is a bug introduced by 'Partial Revoke'.

I have searched the code and found that: if `partial_revokes` is OFF, MySQL would use `wild_compare` but not `strcmp` to compare the db name, which means `test_db` and `test\_db` are treated as same.

What's worse, the loop to fetch db privileges will break if it find one.

(in function acl_getroot)

    if (sctx->get_active_roles()->size() == 0) {
      for (ACL_DB *acl_db = acl_dbs->begin(); acl_db != acl_dbs->end();
           ++acl_db) {
        if (!acl_db->user || (user && user[0] && !strcmp(user, acl_db->user))) {
          if (acl_db->host.compare_hostname(host, ip)) {
            /*
              Do the usual string comparison if partial_revokes is ON,
              otherwise do the wildcard grant comparison
            */
            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), false))))) {
              sctx->cache_current_db_access(acl_db->access);
              break;
            }
          }
        }  // end if
      }    // end for
      sctx->set_master_access(acl_user->access,
                              acl_restrictions->find_restrictions(acl_user));
    }  // end if

[2 Apr 2024 9:23] MySQL Verification Team

Hello George Ma,

Thank you for the report and feedback.

regards,
Umesh