Re: Select privilage on all the tables in a schema.

Lennart wrote:

> On Apr 25, 9:02 pm, Gladiator <vkamalnath1...@[EMAIL PROTECTED]
> wrote:
>> Any Suggestions on the below scenario will be helpful to us.
>>
>> # There are 10 tables in a schema “S1” and  I have to give select
>> privilege (Only read access) to a user on all the tables in the schema
>> “S1”.
>>
>> # Initially I have given the required privileges to the user with
>> grant command on every table..
>>
>> #  But the problem here is whenever there are new tables building in
>> the schema “S1”, We have to give the privilege Explicitly.
>>
>> # It is okay if we have less number of tables and  the changes are not
>> frequent, But our case is the tables are changing and there are
>> hundreds  of it.
>>
>> # Is there any way we to automate whenever a new table is created in
>> that schema the select privilege should go to user .
> 
> AFAIK it is not possible (but it would be great if someone proved me
> wrong :-). I solved the problem with a script that loops over all the
> tables in a given schema and grant select on each one to a user

Another alternative would be to implement an ACL-like table where each
entry
in marks the access of a specific user or group to the table (or schema). 
Then you create a view over each table and join with the ACL-table in the
view definition.  You grant SELECT privileges to PUBLIC on each view and
the view definition takes care of the rest.

-- 
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany