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'm not a great fan of duplicating all tables, I'd rather reuse
existing ones