Creating and using database roles

In this recipe, you'll learn the basics about database roles. Roles group together related system and/or object privileges and they can be granted to users and other roles. They simplify privilege management (for example, rather than granting the same set of privileges to many users, you can grant those privileges to a role and then grant that role to users that need those privileges).

Getting ready

For this recipe, you will need an existing (for example, OS-authenticated) user that has a dba role and another three existing users (for example, mike, tom, and jessica). It is assumed that sample schemas are installed.

How to do it...

1. Connect to the database as a user who has a dba role:

   $ sqlplus /
   

2. Create the role usr_role:

   SQL> create role usr_role;
   

3. Grant system privilege to usr_role:

   SQL> grant create session to usr_role;
   

4. Grant object privileges to usr_role:

   SQL> grant select, insert on hr.employees to usr_role;
   

5. Create another role as follows:

   SQL> create role mgr_role;
   

6. Grant usr_role to mgr_role:

   SQL> grant usr_role to mgr_role;
   

7. Grant system privileges to mgr_role:

   SQL> grant create table to mgr_role;
   

8. Grant object privileges to mgr_role:

   SQL> grant update, delete on hr.employees to mgr_role;
   

9. Grant usr_role to user (mike):

   SQL> grant usr_role to mike;
   

10. Grant mgr_role to user (tom):

    SQL> grant mgr_role to tom;
    

How it works...

In the first step, you used OS authentication to connect to the database. In steps 2 and 3, you granted system privileges and object privileges, respectively, to the role usr_role. In the next steps, you practiced using database roles; you granted the following:

• A role to another role
• System and object privileges to role
• Roles to users

You revoke privileges and roles by using a revoke statement. For example:

SQL> revoke usr_role from mike;

Note

Circular granting of roles is not allowed.

SQL> grant role1 to role2;
Grant succeeded.

SQL> grant role2 to role1;
grant role2 to role1
*
ERROR at line 1: ORA-01934: circular role grant detected

There's more...

Tip

You should be careful about granting privileges to the PUBLIC role because then every database user can use these privileges.

Suppose that user mike grants object privilege to user jessica with a grant option and user jessica grants that privilege to user tom. If user mike revokes that privilege from jessica, it will be automatically revoked from tom.

Note

Revoking a system privilege will not cascade.

SQL> grant select on hr.employees to jessica with grant option;
Grant succeeded.

SQL> connect jessica
Enter password:
Connected.

SQL> grant select on hr.employees to tom;
Grant succeeded.

SQL> connect tom/oracle_123
Connected.

SQL> select count(*) from hr.employees;
COUNT(*)
----------
 107

SQL> connect mike/welcome1
Connected.

SQL> revoke select on hr.employees from jessica;
Revoke succeeded.

SQL> connect tom/oracle_123
Connected.

SQL> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist

Note

You cannot revoke object privileges you didn't grant.

See also

• If you want to learn more about roles, see the official Oracle documentation—Oracle Database Security Guide 12c Release 1 (refer Chapter 4, Configuring Privilege and Role Authorization, of this documentation).