December 3, 2015 | Written by: edong
Share this post:
The IBM dashDB data warehouse uses role-based access control (RBAC) to restrict access to a dashDB system. 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.
If you want to have more flexibility and fine-grained access control to the dashDB system, you can create and use your own user-defined user roles to accommodate the special needs of your organization. This post explains what the built-in user roles can do and how to create your own user-defined user roles in dashDB.
Definition of dashDB’s built-in user roles
In dashDB 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 dashDB console, and has the privileges to manage other users
- User -Has access to many of the features in the dashDB 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.
For a more detailed list of the Administrator and User role privileges, see Learn more on the Settings > Users and Privileges page in the dashDB console.
Definition of dashDB’s 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 in dashDB 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 a dashDB Enterprise plan.
As a prerequisite, you must be able to execute SQL statements and scripts against the dashDB database to create user-defined user roles in dashDB. You can use one of the following methods:
- The Run SQL feature in the dashDB console. You can create and run your SQL statements and scripts against the dashDB database from the dashDB console
- The Command line processor plus (CLPPlus) interface. The dashDB driver package includes the CLPPlus tool, which is a command-line tool that you can use to run queries against the dashDB database from your computer. You can download the driver package from the Connect > Download Tools page in the dashDB console
- The IBM Data Studio client. The 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:
- Log in to the dashDB console as an Administrator.
- Go to the Settings > Users and Privileges page, add new users ALICE, TOM, and BOB, and assign them to the built-in User role.
- Go to the Run SQL page (or start either CLPPlus or the Data Studio client), create the user-defined user roles DEVELOPER, TESTER, and OPERATOR:
CREATE ROLE DEVELOPER;
CREATE ROLE TESTER;
CREATE ROLE OPERATOR;
- Grant membership in the user-defined roles to users:
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:
a. Create table WORKITEM under schema SAMPLE:
CREATE TABLE SAMPLE.WORKITEM(x int);
b. 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.
IBM dashDB provides 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.
For more information about roles and role-related SQL statements, see the IBM dashDB Knowledge Center.