Re: System catalog representation of access privileges
От | Mike Mascari |
---|---|
Тема | Re: System catalog representation of access privileges |
Дата | |
Msg-id | 3ADF67E3.8367B467@mascari.com обсуждение исходный текст |
Ответ на | Re: System catalog representation of access privileges (Peter Eisentraut <peter_e@gmx.net>) |
Список | 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
В списке pgsql-hackers по дате отправления: