DB2 - Roles

Introduction

A role is a database object that groups multiple privileges that can be assigned to users, groups, PUBLIC or other roles by using GRANT statement.

Restrictions on roles

• A role cannot own database objects.
• Permissions and roles granted to groups are not considered when you create the following database objects.
  • Package Containing static SQL
  • Views
  • Materialized Query Tables (MQT)
  • Triggers
  • SQL Routines

Creating and granting membership in roles

Syntax: [To create a new role]

db2 create role <role_name> 

Example: [To create a new role named ‘sales’ to add some table to be managed by some user or group]

db2 create role sales 

Output:

DB20000I The SQL command completed successfully. 

Granting role from DBADM to a particular table

Syntax: [To grant permission of a role to a table]

db2 grant select on table <table_name> to role <role_name> 

Example: [To add permission to manage a table ‘shope.books’ to role ‘sales’]

db2 grant select on table shope.books to role sales 

Output:

DB20000I  The SQL command completed successfully. 

Security administrator grants role to the required users. (Before you use this command, you need to create the users.)

Syntax: [To add users to a role]

db2 grant role <role_name> to user <username> 

Example: [To add a user ‘mastanvali’ to a role ‘sales’]

db2 grant sales to user mastanvali  

Output:

DB20000I  The SQL command completed successfully. 

Role hierarchies

For creating a hierarchies for roles, each role is granted permissions/ membership with another role.

Syntax: [before this syntax create a new role with name of “production”]

db2 grant role <roll_name> to role <role_name>

Example: [To provide permission of a role ‘sales’ to another role ‘production’]

db2 grant sales to role production