Hi Sergei! On Fri, 11 May 2018 at 21:10 Sergei Golubchik wrote:

Hi, Rutuja!

I think it would've be useful to put your main ideas into MDEV-14443, as a comment.

For now I've just added a link to your email.

On May 09, Rutuja Surve wrote:

...

Hello, Please find the proposal for the Negative Grants project attached along with this e-mail. It would be great to hear your opinions and suggestions on the approach proposed here, before delving into its implementation. Some context about the project:

*The current privilege system allows access in this manner:GRANT SELECT ON some_database.* TO a_user@%;Any revoke would revoke access from the entire database. We want to support a revoke that would disable select on a specific table, like:REVOKE SELECT ON some_database.secret_table FROM a_user@%;Reference:[1] https://jira.mariadb.org/browse/MDEV-14443 https://mariadb.com/kb/en/library/google-summer-of-code-2018/ Thanks,Rutuja*

Random thoughts:

* It's good that SQL Server supports DENY statement, a precedent is a very strong argument for us to do it that way.

* I wasn't able to find any other database that has this functionality. (but many questions about how to achieve that result)

* DENY is not really an antonym of GRANT. If we'd have an ALLOW statement, DENY would've been a logical choice. But we don't.

* REVOKE is a logical complement to GRANT, some of these questions that I've found were answered with, like GRANT ... ON *.* REVOKE ... ON somedb.* which, of course, is wrong, but it shows what an intuitive answer is

* DENY encourages wrong thinking - I've seen questions like "how to deny access to all tables, but one". The correct answer is, of course, just grant access to one table, don't use DENY at all. If there's no DENY, there's no place for such a mistake.

