Database role can be defined as the collection privileges assigned one or several users. Each database role is given a name. Most RDBMS platforms have set of predefined roles assigned to a user. Every user with CREATE ROLE privilege can create his/her own database role. Roles in database allow a user to set up hierarchical privileges among other database users. The process of setting hierarchical privileges among the users is called as Role based access control.
- Read: More Interview Questions
How to Create Roles in Database?
Roles in database are created by using the statement CREATE ROLE. DROP ROLE statement is used to drop the created roles in database. The roles are extended with privileges by using GRANT statement. The privileges assigned to a user can be revoked by using the REVOKE statement. A user can obtain all the privileges allied with a role by using the statement SET ROLE. The CURRENT_ROLE function will return the present role in a statement. Only roles which are granted to the user can be set along with the user while setting other role to user is not permitted.
Example for Creating Role in Database
The following SQL statements are used to create a role and grant some privileges to it.
CREATE ROLE player1;
GRANT SHOW DATABASES ON *.* TO player1;
GRANT player1 to cricket;
Since there is no SHOW DATABASE privilege to cricket, it needs to be set first
SHOW DATABASES;
DatabaseName
info_schema
SELECT CURRENT_ROLE;
CURRENT_ROLE
NULL
SET ROLE player1;
SELECT CURRENT_ROLE;
CURRENT_ROLE
player1
SHOW DATABASES;
DatabaseName
info_schema
mysql1
per_schema
test1
SET ROLE NONE;
The Roles are to be now granted to their roles
CREATE ROLE midfielder1;
GRANT SELECT ON data1.* TO midfielder1;
GRANT midfielder1 TO player1;
Setting a role to granted role is not required by the user. For example football will acquire all the privileges when the player role is set. The SQL for acquiring privileges from player role is given below.
SELECT CURRENT_ROLE;
CURRENT_ROLE
NULL
SHOW TABLES FROM data1;
Empty set (0.01 sec)
SET ROLE player1;
SELECT CURRENT_ROLE;
CURRENT_ROLE
player1
SHOW TABLES FROM data1;
Tables_in_data
set1
Roles and Stored Routines
When a role in database is set by a user, the role will have two set of identities which are associated with a set of privileges. Stored routines which are also known as vies has only one definer. The SQL SECURITY DEFINER is used to create a stored routine. In the stored routine, the user can mention whether the diner is for CURRENT_USER or CURRENT_ROLE. The user will have user’s privilege while role has role privilege. So view which is impossible to use will be created.
Roles Restriction in DB2:
- Database objects cannot be owned by roles.
When the following objects are created in DB2 database then privileges and roles are not considered.
- SQL Routines
- Views
- Triggers
- Materialized Query Tables
- Packages with static SQL.
The following syntax is used to create and grant membership in roles
db2 create role <role_name>
Basic syntax for granting role from DBADM to a particular role
db2 grant select on table <table_name> to role <role_name>
Basic syntax to add users in a role
db2 grant role <role_name> to user <user_name>
Role Hierarchy in DB2 Database
Hierarchies for a role are created by granting privileges with another role.
Basic syntax for creating a role hierarchy in DB2 database.
db2 grant role <roll_name> to role <role_name>
Disadvantages of Role in Database
- A role granted to a user may contain more privileges than actually required by the user.
- Granting more privileges than required by a user may lead to potential security problems.
- The users may abuse the extra privileges provided to them.
- Abusing the extra privileges by the user may lead to database ruin.