4

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

Improve this question

1 Answer 1

Reset to default
6

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.

Improve this answer
4
  • Perfect, mustaccio! Thank you so much! :)
    – muddu83
    Jun 18, 2014 at 7:46
  • Kudos to the use of roles. Much easier than trying to just grant priveleges to IDs directly.
    – Chris Aldrich
    Jun 20, 2014 at 13:06
  • What if we there is a request for multiple tables or a complete schema having 100+ tables?
    – muddu83
    Jun 25, 2014 at 8:32
  • Obviously, you'll need to include multiple grant statements in your process. It's quite easy to generate a script for that by selecting from SYSCAT.TABLES.
    – mustaccio
    Jun 25, 2014 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.