← Back to team overview

maria-developers team mailing list archive

Re: Proposal for Negative Grants Project


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

> 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.

Chief Architect MariaDB
and security@xxxxxxxxxxx