Re: [HACKERS] GRANT/REVOKE: Allow column-level privileges
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; I don't (do not want to) read that conclusion from above paragraphs, anyone else ? My reasoning is, that you can only revoke what has previously been granted. e.g. grant dba to grantee; cannot be revoked with: revoke select on tab from grantee; for that table I think the paragraphs have only been added to understand what rights you have on each column. Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] GRANT/REVOKE: Allow column-level privileges
2. deal with circles in GRANT graph. Can you give an examle for how this is any different for column-level GRANTs? When judging if there are any circles in the grant graph, we can represent table priviledges as column priviledges, thus make things easier. I have not think hard enought to figure out a better algorithm. Another problem is, should we allow any circles to be formed when executing GRANTs? Say: grantor1, grantee1, object1, priviledge1, with grant option, grantee1, grantee2, object1, priviledge1, with grant option, grantee2, grantor1, object1, priviledge1, with grant option, should the third GRANT be executed successfuly? I remember that MSSQL 2000 and ORACLE 9i are different. William ZHANG ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GRANT/REVOKE: Allow column-level privileges
kevin brintnall [EMAIL PROTECTED] ... (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 ^^^ That is exactly what I wanted to point out. We should record in some place that the grantee will have SELECT priviledge on any newly created columns. e.g. GRANT SELECT ON tab TO grantee; can be represented as tab, grantee, table priviledge SELECT after REVOKE SELECT (c1) ON tab FROM grantee; tab, grantee, pseduo table priviledge SELECT tab/c2, grantee, column priviledge SELECT tab/c3, grantee, column priviledge SELECT when ALTER TABLE tab ADD COLUMN c4 CHAR(8); we can use tab, grantee, pseduo table priviledge SELECT to deduce: tab/c4, grantee, column priviledge SELECT 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 ... ? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] GRANT/REVOKE: Allow column-level privileges
William ZHANG [EMAIL PROTECTED] writes: Another problem is, should we allow any circles to be formed when executing GRANTs? This is already prohibited. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] GRANT/REVOKE: Allow column-level privileges
On Thu, Jan 26, 2006 at 10:25:40PM +0800, William ZHANG wrote: I think we should pay attention to the sematic of table privs and column privs. Here is some examples. 1. role1 GRANT table priviledge SELECT on table S to role2. role1 REVOKE column priviledge SELECT on column S(SNO) from role2. As I understand the SQL spec, the first (table-level) GRANT you specified would be equivalent to repeating an appropriate column-level GRANT for every column of S. My thought was to check the column privs and apply this logic: 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 2. deal with circles in GRANT graph. Can you give an examle for how this is any different for column-level GRANTs? -- kevin brintnall =~ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] GRANT/REVOKE: Allow column-level privileges
--- kevin brintnall [EMAIL PROTECTED] 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? Euler Taveira de Oliveira euler[at]yahoo_com_br ___ Yahoo! doce lar. Faça do Yahoo! sua homepage. http://br.yahoo.com/homepageset.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GRANT/REVOKE: Allow column-level privileges
Euler Taveira de Oliveira [EMAIL PROTECTED] writes: --- kevin brintnall [EMAIL PROTECTED] 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. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] GRANT/REVOKE: Allow column-level privileges
On Sun, Jan 29, 2006 at 08:16:40PM -0500, Tom Lane wrote: Euler Taveira de Oliveira [EMAIL PROTECTED] writes: --- kevin brintnall [EMAIL PROTECTED] 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 =~ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] GRANT/REVOKE: Allow column-level privileges
I think we should pay attention to the sematic of table privs and column privs. Here is some examples. 1. role1 GRANT table priviledge SELECT on table S to role2. role1 REVOKE column priviledge SELECT on column S(SNO) from role2. 2. deal with circles in GRANT graph. kevin brintnall [EMAIL PROTECTED] wrote Fellow Hackers, I've been working on this item for a little while, and I'm starting to see some code come together. I wanted to solicit some feedback before I got too far along to make sure I'm on the right track. Here's a rough overview of what I've done so far: - PARSER: * modified parser to accept SQL column privs syntax * created a PrivAttr Node which holds ( priv, attr[] ) pairs. Currently, it's just a list of strings. For example, when you call... GRANT SELECT, UPDATE (col1, col2) ON table1, table2 to grantee; ... the parser creates a list of Nodes: (select, NIL), (update, (col1, col2)) SYSTEM CATALOG: * add attacl aclinfo[] column to pg_attribute table and Form_pg_attribute. * add OID column to pg_attribute. This permits dependencies to be registered correctly in pg_shdepend. * populated attacl column in existing pg_attribute bootstrap with NULLs * allocated an unused oid for each of the pg_attribute rows that are bootstrapped * created an oid index on pg_attribute * modified ExecuteGrantStmt to handle the PrivAttr structure instead of the list of strings * modified ExecuteGrantStmt to do a nested loop over all (column,relation) pairs in the GRANT and find oids for all of the attributes. PSQL COMMAND LINE: * display column privileges with \d+ table STILL LEFT TO DO: * implement ExecGrant_Attribute() to modify pg_attribute * verify query against column privileges in addition to table privileges * register dependencies * pg_dump column privileges - I'd welcome any feedback on the design changes I've made, or any other potential snags I should watch out for. Thanks. -- kevin brintnall =~ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
