cvicentiu, rutuja, Please add a rough estimate for the remaining work

Current status:
1. User level (Global) deny works with and without an initial GRANT.
2. Database level deny works without an initial GRANT.
Attaching the detailed approach followed for the same shortly.

Current status of implementation:
User and database level denies work end to end.
Work in progress for table and column level deny (estimate 1 week)
Routine level deny (estimate 1 week)
Testing and review (estimate 2 weeks)

As discussed with Max a while back, this functionality is highly desired by the SkySQL team to revoke access to system schemas.

The problem I see with that is just that if you later grant some privileges
that contradict the earlier REVOKE, then what will the expected behavior
be? I like the idea of a DENY that overrides any existing or future grants.

JP

On Thu, May 20, 2021 at 7:13 AM Ralf Gebhardt (Jira) <jira@mariadb.org>

If there is a DENY on mysql.* for a user and they try to run:

mysql < mydump.sql

the full dump should load but avoid the mysql.* tables (or have only warnings related).

if there is a DENY on mysql.* .. GRANT and related syntax should still be allowed

manjot DENY cancels out privileges.

For example if a user could create a user with CREATE USER privilege, but otherwise didn't have INSERT/UPDATE rights on mysql.* database, then the same would be true if the user had DENY INSERT UPDATE on mysql.*.

I assume this is in line with your statements, correct?

A current status update on the feature:

In order to facilitate the feature, I have done the following cleanups:

1. Move check_xxx_access functions to sql_acl.cc.
2. Uniform privilege modification functions for mysql.user and the newer mysql.global_priv table. This is to facilitate writing denies into mysql.global_priv table.
3. Optimize Sql_cmd_grant class, to use composition instead of inheritance. This also had the added benefit of reducing memory usage for Grant commands, avoiding an extra copy in LEX structure.
4. Clean up the sql_acl.h exposed API. Many of the functions are now behind Sql_cmd_grant::execute calls.
5. Remove code duplication by converting double acl_get calls checking for user and role privileges into a single function. This will facilitate DENY checks as well.
6. Introduce a Priv_spec class that defines a delta of privileges. This is used uniformly by replace_xxxx_table functions to apply privilege deltas. This facilitates DENY and /REVOKE DENY grants.
7. Split mysql_grant function into separate components. This facilitates individual DENY level grants (Global, Database, Proxy) This has the added benefit of only locking the modified privilege tables when doing a grant update.
8. Cache get_current_user results in Sql_cmd_grant, before calling mysql_grant_xxx functions. This simplifies a lot of the mysql_grant_xxx functions.
9. Refactor test_if_create_new_users, similar to get_current_user caching. This will facilitate hooking up denies too.
10. Extend gramar in sql_yacc.yy to cover DENY clause.
11. Code to read DENY specification from mysql.global_priv table. The JSON format at this point is:

    {

    "global": int

    "db": [

    {

    "name": str

    "access": int

    },

    ]

    "table": [

    {

    "name": str

    "access": int

    },

    ]

    }

    The format will be extended to cover finer level denies (table columns) too.

12. JSON reader class, used to parse the DENY json and create in-memory HASH tables for all deny entries - per user.
13. Extend mysql_show_grants to also output denies.
14. Wrote test cases to cover currently implemented functionality, as well as tests that currently fail (because denies are not properly enforced). Using effectively test driven development to ensure all code paths are covered.

TODO items:

1. Adjust privilege check call sites to do proper deny evaluation. Ideally, create a uniform API that abstracts away both user and role current grants and denies.
2. Extend information schema tables to present user denies.
3. Create a new privilege (IGNORE_DENIES) to allow a user to bypass denies.
4. Extend mysql_show_grants to output grants for users who have denies, but do not have the rights to see those denies. This requires a dedicated algorithm to generate the equivalent set of rights that are available.
5. Extend test coverage.
6. Code documentation - both within source as well as in the KB.

cvicentiu the Xpand team is going to start implementing this feature as well. Is there a build that we could use to compare notes?

Hi Sergei!

Once you are done with reviewing MDEV-29458 & MDEV-29465, please start looking at the code here:

https://github.com/MariaDB/server/pull/2258

Things currently not completely operational:
1. SHOW GRANTS currently only shows up-to database level denies.
2. Wildcard DB denies.

I'm looking for general input on architecture changes as well as functionality. I expect that by the time you get to the end of the commit tree that the missing items will also be present.

One status update on this:

I've rebased on top of 11.2.

As discussed with Sergei Golubchik, I've implemented the separation of denies between users and roles. The implementation still requires testing and I am uncovering edge cases, bugs and an occasional crash. I expect around a week worth of work to stabilize this, then it should be ready for one final review.