HomeOracle The Role of Roles in Securing Oracle Database XE
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).
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
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.
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 rolesCONNECT,RESOURCE, andDBAare 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;
SQL> grant create session, create table, exp_full_database 2 to night_dba;
Creating just enough privileges for various classes of users and administrators rather than a blanketDBArole (with access to all privileges) enhances the security of your environment: each user or administrator gets exactly the privileges they need, and nothing more.