Home arrow Oracle arrow 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).

TABLE OF CONTENTS:
  1. The Role of Roles in Securing Oracle Database XE
  2. Creating or Dropping a Role
By: Apress Publishing
Rating: starstarstarstarstar / 1
January 27, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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

Definition

CONNECT

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.

RESOURCE

CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGERCREATE TYPE. Gives application developers privileges needed to code PL/SQL procedures and functions.

 

 

Table 31-10.  Predefined Oracle Roles (continued) 

 

 

Role Name

Definition

DBA

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.

DELETE_CATALOG_ROLE

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$.

EXECUTE_CATALOG_ROLE

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

SELECT_CATALOG_ROLE

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

EXP_FULL_DATABASE

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. 

IMP_FULL_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.

AQ_USER_ROLE

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

AQ_ADMINISTRATOR_ROLE

Manages Advanced Queuing queues.

SNMPAGENT

Provides required privileges for the Enterprise Manager Intelligent Agent.

RECOVERY_CATALOG_OWNER

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

HS_ADMIN_ROLE

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

SCHEDULER_ADMIN

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;

Role created.

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

Grant succeeded.

SQL>

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.



 
 
>>> More Oracle Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: