How to grant read and write permissions in DB2

    In older versions of DB2, the ever useful command “grant dataaccess” was not implemented yet. So, in order to grant permissions over a whole database, it is needed to grant the permissions manually, object by object. There are ways to do this relatively fast. The following example details how to easily grant permissions to a user or to a group in a database which runs in DB2 version 9.5.0.5:

    First, connect to the desired DB as the instance owner. For example, we will connect to the database named “MYSAMPLEDB” as its instance owner, named “instanceowner“:

su - instanceowner
db2 connect to MYSAMPLEDB

    Then, grant connect permission on the current database (in this case MYSAMPLEDB) to the desired user with the “grant connect” command. In this example we will grant connect permissions to the user named “user1“:

db2 grant connect on database to user user1

    Next, we will grant writing permissions to “user1” in all the tabschemas except SYSTEM:

db2 "select 'GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE ' || rtrim(tabschema)||'.'|| rtrim(TABNAME) || '  TO USER user1;' from syscat.tables where tabschema not like '%SYS%' " > /tmp/grants.ddl

    Instead of only one user, with the following command, permissions are granted to a group of users named “db2group” in all the schemas except SYSTEM:

db2 "select 'GRANT SELECT ON TABLE ' || rtrim(tabschema)||'.'|| rtrim(TABNAME) || '  TO GROUP db2group;' from syscat.tables where tabschema not like '%SYS%' " > /tmp/grants.ddl

    In any of those cases, the file /tmp/grants.ddl will contain a long list of commands that will need to be executed in order to actually provide the permissions. Checking the newly created file is recommended in order to see if any errors occurred. If everything is fine, we can proceed to the next step.

    As said before, it is needed to execute the list of commands that were inserted into the /tmp/grants.ddl file. In out example, this is done like this:

db2 -tvf /tmp/grants.ddl > /tmp/grants_output.txt

The /tmp/grants_output.txt file will contain any errors that may have occurred. This is easier, right?