Обсуждение: [HACHERS] privilege check: column level only?

Hello<br /><br />I'm one of the Google SoC's students for PostgreSQL. While reading sql92 standard, I found something
likethis:<br /><br />11.36  <grant statement><br />         General Rules<br />         3) For every identified
privilegedescriptor whose action is <br />            SELECT, INSERT, UPDATE, or REFERENCES without a column name,<br
/>           privilege descriptors are also created for each column C in O<br />            for which A holds the
correspondingprivilege with grant op- <br />            tion. For each such column, a privilege descriptor is
created<br/>            that specifies the identical <grantee>, the identical <action>,<br />           
objectC, and grantor A.<br /><br />According to this, column privilege descriptors are created automatically while
tableprivilege descriptor is created. Then, while checking privilege, can I JUST check column level privilege? <br
/><br/>Here is some examples.<br /><br />(1)<br />       CREATE TABLE t1 (c1 int, c2 int);<br />       GRANT SELECT ON
t1TO grantee;<br />       REVOKE SELECT ON t1 (c1) FROM grantee;<br />   Now grantee has privilege on t1(c2) but NOT on
t1(c1).Although grantee has privilege on t1, he still has no privilege on t1(c1). So checking column privilege is
enough.We don't need to check table privilege. <br />(2)<br />       CREATE TABLE t1 (c1 int, c2 int);<br />      
REVOKESELECT ON t1 FROM grantee;<br />       GRANT SELECT ON t1(c2) TO grantee;<br />   Here, still, grantee has
privilegeon t1(c2) but NOT on t1(c1). (Is this right?) Although grantee has no privilege on t1, he can has privilege on
t1(c1).Here, again, checking column privilege is enough. <br /><br />Table privilege is useful when you add columns to
atable. Whether grantee has privilege on the new columns depends on whether he has privilege on the table.<br /><br
/>Anyand all help and/or comment is appreciated. From sql standard, I found no information on how privilege check
shouldbe done. <br /><br />Thanks.<br />Dong<br clear="all" /><br />-- <br />Guodong Liu<br />Database Lab, School of
EECS,Peking University<br />Room 314, Building 42, Peking University, Beijing, 100871, China  

Am Dienstag, 5. Juni 2007 06:39 schrieb Golden Liu:
> According to this, column privilege descriptors are created automatically
> while table privilege descriptor is created. Then, while checking
> privilege, can I JUST check column level privilege?

While possible, for performance reasons it would probably be unwise.  Needs 
checking.

> Any and all help and/or comment is appreciated. From sql standard, I found
> no information on how privilege check should be done.

The SQL standard only explains constraints on the behavior of an 
implementation, not how to implement it.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Am Dienstag, 5. Juni 2007 06:39 schrieb Golden Liu:
>   
>> According to this, column privilege descriptors are created automatically
>> while table privilege descriptor is created. Then, while checking
>> privilege, can I JUST check column level privilege?
>>     
>
> While possible, for performance reasons it would probably be unwise.  Needs 
> checking.
>   

We can possibly infer their existence according to the table level 
privileges in certain cases.  But it's not clear to me how that will 
work when we change the table level privileges, nor how it works with 
revoked privileges. Do we have any provision for negative privileges? If 
not, do we need them?


cheers

andrew

"Golden Liu" <goldenliu@gmail.com> writes:
> According to this, column privilege descriptors are created automatically
> while table privilege descriptor is created. Then, while checking privilege,
> can I JUST check column level privilege?

Since we don't have any, no ;-)

You could imagine implementing it as the spec suggests, but storing all
those per-column privileges would be bulky and usually redundant.
I think part of the "TODO" item here is to think of a more intelligent
representation that only stores a column privilege descriptor when it's
different from the table-level privileges.
        regards, tom lane