Hi,

I am trying to grant permission to all the tables in a particular schema. There are more than 100 tables in that schema and I do not want to type the grant statement for every table like

grant select on table1 to public;
grant select on table2 to public;
....
....

I tried to use the following statement.

select 'grant select on',tabname,' to public' from syscat.tables where tabschema='ABC'

This statement creates the select grant statements but does not grant permission. If I use this statement, then I have to export the output to a text file and then execute that text file which I do not want.

I am using DB2 command editor and I do not want to use Linux shell utility.

Can someone please help.

Thanks in advance.

Nav

Unfortunately there is not much we can do to help you. You have stated the only options that will allow you to do what you want.

Andy

Ok, if it can not be done in one statement using command editor then how should it be done using other platforms.

Nav

You can do it either of the two ways you mentioned. Use a select statement to generate a script that will do it and then run that script, or use a linux script to do the same thing.


Andy

navch, If you use Control Center, you can do this.

Expand User and Group objects under the database.
Click on Groups.
Right click on Public and select Change.
Click on the Table tab.
Click on Add Table.
Enter or Select a Schema.
Highlight all the tables (CNTL+A if you want everything) and click Ok.
Highlight all the tables (Click the first one, hold SHIFT key down and click the last one).
Change the Select Drop down box to Yes.
Click OK.