Grant Schema-Wide SELECT Permission in DB2

When managing user permissions in DB2 (version 9.5), sometimes one wants to grant the SELECT permission to all tables within a given schema. Unfortunately, this isn’t possible; only the schema permissions ALTERIN, CREATEIN, and DROPIN can be granted in DB2, which is not what we want.

A workaround is to collect a list of all tables within the given schema and grant the SELECT permission to each of them. On Linux/UNIX systems, this can be done as follows using DB2′s command line processor:

DBNAME=<em>dbname</em>
SCHEMANAME=<em>schemaname</em>
USERNAME=<em>username</em>

db2 "CONNECT TO $DBNAME"
TABLES=`db2 -x "SELECT tabname FROM syscat.tables WHERE tabschema=UPPER('$SCHEMANAME')"`
for TABLENAME in $TABLES;
  do db2 "GRANT SELECT ON $SCHEMANAME.$TABLENAME TO USER $USERNAME"
done
db2 "DISCONNECT $DBNAME"

The unavoidable disadvantage of this approach is that you have to run the above script each time new tables are added within the given schema.