[HACHERS] privilege check: column level only?
Hello
I'm one of the Google SoC's students for PostgreSQL. While reading sql92
standard, I found something like this:
11.36 <grant statement>
General Rules
3) For every identified privilege descriptor whose action is
SELECT, INSERT, UPDATE, or REFERENCES without a column name,
privilege descriptors are also created for each column C in O
for which A holds the corresponding privilege with grant op-
tion. For each such column, a privilege descriptor is created
that specifies the identical <grantee>, the identical <action>,
object C, and grantor A.
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?
Here is some examples.
(1)
CREATE TABLE t1 (c1 int, c2 int);
GRANT SELECT ON t1 TO grantee;
REVOKE SELECT ON t1 (c1) FROM grantee;
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.
(2)
CREATE TABLE t1 (c1 int, c2 int);
REVOKE SELECT ON t1 FROM grantee;
GRANT SELECT ON t1(c2) TO grantee;
Here, still, grantee has privilege on 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.
Table privilege is useful when you add columns to a table. Whether grantee
has privilege on the new columns depends on whether he has privilege on the
table.
Any and all help and/or comment is appreciated. From sql standard, I found
no information on how privilege check should be done.
Thanks.
Dong
--
Guodong Liu
Database Lab, School of EECS, Peking University
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