Re: GRANT/REVOKE: Allow column-level privileges

On Sun, Jan 29, 2006 at 08:16:40PM -0500, Tom Lane wrote:
> Euler Taveira de Oliveira <eulerto(at)yahoo(dot)com(dot)br> writes:
> > --- kevin brintnall <kbrint(at)rufus(dot)net> escreveu:
> >> if user matches an acl for the column
> >> .. and priv is granted, then permit
> >> .. else priv is not granted, reject
> >> else fall through to table privileges
>
> > Wouldn't it be more cheap to test the most-common-case table privileges
> > first?
>
> Also, the "reject" bit is wrong: if you have table-level privileges
> then that implies privileges on all columns. So it should be just
> an additional test made after failing to find the desired table-level
> privilege, and before erroring out.

I think that would put is in violation of the spec? This is what I got
from SQL99 (12.2 <grant privilege statement>, General Rules):

3) For every privilege descriptor in CPD whose action is INSERT, UPDATE,
or REFERENCES without a column name, privilege descriptors are also
created and added to CPD for each column C in O for which A holds the
corresponding privilege with grant option. For each such column, a
privilege descriptor is created that specifies the identical <grantee>,
the identical <action>, object C, and grantor A.

4) For every privilege descriptor in CPD whose action is SELECT without a
column name or method name, privilege descriptors are also created and
added to CPD for each column C in O for which A holds the corresponding
privilege with grant option. For each such column, a privilege
descriptor is created that specifies the identical <grantee>, the
identical <action>, object C, and grantor A.

As I read it, granting a table-level privilege is equivalent to repeating
the appropriate column-level privilege for all columns. In other words:

For this table:

CREATE TABLE tab (c1 int, c2 int, c3 int);

This statement:
GRANT SELECT ON tab TO grantee;

...also implies:

GRANT SELECT (c1) ON tab TO grantee;
GRANT SELECT (c2) ON tab TO grantee;
GRANT SELECT (c3) ON tab TO grantee;

This means that after the following, the grantee should have no privileges
on tab.c1 (but should retain them on tab.c2, tab.c3):

GRANT SELECT ON tab TO grantee;
REVOKE SELECT (c1) ON tab FROM grantee;

If we want to consult the relation ACL first, then we have to convert any
relation-level GRANTs to column-level GRANTs once any of the column
privileges are REVOKEd. However, this prevents us from seeing that the
grantee ever had table privileges, and we'll be in violation of the spec
when we go to add new columns:

(SQL99, 10.5 <privileges>, General Rules, 15-18)

15) SELECT with neither <privilege column list> nor <privilege method
list> specifies the SELECT privilege on all columns of T including any
^^^^^^^^^^^^^
columns subsequently added to T and implies a table privilege descriptor
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
and one or more column privilege descriptors. If T is a table of a
structured type TY, then SELECT also specifies the SELECT privilege on all
methods of the type TY, including any methods subsequently added to the
type TY, and implies one or more table/method privilege descriptors.

Aside from checking the column acl first, I'm not sure how we can conform
to the spec. Does anyone have a better way to handle this internally,
while still producing correct results?

GRANT SELECT ON tab TO grantee;
REVOKE SELECT (c1) ON tab FROM grantee;

It's possible I'm just mis-understanding SQL99 ... ?

--
kevin brintnall =~ <kbrint(at)rufus(dot)net>