DENY on a wildcard database not applied when a more-specific GRANT exists

Wildcard / broader database DENY is bypassed by a more-specific GRANT - only the single highest-sort ACL_DB row is consulted, and the parent deny_subtree is dropped on merge (sql/sql_acl.cc:3982, sql/privilege.h:776)

# A DENY on a wildcard database must override a more-specific GRANT.

# sql/sql_acl.cc:3982 find_by_username_or_anon returns only the single

# highest-sort ACL_DB row, so the literal-db GRANT row out-sorts and hides

# the `%` DENY row; sql/privilege.h:776 merge_with_parent then drops the

# parent deny_subtree. SELECT on the protected table is granted.

CREATE DATABASE secretdb;

CREATE TABLE secretdb.t (a INT);

INSERT INTO secretdb.t VALUES (1);

CREATE USER u@localhost;

GRANT SELECT ON secretdb.* TO u@localhost;

DENY SELECT ON `%`.* TO u@localhost;

connect (con1, localhost, u,,*NO-ONE*);

# DENY SELECT ON `%`.* covers secretdb, so this SELECT must be blocked.

--error ER_TABLEACCESS_DENIED_ERROR

SELECT * FROM secretdb.t;

connection default;

disconnect con1;

DROP USER u@localhost;

DROP DATABASE secretdb;

Leads to:

mysqltest: At line 32: query 'SELECT * FROM secretdb.t' succeeded - should have failed with error ER_TABLEACCESS_DENIED_ERROR (1142)...

Summary: A DENY placed on a wildcard or broader database (for example DENY SELECT ON `%`.* TO u) is silently ignored for any database on which the same user also holds a more-specific GRANT. Database privileges are read from a single ACL_DB row - the highest-sort match - so a literal-database GRANT row out-sorts and hides the wildcard DENY row. The deny_subtree signal that would otherwise force a descent is also discarded when the per-database access is merged with the user-level access. The denied privilege therefore remains fully effective, breaking the feature's core guarantee that a DENY overrides any existing or future grant.

Threat shape

The feature's headline use case is NOT affected: GRANT ALL ON . TO u; DENY SELECT ON mysql.* TO u works, because the mysql deny row is the only ACL_DB row matching database mysql (the . grant lives in the user-level access, not an ACL_DB row), so it is the row returned and the deny applies. The defect appears specifically when a broader/wildcard DENY coexists with a more-specific database GRANT on a database the wildcard matches.

Source

Database privileges come from one row. sql/sql_acl.cc:4570 acl_get takes the access of a single ACL_DB entry:

if (ACL_DB *acl_db= acl_db_find(db,user, host, ip, db_is_pattern))

{

  db_access= acl_db->access;          // single row only

  ...

}

acl_db_find -> find_by_username_or_anon (sql/sql_acl.cc:3967) returns the FIRST matching entry and breaks:

if (compare_hostname(&entry->host, host, ip) &&

    (!match_db_func || match_db_func(entry, db, db_is_pattern)))

{

  ret= entry;

  break;                              // first (highest-sort) match wins

}

match_db (sql/sql_acl.cc:3951) treats the stored name as a SQL pattern via wild_compare, so both the literal secretdb grant row and the % deny row match database secretdb. The array is sorted by ACL_ENTRY::sort descending (acl_compare, sql/sql_acl.cc:3847-3856), and a literal database name sorts higher (more specific) than %. The literal GRANT row is therefore returned and the % DENY row - carrying the deny_bits - is never reached.

Database denies are stored as ordinary ACL_DB rows. apply_deny_db (sql/sql_acl.cc:6948) writes the deny into an ACL_DB row keyed by its own database string, so DENY ... ON `%`. and GRANT ... ON secretdb. are two distinct rows:

if (!acl_update_db(username, hostname, db, acc, true /*is_deny*/) && !acc.is_empty())

  acl_insert_db(username, hostname, db, acc);

/* Update parent's deny_subtree */

