User-defined user roles

If you want to have more flexibility and fine-grained access control to your data warehouse, you can create and use your own user-defined user roles to accommodate the special needs of your organization.

The Db2® data warehouse uses role-based access control (RBAC). Users are created and assigned a specific built-in user role with a predefined set of privileges on the system. The built-in user roles are simple to use and easy to manage. However, your organization might have requirements for users to have more specific roles when they access the data warehouse. The Db2 data warehouse supports the creation and use of user-defined user roles.

Definition of built-in user roles

In Enterprise plans, a user is created by the administrator and typically assigned one of the following built-in user roles: Administrator or User. These built-in user roles provide different levels of access commonly needed in a database system. The following general summary describes what each of the built-in user roles can do:

Administrator: Has access to all of the features in the console, and has the privileges to manage other users.

User: Has access to many of the features in the console, and has the privileges to manage their own user profile. Users with this role also have full access to their own tables and can use their tables with all permissions, including giving other users permission to access and use their tables.

Definition of user-defined user roles

The built-in set of user roles is simple to use and easy to manage, but if it does not provide you with the desired set of fine-grained permissions, you can also create your own user-defined user roles to satisfy your special requirements.

A role is a database object that groups together one or more privileges and can be assigned to users. A user that is assigned a role receives all of the privileges of that role. A user can have multiple roles. A role hierarchy is also supported. This allows one of the roles to inherit all of the privileges and authorities of the other role or roles that they have been granted.

A user-defined user role simplifies the administration and management of privileges by allowing the administrator to group authorities and privileges into a single role and then grant this role to the users that need those authorities and privileges to perform their jobs.

Creating and using user-defined user roles

The following example instructions describe how you can create and use user-defined user roles in an Enterprise plan.

As a prerequisite, you must be able to execute SQL statements and scripts against the database to create user-defined user roles. You can use one of the following methods:

The Run SQL feature in the console. You can create and run your SQL statements and scripts against the database from the console.
The Command line processor plus (CLPPlus) interface. The driver package includes the CLPPlus tool, which is a command-line tool that you can use to run queries against the database from your computer. You can download the driver package from the console.
The IBM® Data Studio client. The IBM Data Studio client provides both database administrative and application development capabilities.

Let’s assume that you want to create and add the new users ALICE, TOM, and BOB as members of the user-defined roles DEVELOPER, TESTER, and OPERATOR, as shown in the following table:

Table 1. User-defined roles example
Built-in role	User-defined role	User
User	DEVELOPER	ALICE
User	TESTER	TOM, ALICE
User	OPERATOR	BOB

Log in to the console as an Administrator.
Add new users ALICE, TOM, and BOB, and assign them to the built-in User role.
From the Run SQL page (or start either CLPPlus or the IBM Data Studio client), create the user-defined user roles DEVELOPER, TESTER, and OPERATOR by running the following example SQL statements:
```
CREATE ROLE DEVELOPER;
```
```
CREATE ROLE TESTER;
```
```
CREATE ROLE OPERATOR;
```

Grant membership in the user-defined roles to users by running the following example SQL statements:

GRANT ROLE DEVELOPER TO USER ALICE;

GRANT ROLE TESTER TO USER TOM, USER ALICE;

GRANT ROLE OPERATOR TO USER BOB;

Grant the following example privileges to the user-defined roles:
1. Create table WORKITEM under schema SAMPLE:
```
CREATE TABLE SAMPLE.WORKITEM(x int);
```
2. Grant different table privileges to the user-defined user roles DEVELOPER, TESTER, and OPERATOR:
```
GRANT ALL ON SAMPLE.WORKITEM TO ROLE DEVELOPER;
```
```
GRANT ALTER ON SAMPLE.WORKITEM TO ROLE TESTER;
```
```
GRANT SELECT ON SAMPLE.WORKITEM TO ROLE OPERATOR;
```

Users ALICE, TOM, and BOB inherit the privileges and authorities of the user-defined user roles DEVELOPER, TESTER, and OPERATOR, and thereby have different privileges on table SAMPLE.WORKITEM.

Conclusion

Db2 warehouse databases provide the ability to use built-in user roles and to create your own user-defined user roles to manage user privileges and authorities with simplicity and flexibility.