maria-developers team mailing list archive
Mailing list archive
Re: Proposal for Negative Grants Project
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.
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
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
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.
Chief Architect MariaDB