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.

This entry was posted in DB2. Bookmark the permalink.

6 Responses to Grant Schema-Wide SELECT Permission in DB2

  1. Igor says:

    That’s just grand!!!!!!

  2. Simon says:

    Hey, this works perfectly!

  3. Steve says:

    Does this script work for DB2 9.7? I am trying to enable an OS level user to have select only privileges on a database but the syntax is not working out.

  4. Pravin says:

    Can we grant the Select(read-only) access on Schema level.

    Thanks,
    Pravin

Leave a Reply

Your email address will not be published. Required fields are marked *