TODO: GRANT/REVOKE: Allow column-level privileges

kevin brintnall <kbrint@rufus.net> writes:

* add OID column to pg_attribute. This permits dependencies to be
registered correctly in pg_shdepend.

No, no ... the precedent in pg_depend is that columns are represented as
the table's OID plus a column number. Please don't invent some random
other notation for a column, especially not one that is so expensive to
relate to the parent table. Add a subobject ID to pg_shdepend instead.

STILL LEFT TO DO:

My recollection is that there's quite some deal of code that assumes
pg_attribute rows are fixed-width. You will have some issues there.
It's possible though that none of that code needs to access privileges,
in which case you'd be OK just dropping off the ACL data from the
in-memory copies of pg_attribute rows. Another possible solution is the
pg_attrdef model, ie, keep the ACLs somewhere else.

regards, tom lane

On Fri, Jan 20, 2006 at 07:09:46PM -0500, Tom Lane wrote:

kevin brintnall <kbrint@rufus.net> writes:

* add OID column to pg_attribute. This permits dependencies to be
registered correctly in pg_shdepend.

No, no ... the precedent in pg_depend is that columns are represented as
the table's OID plus a column number. Please don't invent some random
other notation for a column, especially not one that is so expensive to
relate to the parent table. Add a subobject ID to pg_shdepend instead.

I was referring to the dependency that exists between a grantee and any
pg_attribute ACL entries that mention the grantee. When the role is
dropped, the ACL entries that mention that role have to be removed.

Specifically, I propose creating an entry such as the following in
pg_shdepend for every grantee G, for every column C in which G is
mentioned:

classid = AttributeRelationId /* 1249 */
objid = C.oid
refclassid = AuthIdRelationId /* 1260 */
refobjid = G.oid
deptype = 'a' /* SHARED_DEPENDENCY_ACL */

Are you suggesting that the pair (reloid,attnum) is superior for
identifying a pg_attribute entry? Are there any other possible uses for
pg_attribute.oid?

STILL LEFT TO DO:

My recollection is that there's quite some deal of code that assumes
pg_attribute rows are fixed-width. You will have some issues there.
It's possible though that none of that code needs to access privileges,
in which case you'd be OK just dropping off the ACL data from the
in-memory copies of pg_attribute rows. Another possible solution is the
pg_attrdef model, ie, keep the ACLs somewhere else.

I'm employing the same hack^H^H^H^Hmethod that is currently used in
pg_class.

--
kevin brintnall =~ <kbrint@rufus.net>

kevin brintnall <kbrint@rufus.net> writes:

Are you suggesting that the pair (reloid,attnum) is superior for
identifying a pg_attribute entry?

Yes. It's just as unique, and it makes it easy to see the relationship
between the table and its columns. Moreover, it's what we're already
using in pg_depend.

regards, tom lane

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" <kbrint@rufus.net> 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.

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 =~ <kbrint@rufus.net>

--- 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?

Euler Taveira de Oliveira
euler[at]yahoo_com_br

_______________________________________________________
Yahoo! doce lar. Fa�a do Yahoo! sua homepage.
http://br.yahoo.com/homepageset.html

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.

regards, tom lane

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, 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@rufus.net>

"kevin brintnall" <kbrint@rufus.net>
...

(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>

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

"William ZHANG" <uniware@zedware.org> writes:

Another problem is, should we allow any circles to be formed when executing
GRANTs?

This is already prohibited.

regards, tom lane

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