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. 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: INFORMATION_SCHEMA.USER_PRIVILEGES
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)

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