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
