Create new Read Only user on DB2 on the whole database

Question

I am an oracle/sql server DBA having limited knowledge in IBM DB2. We have DB2 9.7 fp4 running on AIX 6. I would like to create a new user and grant him READ only privilege on a particular database. Please help!

Thanks & Regards Mohammed

Answer 1

There is no read only privilege on a database in DB2. You will need to grant SELECT privileges on specific tables, preferably via a role to avoid doing this multiple times.

1. Create a new database role, e.g. create role readonly.
2. Grant SELECT privileges on the required tables to that role: grant select on myschema.mytable to role readonly.
3. Create a new user in the operating system, e.g. # mkuser newuser.
4. Grant the role to the user: grant role readonly to user newuser.

Note that the new user will likely need to change the initial password before he or she can connect to the database.