CODEWITHSUNDEEP
db2
Kevin Lillybridge
Kevin Lillybridge

Reputation: 319

How do I grant select for a user on all tables?

I have a user in my DB2 database that I want to grant select rights on all tables and views for a given schema. Any thoughts on how to do that as one SQL statement?

Upvotes: 2

Views: 17663

Answers (1)

AngocA
AngocA

Reputation: 7693

In order to grant select to a given user, you have to "generate" the sentence for each table and view of a given schema. You can do it via the CLP with a query like this:

db2 -x "select 'grant select on table ' || rtrim(tabschema) || '.' || rtrim(tabname) || ' to user JOHN_DOE' from syscat.tables where tabschema like 'FOO%' and (type = 'T' or type = 'V')" | db2 +p -tv

This command line will generate the grants for user JOHN_DOE for all tables (T) and views (V) of any schema starting with FOO.

If you have many tables, the output will be very big and the internal buffer will be filled. Reissue the command by generating the grants for a smaller set of tables.

If you are not sure about what you are going to execute, issue the previous command without the final part (| db2 +p -tv), this will write the commands in the standard output. However, this part is the most important, because this executes the generated output.

For more details, please check the InfoCenter or my blog http://angocadb2.blogspot.com/2011/12/ejecutar-la-salida-de-un-query-en-clp.html (In Spanish)

Upvotes: 7

Related Questions