Re: System catalog representation of access privileges

Mike Mascari writes:

> That looks quite nice. I do have 3 quick questions though. First, I
> assume that the prigrantee could also be a group id?

Yes.  It was also suggested making two different grantee columns for users
and groups, but I'm not yet convinced of that.  It's an option though.

> Second, one nice feature of Oracle is the ability to GRANT roles
> (our groups) to other roles.

Roles are not part of this deal, although I agree that they would be nice
to have eventually.  I'm not sure yet whether role grants would get a
different system table, but I'm leaning there.

> Would any part of your design prohibit such functionality in the future?

Not that I can see.

> Finally, I'm wondering if "Usage" or "System" privileges should be
> another system table. For example, one day I would like to (as in
> Oracle):
>
> GRANT SELECT ANY TABLE TO foo WITH ADMIN;

ANY TABLE probably implies "any table in this schema/database", no?  In
that case the grant record would refer to the oid of the schema/database.
Is there any use distinguishing between ANY TABLE and ANY VIEW?  That
would make it a bit trickier.

> GRANT CREATE PUBLIC SYNONYM TO foo;

I'm not familiar with that above command.

> GRANT DROP ANY TABLE TO foo;

I'm not sold on a DROP privilege, but a CREATE privilege would be another
column.  I didn't include it here because it's not in SQL.

> While I'm not arguing it won't work, it just doesn't "seem" clean to
> shoe-horn the system privileges into the same table as the object
> privileges.

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