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(at)gmx(dot)net http://funkturm.homeip.net/~peter