Select privilage on all the tables in a schema.

[Gladiator]

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.

[Lennart]

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

On Apr 25, 9:02 pm, Gladiator <vkamalnath1... @gmail.comwrote :

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

[Knut Stolze]

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

Lennart wrote:

On Apr 25, 9:02 pm, Gladiator <vkamalnath1... @gmail.comwrote :

>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

[agrkanhaiya@gmail.com]

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

On Apr 28, 11:04 pm, Knut Stolze <sto...@de.ibm. comwrote:

Lennart wrote:

On Apr 25, 9:02 pm, Gladiator <vkamalnath1... @gmail.comwrote :

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- Hide quoted text -
>
- Show quoted text -

Hi Knut,
Even i am facing the same scenarios .,, can you please explain the
solution in more details ..

Thanks,
Kanhaiya