The Role of Roles in Securing Oracle Database XE

In this eighth part of a ten-part series on securing Oracle Database XE, you will learn what roles are, why they are important to database security, and how to create, assign and maintain roles, among other things. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Creating, Assigning, and Maintaining Roles

A role is a named group of privileges, either system privileges or object privileges or a combination of the two, that helps to ease the administration of privileges. Rather than granting system or object privileges individually to each user, you can grant the group of system or object privileges to a role, and in turn the role can be granted to the user instead. This tremendously reduces the amount of administrative overhead involved in maintaining privileges for users. Figure 31-2 shows how a role can reduce the number of GRANT commands (and ultimately REVOKE commands) that you need to execute when roles are used to group privileges.

If the privileges for a group of people authorized by a role need to change, only the privileges of the role need to be changed, and the capabilities of the users with that role automatically use the new or changed privileges. Roles may selectively be enabled by a user; some roles may automatically be enabled at login. In addition, passwords can be used to protect a role, adding another level of authentication to the capabilities in the database.

Figure 31-2.  Using roles to manage privileges

Table 31-10 shows the most common roles that Oracle automatically provides with the database, along with a brief description of the privileges that come with each role.

Table 31-10. Predefined Oracle Roles  



Role Name



ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW. Gives a user the right to connect to the database and create tables, indexes, and views.





Table 31-10.  Predefined Oracle Roles (continued) 



Role Name



Allows a person with the DBA role to grant system privileges to others. This is the equivalent of giving a user all system privileges WITH ADMIN OPTION.


Allows a user to remove audit records from the audit trail for regular or fine-grained auditing. Provides only object privileges (DELETE) on SYS.AUD$and FGA_LOG$.


Gives privileges on various system packages, procedures, and functions, such as DBMS_FGAand DBMS_RLS. 


Provides the SELECTobject privilege on 1,638 data dictionary tables.


EXECUTE_CATALOG_ROLE, SELECT_CATALOG_ROLE, and system privileges such as BACKUP ANY TABLEand RESUMABLE. Allows a user with this role to export all objects in the database. 


Allows the import of a previously exported full database; similar to EXP_FULL_DATABASE, with many more system privileges, such as CREATE ANY TABLE.


Provides execute access on routines needed for Advanced Queuing, such as DBMS_AQ.


Manages Advanced Queuing queues.


Provides required privileges for the Enterprise Manager Intelligent Agent.


Provides privileges to a user who owns a recovery catalog for RMANbackup and recovery.


Provides access to the tables HS_*and the package DBMS_HS for administering Oracle Heterogeneous Services.


Provides access to the DBMS_SCHEDULERpackage, along with privileges to create batch jobs.



The roles CONNECT , RESOURCE , and DBA are provided mainly for compatibility with previous versions of Oracle; they may not exist in future versions of Oracle. You should create custom roles using the privileges granted to these roles as a starting point, as in this example:

SQL> create role night_dba;

Role created.

SQL> grant create session, create table, exp_full_database
  2  to night_dba;

Grant succeeded.


Creating just enough privileges for various classes of users and administrators rather than a blanket DBA role (with access to all privileges) enhances the security of your environment: each user or administrator gets exactly the privileges they need, and nothing more.

{mospagebreak title=Creating or Dropping a Role}

To create a role, you use the CREATE ROLE command, and you must have the CREATE ROLE system privilege. Typically, you grant this only to database administrators or application administrators. Here’s an example:

SQL> create role hr_admin;
Role created.

By default, no password or authentication is required to enable or use an assigned role. As with creating users, you can authorize use of a role by requiring a password for the role (creating the role with the IDENTIFIED BY PASSWORD clause), using the operating system’s authentication methods (creating the role with the IDENTIFIED EXTERNALLY clause), or using a network or directory service (creating the role with the IDENTIFIED GLOBALLY clause).

Granting Privileges to a Role

Assigning a privilege to a role is very straightforward; you use the GRANT command just as you would assign a privilege to a user:

SQL> grant select on hr.employees to hr_clerk;
Grant succeeded.
SQL> grant create trigger to hr_clerk;
Grant succeeded.

In this example, we’ve assigned an object privilege and a system privilege to the HR_CLERK role.

Assigning or Revoking Roles

Once you have the desired system and object privileges assigned to the role, you can assign the role to a user, using familiar syntax:

SQL> grant hr_clerk to kellyc;
Grant succeeded.

Any other privileges granted to the HR_CLERK role in the future will automatically be useable by KELLYC because KELLYC has been granted the role.

Roles may be granted to other roles; this allows a DBA to have a hierarchy of roles, making role administration easier. For example, there could be roles named DEPT30 , DEPT50 , and DEPT100 , each having object privileges to tables owned by each of those departments. An employee in department 30 would be assigned the DEPT30 role, and so forth. The president of the company would like to see tables in all departments; but rather than assigning individual object privileges to the role ALL_DEPTS , you can assign the individual department roles to ALL_DEPTS :

SQL> create role all_depts;
Role created.
SQL> grant dept30, dept50, dept100 to all_depts;
Grant succeeded.
SQL> grant all_depts to kellyc;
Grant succeeded.

The role ALL_DEPTS may also contain individual object and system privileges that do not apply to individual departments, such as object privileges on order entry tables or accounts receivable tables.

Revoking a role from a user is very similar to revoking privileges from a user:

SQL> revoke all_depts from kellyc;
Revoke succeeded.

The privileges revoked will no longer be available to the user the next time they connect to the database. However, it is worth noting that if another role contains privileges on the same objects as the dropped role, or privileges on the objects are granted directly, the user retains these privileges on the objects until these and all other grants are explicitly revoked.

Please check back next week for the ninth part of this article series.

Google+ Comments

Google+ Comments