* DENY semantics is quite simple in SQL Server (if we disregard strange treatment of column level grants). It's very easy to explain, which is good. I'm not totally grasping how a REVOKE could work :(

* How do you undo a DENY statement?

I proposed an extension of REVOKE: REVOKE DENY xxx, similar to how a REVOKE ROLE would function. REVOKE ALL DENIES FOR foo This way there are 2 separate commands. GRANT and REVOKE for positive grants and DENY and REVOKE DENY for negative grants. Perhaps REVOKE DENY is not the greatest idea, but that's why this was brought up for discussion. Any better solution here would help. * I'm not a great fan of duplicating all tables, I'd rather reuse

existing ones

From an implementation standpoint it's almost trivial to reuse as well as duplicate. Reusing tables means that each mysql privilege table can

potentially have two entries for the same user@host combination, one for denies, one for grants and have them distinguished through a boolean column (say is_deny, very similar to is_role) This requires changing the current primary key definition of these tables as now we have host and user as uniques. I think that 2 entries in the privilege table for the same user,host combination might cause confusion for users, hence the suggestion for duplicating tables (only the host, user && priv_xxx columns, not the other ones). SQL server has a bit of a strange approach to revoking denies, it feels like each priv column internally for them is a tri-state version of our solution. (Yes, No, Deny) and REVOKING moves it to No always, regardless of previous state. Thoughts? Vicentiu

Hi, Vicențiu! On May 12, Vicențiu Ciorbaru wrote:

...

Random thoughts:

* It's good that SQL Server supports DENY statement, a precedent is a very strong argument for us to do it that way.

* I wasn't able to find any other database that has this functionality. (but many questions about how to achieve that result)

* DENY is not really an antonym of GRANT. If we'd have an ALLOW statement, DENY would've been a logical choice. But we don't.

* REVOKE is a logical complement to GRANT, some of these questions that I've found were answered with, like GRANT ... ON *.* REVOKE ... ON somedb.* which, of course, is wrong, but it shows what an intuitive answer is

* DENY encourages wrong thinking - I've seen questions like "how to deny access to all tables, but one". The correct answer is, of course, just grant access to one table, don't use DENY at all. If there's no DENY, there's no place for such a mistake.

* DENY semantics is quite simple in SQL Server (if we disregard strange treatment of column level grants). It's very easy to explain, which is good. I'm not totally grasping how a REVOKE could work :(

* How do you undo a DENY statement?

I proposed an extension of REVOKE: REVOKE DENY xxx, similar to how a REVOKE ROLE would function.

REVOKE ALL DENIES FOR foo

This way there are 2 separate commands. GRANT and REVOKE for positive grants and DENY and REVOKE DENY for negative grants.

Perhaps REVOKE DENY is not the greatest idea, but that's why this was brought up for discussion. Any better solution here would help.

SQL server has a bit of a strange approach to revoking denies, it feels like each priv column internally for them is a tri-state version of our solution. (Yes, No, Deny) and REVOKING moves it to No always, regardless of previous state.

Thoughts?

As you might've guessed from my comments above, I'd rather prefer to use REVOKE instead of DENY, if we can make it to work (and to explain how it works afterwards :). So here I suggest a semantics how REVOKE could be used for negative grants (note, I'm not talking about storage or any other technical details here). In the standard REVOKE removes an existing GRANT and must match the existing GRANT exactly. Extending this idea, we say that REVOKE *makes a hole* in the existing GRANT and must be *a subset* of an existing GRANT (a subset could be equal to the whole GRANT too). So REVOKE is a set difference operation. Note the difference with DENY approach - DENY exists as a separate entity, it may deny access to something that isn't granted, after revoking a grant DENY stays and needs to be revoked separately. While REVOKE is not a separate entity. It simply reduces the scope of the original GRANT. You cannot "revoke a REVOKE", to remove a hole you revoke that GRANT that has it. This interpretation doesn't have some features that DENY approach has. On the other hand it's surely much more intuitive. And it's an extention of the standard semantics, not a new totally feature to learn. Regards, Sergei Chief Architect MariaDB and security@mariadb.org

Hi, Vicențiu! On May 31, Vicențiu Ciorbaru wrote:

...

As you might've guessed from my comments above, I'd rather prefer to use REVOKE instead of DENY, if we can make it to work (and to explain how it works afterwards :).

So here I suggest a semantics how REVOKE could be used for negative grants (note, I'm not talking about storage or any other technical details here).

In the standard REVOKE removes an existing GRANT and must match the existing GRANT exactly. Extending this idea, we say that REVOKE *makes a hole* in the existing GRANT and must be *a subset* of an existing GRANT (a subset could be equal to the whole GRANT too). So REVOKE is a set difference operation.

Note the difference with DENY approach - DENY exists as a separate entity, it may deny access to something that isn't granted, after revoking a grant DENY stays and needs to be revoked separately.

While REVOKE is not a separate entity. It simply reduces the scope of the original GRANT. You cannot "revoke a REVOKE", to remove a hole you revoke that GRANT that has it.

This interpretation doesn't have some features that DENY approach has. On the other hand it's surely much more intuitive. And it's an extention of the standard semantics, not a new totally feature to learn.

I've spent some time thinking about this today. I believe we can potentially do something, however do you think it is ok to modify grant tables such that the privilege columns now have the form enum(Y,N,D)? I have not fully fleshed out the design of how revoke would work in all cases but this is how I think we can implement grants to work efficiently.

The scenario I'm thinking of is:

Huge db with a million tables.

GRANT SELECT ON db.* TO foo; REVOKE SELECT ON db.test FROM foo;

If we have a revoke which carves out holes, our current basic approach is to just drop the db SELECT privilege and grant SELECT on all other tables.

No-no, this is wrong. This way you'll only grant privileges on tables that existed at the moment of REVOKE. Assuming, db contained only tables t1, t2, t3, test, access to all tables in db but not to db.test is clearly different from access to db.t1, db.t2, db.t3 If you just want to do the latter, it's not a new feature, one can do it now already. May be with a script or a storage procedure for a little automation.

I have a feeling that this imposes a big performance penalty, both that we now need to write a million rows to mysql grant table table and the fact that we need to load a whole bunch of stuff in memory now. I don't remember the exact implementation details here, but I remember that it's not exactly O(1) to check if a table is in the grant table list.

This not just a performance issue, it's a correctness issue.

On the other hand it should be rather easy for revoke to set a "deny" bit for more specific grants if there already is a more general grant (as in this example: a whole database level grant is present and we introduce a table level grant too)

Yes. I think the storage for this REVOKE semantics would not be much different from storage for a separate DENY statement SQL Server style. REVOKE based implementation will behave differently, but storage-wise they should be very similar. I think enum(Y,N,D) is good, looks like it'll work. Regards, Sergei Chief Architect MariaDB and security@mariadb.org