Re: Granting all tables in schema by one SQL command

Dnia 19-10-2006 o 23:14:07 Merlin Moncure <mmoncure(at)gmail(dot)com> napisał:
>> What does "NEW TABLES" mean in this context?
>
> the point is to allow tables to inherit permissions from the parent
> schema. this is actually imo much more important than multiple table
> grant [...] There was considerable debate on this topic at the time that
> todo was written up.

Thanx for reply.

Can You give me some link to this discussion?

But I still have some doubts wich I want to dispel.

If construct with "NEW TABLES" mean "allow tables to inherit permissions
from the parent
schema" so why point "Allow GRANT/REVOKE permissions to be inherited by
objects based on schema permissions"
is separated in TODO list. On my mind I think its the same and please
correct me if I'm wrong.

To sum up:

"GRANT SELECT ON ALL TABLES IN schema_name TO phpuser" mean that all
tables in schema will by grated ot once (the same
way as one GRANT sql command per relation is schema).

"GRANT SELECT ON NEW TABLES IN schema_name TO phpuser" mean that schema
"schema_name" will be marked with SELECT privilage and
every table or view that will be created in that schema after that command
will have SELECT privilage granted to phpuser.
I understant that this command doesn't have influence on tables that
exists in schema at the moment of its execution.

"REVOKE SELECT ON ALL TABLES IN schema_name FROM phpuser" will be the same
as "REVOKE SELECT ON table1, table2, table3... FROM phpuser"
on all tables in schema.

"REVOKE SELECT ON NEW TABLES IN schema_name FROM phpuser" will not exactly
revoke any privilage from tables in schema but unmark schema_name default
permission for any new table.

If so - maby better alternative then construct with "NEW TABLES" is
"ALTER SCHEMA schema_name SET NEW_TABLE_PERMISSONS TO privilages TO roles"
or
"GRANT SELECT, UPDATE... ON SCHEMA schema_name TO roles" (the same as
GRANT CREATE for schema)

Last is in my opinion the best becouse it uses existing SQL syntax.
Moreover due to meaning of "GRANT ... NEW TABLES" that doesn't
exactly gives any permission to any table in schema at the moment of its
execution but only set default privilage for new object in schema
it (in my humble opinion) is more logical.

Thanx for reply.

--
Best Regards
Przemyslaw Kantyka
krycek6(at)wp(dot)pl
--
Używam programu pocztowego Opery: http://www.opera.com/mail/