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@yahoo.com.br> writes:
> > --- kevin brintnall <kbrint@rufus.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,
privilegedescriptors are also  created and added to CPD for each column C in O for which A holds the  corresponding
privilegewith 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
descriptorsare also created and  added to CPD for each column C in O for which A holds the corresponding  privilege
withgrant 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
columnsof T including any                                                            ^^^^^^^^^^^^^   columns
subsequentlyadded to T and implies a table privilege descriptor   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^   and one or more
columnprivilege descriptors. If T is a table of a   structured type TY, then SELECT also specifies the SELECT privilege
onall   methods of the type TY, including any methods subsequently added to the   type TY, and implies one or more
table/methodprivilege 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@rufus.net>