Re: GRANT/REVOKE: Allow column-level privileges
От | kevin brintnall |
---|---|
Тема | Re: GRANT/REVOKE: Allow column-level privileges |
Дата | |
Msg-id | 20060130044430.GA42463@rufus.net обсуждение исходный текст |
Ответ на | Re: GRANT/REVOKE: Allow column-level privileges (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
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>
В списке pgsql-hackers по дате отправления: