SEO

#1 (**permalink**) 04-29-2008, 08:26 PM

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 .

Thanks
-Kamal.

#2 (**permalink**) 04-29-2008, 08:26 PM

On Apr 25, 9:02 pm, Gladiator <vkamalnath1...@gmail.com> 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 .
>
> Thanks
> -Kamal.

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

/Lennart

#3 (**permalink**) 04-29-2008, 08:26 PM

Lennart wrote:

> On Apr 25, 9:02 pm, Gladiator <vkamalnath1...@gmail.com> 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Select privilage on all the tables in a schema.