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.