maria-developers team mailing list archive

[Rutuja Surve <rutuja@xxxxxxxxxxx>]

Hi,
Thanks a lot for reviewing the approach. I'll surely update MDEV-14443 with
these ideas.
We could follow either of the two approaches mentioned by Vicentiu above
(argument for duplication of tables or modification of the primary key
definition for including an extra is_deny column to avoid duplication). It
would be great to hear more thoughts and have more discussions on this.

Thanks,
Rutuja

On Sat, May 12, 2018 at 3:32 PM, Vicențiu Ciorbaru <cvicentiu@xxxxxxxxx>
wrote:

> Hi Sergei!
>
>
> On Fri, 11 May 2018 at 21:10 Sergei Golubchik <serg@xxxxxxxxxxx> 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
>