maria-developers team mailing list archive

[Vicențiu Ciorbaru <cvicentiu@xxxxxxxxx>]

Hi Sergey!

On Tue, 29 May 2018 at 17:54 Sergei Golubchik <serg@xxxxxxxxxxx> wrote:

> Hi, Vicențiu!



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.
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. (I will double check
this).

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)

When one GRANTS SELECT ON db.* TO foo; again, we would need to double check
all possible affected entries (all table and column level grants that match
database db) and clear any SELECT deny bits.

I believe this would be the most intuitive approach. It would keep working
the same for already existing applications, except for those that revoke
stuff expecting it to have no effect whatsoever, and maybe we can set an
SQL MODE for that.

Give me a couple of days to fully think about this, but please let me know
if you agree with changing the privilege columns table structure from
enum(Y,N) to enum(Y,N,D). This is the best solution I could come up with
that is "minimally invasive" and also should provide sufficient info to
recreate the internal structures on server restart. I don't see any
potential problems with this, but you've been through more GA releases than
me so you know the user pains better :)

Regards,
Vicențiu