access_t parent_acc(NO_ACL, NO_ACL, acc.deny_bits()|acc.deny_subtree());

if (apply_deny_user(combo, parent_acc)) return 1;

The descent backstop is then dropped. apply_deny_db propagates deny_subtree up to the user-level access so a later check can tell "a deny exists below, do not short-circuit". But access_t::merge_with_parent (sql/privilege.h:776) keeps only the child's deny_subtree and discards the parent's:

access_t &merge_with_parent(const access_t &parent)

{

  m_allow_bits= privilege_t(m_allow_bits | parent.m_allow_bits);

  m_deny_bits=  privilege_t(m_deny_bits  | parent.m_deny_bits);

  /* Keep current deny_subtree (child scope). */

  return *this;

}

check_access (sql/sql_parse.cc:6781) calls db_access.merge_with_parent(sctx->master_access). The per-database db_access obtained from the matched GRANT row carries deny_subtree=0; the user-level deny_subtree set by apply_deny_db is dropped in the merge. The final decision at sql/sql_parse.cc:6806 is a raw bitmask test:

if ( (db_access & want_access) == want_access || ... )

  DBUG_RETURN(FALSE);                 // access granted

With deny_bits=0 (deny row shadowed) and deny_subtree=0 (dropped on merge), db_access & SELECT == SELECT, so access is granted. The access_t::certainly_allowed helper (sql/privilege.h:797) that does consult deny_subtree is not used on this path.

Two independent root causes

1. acl_db_find returns only the single highest-sort ACL_DB row, so a more-specific GRANT row shadows a broader DENY row for the same accessed database. The deny's deny_bits never enter db_access.
2. merge_with_parent discards the parent (user-level) deny_subtree, removing the secondary signal that should have forced continued deny evaluation. This would still leak even if cause 1 were fixed, because the final decision at sql/sql_parse.cc:6806 consults neither the shadowed row nor the dropped subtree bit.

Possible Fix

The database-level deny evaluation must aggregate deny_bits across ALL matching ACL_DB rows for the accessed database, not just the highest-sort row - a broader DENY row must contribute its deny bits even when a more-specific GRANT row exists. In addition, merge_with_parent should carry the parent deny_subtree (m_deny_subtree |= parent.m_deny_subtree) and the grant decision in check_access should consult deny_subtree (force the table/column descent, or use certainly_allowed) instead of the raw db_access & want_access test. This is an architectural change to the deny-lookup path; the exact shape is left to the maintainers. The proposed fix in this directory addresses the merge_with_parent half as a starting point; the acl_db_find aggregation is the primary change.

Partial fix proposal by AI

# Candidate fix - PARTIAL. This addresses root cause 2 only (merge_with_parent

# dropping the parent deny_subtree). The primary fix for root cause 1 - the

# database-level deny evaluation consulting only the single highest-sort ACL_DB

# row (sql/sql_acl.cc:3982/4570) - is an architectural change to the deny-lookup

# path (aggregate deny_bits across ALL matching ACL_DB rows for the accessed

# database) and is left to the maintainers. With root cause 1 unaddressed, this

# diff alone does not close the bypass, because the grant decision at

# sql/sql_parse.cc:6806 is a raw (db_access & want_access) test that must also be

# taught to consult deny_subtree.

--- a/sql/privilege.h

+++ b/sql/privilege.h

@@ -776,8 +776,9 @@ public:

   access_t &merge_with_parent(const access_t &parent)

   {

     m_allow_bits= privilege_t(m_allow_bits | parent.m_allow_bits);

     m_deny_bits= privilege_t(m_deny_bits | parent.m_deny_bits);

-    /* Keep current deny_subtree (child scope). */

+    /* Carry the parent scope's deny_subtree so a deny recorded at a lower

+       hierarchy level is not lost when an upper level is consulted. */

+    m_deny_subtree= privilege_t(m_deny_subtree | parent.m_deny_subtree);

     return *this;

   }