Add privileges on every db2 table at once
Recently I ran into a problem where I needed to create a user in db2 and grant him only the SELECT privilege. Referring to the db2 documentation I learned that the privilege assignments take place one table at a time, like so:
1 |
GRANT SELECT ON <SCHEMA_NAME>.<TABLE_NAME> TO USER JBOND |
Problem with the above approach is that our db2 database is using more than 70 tables, consequently running command 70+ times was going to be a hassle.
Here’s the SOLUTION to assign the SELECT privilege on every table in a given schema:
1 2 3 |
db2 -x "SELECT 'GRANT SELECT ON ' || rtrim(tabschema) || '.' || rtrim(tabname) || ' TO USER jbond;' FROM SYSCAT.TABLES WHERE TABSCHEMA='<SCHEMA_NAME>' | db2 +p -tv |
NOTE: db2 +p -tv would execute the fetched results returned by the SELECT query