Re: System catalog representation of access privileges - Mailing list pgsql-hackers

First, let me say that just because Oracle does it this way doesn't make
it better but...

Oracle divides privileges into 2 categories:

Object privileges
System privileges

The Object privileges are the ones you describe. And I agree
fundamentally with your design. Although I would have (a) used a bitmask
for the privileges and (b) have an additional bitmask which determines
whether or not the Grantee could turn around and grant the same
permission to someone else:

pg_objprivs {priobj oid,prigrantor oid,prigrantee oid,priprivileges int4,priadmin int4
};

Where priprivileges is a bitmask for:

0 ALTER - tables, sequences
1 DELETE - tables, views    
2 EXECUTE - procedures, functions
3 INDEX - tables
4 INSERT - tables, views
5 REFERENCES - tables
6 SELECT - tables, views, sequences
7 UPDATE - tables, views
8 HIERARCHY - tables
9 UNDER - tables

And the priadmin is a bitmask to determine whether or not the Grantee
could grant the same privilege to another user. Since these are Object
privileges, 32 bits should be enough (and also 640K RAM ;-)).

The System privileges are privileges granted to a user or role (a.k.a
group) which are not associated with any particular object. This is one
area where I think PostgreSQL needs a lot of work and thought,
particularly with schemas coming down the road. Some example Oracle
System privileges are:

Typical User Privileges:
-----------------------

CREATE SESSION - Allows the user to connect 
CREATE SEQUENCE - Allows the user to create sequences in his schema
CREATE SYNONYM - Allows the user to create private synonyms
CREATE TABLE - Allows the user to create a table in his schema
CREATE TRIGGER - Allows the user to create triggers on tables in his
schema
CREATE VIEW - Allows the user to create views in his schema

Typical Power-User Privileges:
-----------------------------

ALTER ANY INDEX - Allows user to alter an index in *any* schema
ALTER ANY PROCEDURE - Allows user to alter a procedure in *any* schema
ALTER ANY TABLE - Allows user to alter a table in *any* schema
...
CREATE ANY TABLE - Allows user to create a table in *any* schema
COMMENT ANY TABLE - Allows user to document any table in *any* schema
...

Typical DBA-Only Privileges:
---------------------------

ALTER USER - Allows user to change password, quotas, etc. for *any* user
CREATE USER - Allows user to create a new user
DROP USER - Allows user to drop a new user
GRANT ANY PRIVILEGE - Allows user to grant any privilege to any user
ANALYZE ANY - Allows user to analyze any table in *any* schema

There are, in fact, many, many more System Privileges that Oracle
defines. You may want someone to connect to a database and query one
table and that's it. Or you may want someone to have no other abilities
except to document the database design via the great COMMENT ON command
;-), etc. 

So for System Privileges, I would have something like:

pg_sysprivs {prigrantee oid,priprivilege oid,prigroup bool,priadmin bool
};

So each System privilege granted to a user (or group) would be its own
record. The priprivilege would be the OID of one of the many System
privileges defined in the same way types are defined, if prigroup is
false. If prigroup is true, however, then priprivilege is not a System
privilege, but a group id. And then PostgreSQL will have to examine the
privileges recursively for that group. Of course, you might not want to
allow for the GRANTing of group privileges to other groups initially,
which simplifies the implementation tremendously. But its a neat (if not
complicated) Oracle-ism.

Unfortunately, this means that the permission might require > 2 lookups.
But these lookups are only if the previous lookup failed:

SELECT * FROM employees.foo;

1. Am I a member of the employees schema? Yes -> Done
2. Have I been GRANTed the Object Privilege of:  SELECT on employees.foo? Yes -> Done
3. Have I been GRANTed the System Privilege of:  SELECT ANY TABLE? Yes -> Done

So the number of lookups does potentially increase, but only for those
users that have been granted access through greater and greater layers
of authority. 

I just think that each new feature added to PostgreSQL opens up a very
large can of worms. Schemas are such a feature and the security system
should be prepared for it.

FWIW,

Mike Mascari
mascarm@mascari.com


Peter Eisentraut wrote:
> 
> 
> It would make sense to split privileges on tables from privileges on
> schemas/databases from privileges on, say, functions, etc.  E.g.,
> 
> pg_privtable    -- like proposed
> 
> pg_privschema (
>     priobj oid, prigrantor oid, prigrantee oid,
>     char pritarget,     -- 't' = any table, 'v' = any view, ...
>     char priselect,
>     char priupdate,
>     /* etc */
> )
> 
> But this would mean that a check like "can I select from this table"
> would possibly require lookups in two tables.  Not sure how much of a
> tradeoff that is, but the "shoehorn factor" would be lower.
> 
> Comments on this?
> 
> --
> Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter