DENY clause for access control a.k.a. "negative grants"

Summary:

Implement a way to ensure that a user can not get access to a particular resource.
DENY will function as a separate set of rules for access control. If any resource (global, database, table, column, procedure, function, etc.) has a deny on one particular privilege, it is impossible to gain that privilege unless the DENY is revoked.

For example:

GRANT SELECT on *.* to alice;

DENY SELECT on db.secret_table to alice;

alice will not be able to select from secret_table.

Syntax:

       DENY <privilege-list> ON <object-list> TO   <user-or-role>

REVOKE DENY <privilege-list> ON <object-list> FROM <user-or-role>

User cases

https://stackoverflow.com/questions/6288554/mysql-grant-all-privileges-to-database-except-one-table (32k views)
https://dba.stackexchange.com/questions/98949/grant-select-on-all-databases-except-one-mysql (18k views)
https://dba.stackexchange.com/questions/68957/block-user-access-to-certain-tables (13k views)

Details:

1. Users should not be able to see that a DENY is assigned to them (Debatable, many SQLServer users can see what DENIES are assigned to them). Similar to how databases that the user can not access are not visible in show databases.
2. DENY works on roles (as well as PUBLIC when it is implemented)
3. Enabling a role activates that role's denies as well.
4. Denies will be visible in SHOW GRANTS [FOR] if:
   • User has read or write access to mysql database.
   • User has read or write access to corresponding mysql privilege table. (TODO for as a separate MDEV for SHOW GRANTS)
5. Methods of querying denies: Currently only SHOW GRANTS or direct query in mysql.global_priv, both for for privileged users.
   I_S not currently being considered because of special privileges required, but if someone wishes it, it can be done separately. Importantly, currently existing I_S tables should not be extended by the new DENY flag, as users of these tables (likely GUI apps with checkboxes per privilege) are not prepared for DENY, and would deliver misleading information.
6. Introduce a form of SUPER privilege, say IGNORE_DENIES, to allow a "superuser" to ignore denies (this is compatible with SQL Server DENY behavior) (Not done, and unclear what purpose does it serve. SQLServer does not have special privilege, it has notion of "object owner", and a predefined role. It has it for whatever legacy reasons they got. In case of privileges went wrong, a "superuser" will want to ignore GRANTS and DENIES, both, and best for a short time similar to "sudo mode". A sufficiently privileged user, in current model, does REVOKE DENY, if he or someone else did a mistake, or starts server with `--skip-grant-tables --skip-networking` and fixes things in this "offline" mode, it is not different from broken GRANTs)
7. For DENY on database level, which uses pattern matching with '_' and '%' (MySQL/MariaDB extension of the standard) , the rules of "best matching" remain unchainged, i.e they are apply to both GRANT an DENY statements, and closest pattern wins.
8. Standard information schema privilege tables (TABLE_PRIVILEGES et al), as well SHOW GRANTS continue to show unfiltered GRANT entries, that is even those entries that have DENY on the same or higher hierarchy level.
9. the only way to

Compatiblity with other databases (Potential future work)

SQL Server

• Compared to SQL Server, DENY can only be revoked via REVOKE DENY. Future possibility for SQL Server compatibility (via SQL_MODE=MSSQL) GRANT can also cancel a DENY.
• An SQL Server quirk is that column level denies take precedence over table level denies. This is a deprecated functionality of SQL Server, meant for backwards compatibility.

MySQL

• MySQL treats revokes as "holes" that are filled when the privilege is granted (directly or via a a higher level grant). Deny will remain in effect in our implementation.
• To allow MySQL compatibility, a possible extension in the future is: @@partial_revokes=1 -> REVOKE also works as DENY.

Implementation details:

• Current privilege checking order first checks global, database, table, column privileges (in this order). If at any point the needed privileges are met, the privilege checking code stops execution. Introducing denies will require an additional lookup, one for each individual resource accessed. Some form of quick lookup must be made to answer:
  • Does this user have denies for databases? (if global privileges met all required access bits).
  • Does this user have denies for tables in a particular database (if database level privileges met all required access bits).
  • Does this user have denies on table columns for a particular table (if table level privileges met all required access bits).
• Should denies be stored in global_priv only, or should corresponding mysql.db, mysql.table_priv, etc. tables also have a "DENY" column?

Milestones (each milestone includes test cases showcasing functionality):

1. Grammar
2. Global denies
3. Database denies
4. Table level denies
5. Column level denies
6. Stored procedure denies
7. Information schema tables