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]

On Apr 25, 9:02 pm, Gladiator <[e-mail]:

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]

Lennart wrote:

On Apr 25, 9:02 pm, Gladiator <[e-mail]:

>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]

On Apr 28, 11:04*pm, Knut Stolze <[e-mail]:

Lennart wrote:

On Apr 25, 9:02 pm, Gladiator <[e-mail]:

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