New to DB2 and am wanting to grant SELECT to all tables in a schema to a group. Am hoping not to have to do this by individual tables (several thousand), but instead loop through all matching tables in SYSCAT.TABLES where TABSCHEMA = 'myschema'.

Anyone done this before?

Thanks.

This is what the syntax looks like:

GRANT SELECT TO TABLE {TABLE NAME}

Looking at the syntax there is no provision for entering multiple table names.

What I would do is to do a select of all the tables and capture it in a text file. And do find replace.

STEP 1:
Capture the contents of the following select statement into text file say capture.sql using the select statement

SELECT CONCAT TABSCHEMA CONCAT '.' TABNAME WHERE TABSCHEMA = 'myschema'

So the file will have enteries like...

myschema.table1
myschema.table2

STEP 2

Use your favourite text editor and REPLACE like...

REPLACE myschema with 'GRANT SELECT TO TABLE myschema'

So
myschema.table1
myschema.table2

will become...

GRANT SELECT TO TABLE myschema.table1
GRANT SELECT TO TABLE myschema.table2

STEP 3
And now run the capture.sql in CLI interface.

Maybe there is better way others can suggest:-)

Could do some thing like that

db2 -x +o -z grant.ddl "select 'grant select on '||'.'|| tabschema '||' to group test;' from syscat.tables where tabschema ='DB2INST1'"

// Change the DB2INST1 to your schema name and group test to your group


check the file

more grant.ddl

run

db2 -tvf grant.ddl


regards,

mujeeb