Oracle9i Application Developer's Guide - Fundamentals
Release 1 (9.0.1)

Part Number A88876-02
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Database Security Overview for Application Developers

This chapter provides a basic understanding of application and database security policies. The following security policy topics are included in this chapter:

See Also:

Oracle Security Overview  

Introduction to Database Security Policies

This section briefly introduces security policies. It covers:

Security Threats and Countermeasures

Organizations should create a written security policy to enumerate the security threats they are trying to guard against, and the specific measures the organization must take. Security threats can be addressed with different types of measures:

Consider whether the appropriate response to a threat is procedural, physical, technical, or personnel-related, or whether the threat can be met by a combination of the above measures.

For example, one possible security threat is disruption of critical business systems caused by a malicious person damaging a computer. A physical response to this threat is to secure key business computers in a locked facility. A procedural response is to create system backups at regular intervals. Personnel measures could include background checks on employees who access or manage key business systems.

Oracle9i offers many mechanisms which can implement the technical measures of a good security policy.

What Information Security Policies Can Cover

In addition to requirements unique to your environment, you should design and implement information security policies to address the following important issues:

Features to Use in Establishing Security Policies

The following elements of Oracle9i enable you to address security issues of a technical nature:

Application Security

Use this feature to attach privileges and roles to each application, while making sure that users do not misuse those roles and privileges when they are not using the application.

Fine-Grained Access Control

Use this feature to implement security policies at a high level of granularity; for example, to enforce row level security. Do this by creating security policy functions attached to the table or view on which you have based your application. Then, when a user enters a DML statement on that object, Oracle modifies that statement dynamically and transparently to the user.

Application Context

Use this feature to set up session-based attributes securely. For example, you can securely store such user attributes as a user name, employee number, and your position in the management hierarchy. You can retrieve that information later in the session and use it for fine-grained access control.

Secure Application Role

Use this feature to base use of roles on user-defined criteria. For example, you could allow use of a role by a user connecting only from a particular IP address, or accessing the database only through a particular middle tier.

Fine-Grained Auditing

Use this feature to monitor query access based on content. For example, you can monitor users accessing a specific row within a table. This feature can also be used to detect data misuse or serve as an intrusion detection system.

Oracle Label Security

Use this Oracle9i data server option to enforce fine-grained access control and label-based access control automatically. For example, you can label data Company Confidential or Partner Releaseable to automatically limit access to data based on the label of the data and the labels of data a user is permitted to access. By using Oracle Label Security, organizations can implement fine-grained and label-based access control quickly, in many cases without additional programming

Proxy Authentication

Use this feature to preserve user identity through a middle tier to the database, without the overhead of a separate database connection. It is able to proxy the user identity and credentials, such as a password or X.509 certificate, through the middle tier to the database. Also, on behalf of a user, it supports auditing of connections.

Data Encryption

Use this feature to encrypt information, as an extra measure of security.

See Also:

Oracle Security Overview  

Recommended Application Design Practices to Reduce Risk

To avoid potential problems, use the following recommended practices when implementing database roles. Each practice is explained in detail below.

Tip 1: Enable and Disable Roles Promptly

Enable the proper role when the application starts, and disable it when the application terminates. To do this, you must take the following approach:


Database roles granted to users can nonetheless be enabled by users outside the application. Such use is not controlled by application-based security. Again, virtual private database is the best way to solve this problem. Also, in three-tier systems, it is possible to restrict the users from using the role outside of the application, by using a secure application role. 

Additionally, you can:

Tip 2: Encapsulate Privileges in Stored Procedures

Another way to restrict users from exercising application privileges by way of ad hoc query tools is to encapsulate privileges into stored procedures. Grant users execute privileges on the procedures, rather than issuing them direct privilege grants. In this way, the logic goes with the privilege.

This allows users to exercise privileges only in the context of well-formed business applications. For example, consider authorizing users to update a table only by executing a stored procedure, rather than by updating the table directly. By doing this, you avoid the problem of the user having the SELECT privilege and using it outside the application.

See Also:

"Example 3: Event Triggers, Application Context, Fine-Grained Access Control, and Encapsulation of Privileges" 

Tip 3: Use Role Passwords Unknown to the User

Grant privileges through roles that require a password unknown to the user.

If there are privileges which the user should use only within the application, you can enable the role by a password known only by the creator of the role. Use the application to issue a SET ROLE statement. Because the user does not have the password, you should either embed the password in the application or use a stored procedure to retrieve the role password from a database table. This measure discourages users from avoiding use of the application. However, while it does improve application security, it is not foolproof.

A user with access to application code could potentially find the password that is embedded in the application. This security by obscurity is not a good security practice. Embedding a password in the application protects against a user who wants to bypass the application (a lazy user). It does not protect against the user who deliberately wants to misuse privileges by accessing data and bypassing the application (a malicious user). Since client code can be decompiled and the embedded password recovered, you should only use the embedded password method to protect against the lazy users.

To use the stored procedure to retrieve the role password from a database table, a user would need EXECUTE permission, then execute the procedure, retrieve the password, and use the role outside of the application.

Tip 4: Use Proxy Authentication and a Secure Application Role

In three-tier systems, it is possible to enable a role only when the user accesses the database through a middle-tier application. This requires the use of proxy authentication and a secure application role. Proxy authentication distinguishes between a middle tier creating a session on behalf of a user and the user connecting directly. Both the proxy user (the middle tier) and the real user information are captured in the user session. A secure application role, which is implemented by a package, can do desired validation before allowing the user to assume the privileges in the role. When the application uses proxy authentication, the secure application role can validate that the user session was created by proxy, and that the user is connecting to the database through an application, and not directly.

Consider a situation in which you want to restrict use of an HR administration role to users accessing the database (by proxy) through the middle tier HRSERVER. You could create the following secure access role:


Here, hr.admin is a package which performs desired validation. The package can determine if a user is connected by proxy using SYS_CONTEXT ('userenv', 'proxy_userid'), or SYS_CONTEXT (userenv', 'proxy_user'), or both return the ID and name of the proxy user (HRSERVER, in this case). If the user attempts to connect directly to the database, the hr.admin package will not allow the role to be set.

Tip 5: Use Secure Application Role to Verify IP Address

The secure application role can use additional information in the user session in order to restrict access. IP-address based security is not foolproof, since IP addresses can be spoofed. Therefore, you should never use IP address to make primary access control decisions, but you could use IP address to further restrict access, in addition to other controls. For example, you may want to ensure that a user session was created by proxy and that a middle tier user connecting from a particular IP address created the user session. Of course, the middle tier must authenticate itself to the database before creating a lightweight session, and the database ensures that the middle tier has privilege to create a session on behalf of the user. Your secure application role could verify the IP address of the incoming connection to ensure that the HRSERVER connection (or the lightweight user session) is coming from the appropriate IP address using SYS_CONTEXT (userenv',' 'ip_address') before allowing SET ROLE to succeed. This provides an additional layer of security.

Tip 6: Use Application Context and Fine-Grained Access Control

In this scenario, you combine server-enforced fine-grained access control and, through application context, session-based attributes.

See Also:

"Ways to Use Application Context with Fine-Grained Access Control" 

Introduction to Application Security Policies

You should draft security policies for each database application. For example, each database application should have one or more database roles that provide different levels of security when executing the application. The database roles can be granted to user roles, or directly to specific usernames.

Applications that potentially allow unrestricted SQL statement execution (through tools such as SQL*Plus) also need security policies that prevent malicious access to confidential or important schema objects.

This section describes the following aspects of application security policies:

Considerations for Using Application-Based Security

There are many issues to consider when you formulate and implement application security. Two of the main considerations are these:

Are Application Users Also Database Users?

Oracle Corporation recommends that, where possible, you build applications in which application users are database users. In this way you can leverage the intrinsic security mechanisms of the database.

For many commercial packaged applications, application users are not database users. For these applications, multiple users authenticate themselves to the application, and the application then connects to the database as a single, highly-privileged user. We will call this the "One Big Application User" model.

Applications built in this fashion generally cannot use many of the intrinsic security features of the database, because the identity of the user is not known to the database.

For example, use of the following features is compromised by the One Big Application User model:


A basic principle of security is accountability through auditing. However, if all actions in the database are performed by One Big Application User, then database auditing cannot hold individual users accountable for their actions. The application must implement its own auditing mechanisms to capture individual users' actions.

Oracle Advanced Security enhanced authentication

Strong forms of authentication supported by Oracle Advanced Security (such as, client authentication over SSL, tokens, and so on) cannot be used if the client authenticating to the database is the application, rather than an individual user.


Roles are assigned to database users. Enterprise roles are assigned to enterprise users who, though not created in the database, are known to the database. If application users are not database users, then the usefulness of roles is diminished. Applications must then craft their own mechanisms to distinguish between the privileges which various application users need to access data within the application.

Enterprise user management feature of Oracle Advanced Security

This feature enables users and their authorizations to be centrally managed in an LDAP-based directory such as Oracle Internet Directory. While enterprise users do not need to be created in the database, they do need to be known to the database. The One Big Application User model cannot take advantage of user and authorization management in LDAP.

Is Security Enforced in the Application or in the Database?

Applications whose users are also database users can either build security into the application, or rely upon intrinsic database security mechanisms such as granular privileges, virtual private database (fine-grained access control with application context), roles, stored procedures, and auditing (including fine-grained auditing). To the extent possible, Oracle recommends that applications utilize the security enforcement mechanisms of the database.

When security is enforced in the database itself, rather than in the application, it cannot be bypassed. The main shortcoming of application-based security is that security is bypassed if the user bypasses the application to access data. For example, a user who has SQL*Plus access to the database can execute queries without going through the Human Resources application. The user thus bypasses all of the security measures in the application.

Applications that use the One Big Application User model must build security enforcement into the application rather than using database security mechanisms. In this case, since it is the application--and not the database--which recognizes users, the application must enforce any per-user security measures itself.

This approach means that each and every application which accesses data must reimplement security. For example, if an organization implements a new report-writing tool, then it must also implement security to ensure that users do not get more data access through the report-writing tool than they would have in the application itself. Security becomes expensive because organizations must implement the same security policies in multiple applications. Each new application requires an expensive reimplementation.

See Also:

"Use of Ad Hoc Tools a Potential Security Problem" 

Security-Related Tasks of Application Administrators

In large database systems with many applications, you may decide to have application administrators. An application administrator is responsible for the following:

Managing Application Privileges

Most database applications involve different privileges on different schema objects. Keeping track of which privileges are required for each application can be complex. In addition, authorizing users to run an application can involve many GRANT operations. This section provides some features to managing application privileges. Managing application privileges includes the following:

Creating Roles to Simplify Application Privilege Management

To simplify application privilege management, you can create a role for each application and grant that role all the privileges a user needs to run the application. In fact, an application might have a number of roles, each granted a specific subset of privileges that allow greater or lesser capabilities while running the application.

For example, suppose that every administrative assistant uses the Vacation application to record vacation taken by members of the department. To best manage this application, you should:

  1. Create a VACATION role.

  2. Grant all privileges required by the Vacation application to the VACATION role.

  3. Grant the VACATION role to all administrative assistants or to a role named ADMIN_ASSISTS (if previously defined).

Advantages of Grouping Application Privileges in Roles

Grouping application privileges in a role aids privilege management. Consider the following administrative options:

Creating Secure Application Roles

Database access is based on privileges, which are often grouped into roles. Once grouped, the roles are granted to the application user. In previous releases, one would embed a password inside the application to ensure that users only enable the granted roles within the application. Roles secured by embedding passwords inside their applications are called application roles.

In Oracle9i, application developers no longer need to secure a role by embedding passwords inside applications. They can create application roles and specify which PL/SQL package is authorized to enable the roles. These application roles, those enabled by PL/SQL packages, are called secure application roles.

Within the package that implements the secure application role:

Topics in this section include:


Because users can not change security domain inside Definer's Right procedures, secure application roles can only be enabled inside Invoker's Right procedures. 

Example of Creating a Secure Application Role

To create a secure application role:

  1. Create the roles as application roles and specify the authorized package that will enable the roles. In this example, hr.hr_admin is the specified authorized package.

    CREATE ROLE admin_role IDENTIFIED USING hr.hr_admin;
    CREATE ROLE staff_role IDENTIFIED USING hr.hr_admin;
  2. Create an invoker's right procedure.

    PROCEDURE hr_app_report
    /* set application context in 'responsibility' namespace */
    /* authentication check here */
    if (Hr.MySecurityCheck = TRUE)
    /* check 'responsibility' being set, then enable the roles without 
    supplying the password */
    if (sys_context('hr','role') = 'admin' ) 
    end if;
    end if;
    /* Create a dedicated authentication function for manageability so that 
    changes in authentication policies would not affect the source code of the 
    application - this design is up the application developers */
    /* the only policy in this function is that current user must have been 
    authenticated using the proxy user 'SCOTT' */
    /* a simple check to see if current session is authenticated 				
    by the proxy user 'SCOTT' */
    if (sys_context('userenv','proxy_user') = 'SCOTT')
    return TRUE;
    return FALSE;

When enabling the secure application role, Oracle verifies that the authorized PL/SQL package is on the calling stack. This step verifies that the authorized PL/SQL package is issuing the command to enable the role. Also, when enabling the user's default roles, no checking will be performed for application roles.

Using Secure Application Role to Ensure Database Connection

Since a secure application role is a role implemented by a package, the package can do desired validation, such as ensuring that users can connect to the database through a middle tier or from a specific IP address. In this way, users are prevented from accessing data outside an application. They are forced to work within the framework of the application privileges that they have been granted.

Associating Privileges with the User's Database Role

A single user can use many applications and associated roles. However, you should ensure that the user has only the privileges associated with the running database role. Consider the following scenario:

In this scenario, an order entry clerk who has been granted both roles, can presumably use the privileges of the ORDER role when running the INVENTORY application to update the INVENTORY table. The problem is that updating the INVENTORY table is not an authorized action when using the INVENTORY application, but only when using the ORDER application.

To avoid such problems, consider using either the SET ROLE statement or the SET_ROLE procedure as explained below. You can also use the secure application role feature to allow roles to be set based on criteria you define.

Topics in this section include:

Using the SET ROLE Statement

Use a SET ROLE statement at the beginning of each application to automatically enable its associated role and, consequently, disable all others. In this way, each application dynamically enables particular privileges for a user only when required.

The SET ROLE statement facilitates privilege management because, in addition to letting you control what information a user can access, it allows you to control when a user can access it. In addition, the SET ROLE statement keeps users operating in a well-defined privilege domain. If a user obtains privileges only from roles, then the user cannot combine these privileges to perform unauthorized operations.

See Also:

"Enabling and Disabling Roles" 

Using the SET_ROLE Procedure

The PL/SQL package DBMS_SESSION.SET_ROLE is functionally equivalent to the SET ROLE statement in SQL.

A limitation of roles is the inability to SET ROLE within a definer's rights procedure. The reason is that, for a definer's rights procedure, the database checks privileges at compilation time, not at execution time. That is, the database verifies that the owner of the procedure has necessary privileges--granted to him directly, not through a role--at the time the procedure is compiled. A SET ROLE statement does not work because the role is not enabled at compilation time, when the database checks privileges. At execution time, when the role is to be enabled, the database does not check the owner's privileges; instead, the database merely ensures that a user of the procedure has EXECUTE privilege on the procedure.

In cases where the database checks privileges at execution time rather than at compilation time, it is possible to issue a SET ROLE. Thus, the DBMS_SESSION.SET_ROLE command can be called from the following:

In both the above cases, the database checks privileges at execution time, not at compilation time. Therefore, the database can validate that a user has the appropriate privileges (that is, that the user has been granted the role that is being set).


If you use DBMS_SESSION.SET_ROLE within an invoker's rights procedure, the role remains in effect until you explicitly disable it. In keeping with the least privilege principle, (that users should have the fewest privileges they need to do their jobs), you should explicitly disable roles set within an invoker's rights procedure, at the end of the procedure. 

Because PL/SQL performs the security check on SQL when an anonymous block is compiled, SET_ROLE will not affect the security role (in other words, it will not affect the roles enabled) for embedded SQL statements or procedure calls.

Examples of Assigning Roles with Static and Dynamic SQL

This section shows how static and dynamic SQL affect the assignment of roles.


You may need to set up data structures for the following example, and certain others, to work. Set up the following:

CONNECT system/manager
GRANT acct TO scott;

CONNECT joe/joe;
CREATE TABLE finance (empno NUMBER);
GRANT SELECT ON finance TO acct;
CONNECT scott/tiger

Suppose you have a role named ACCT that has been granted privileges allowing you to select from table FINANCE in the JOE schema. In this case, the following block fails:

    n NUMBER;

The block fails because the security check which verifies that you have the SELECT privilege on table JOE.FINANCE occurs at compile time. At compile time, however, the ACCT role is not yet enabled. The role is not enabled until the block is executed.

The DBMS_SQL package, by contrast, is not subject to this restriction. When you use this package, the security checks are performed at runtime. Thus, a call to SET_ROLE would affect the SQL executed using calls to the DBMS_SQL package. The following block is, therefore, successful:

   n NUMBER;
   EXECUTE IMMEDIATE 'select empno from' INTO n;
    --other calls to SYS.DBMS_SQL

See Also:

"Choosing Between Native Dynamic SQL and the DBMS_SQL Package" 

Protecting Database Objects Through Use of Schemas

A schema is a security domain that can contain database objects. The privileges granted to each user or role control access to these database objects. This section covers:

Unique Schemas

Most schemas can be thought of as usernames: the accounts which enable users to connect to a database and access the database objects. However, unique schemas do not allow connections to the database, but are used to contain a related set of objects. Schemas of this sort are created as normal users, yet are not granted the CREATE SESSION system privilege (either explicitly or through a role). However, you must temporarily grant the CREATE SESSION and RESOURCE privilege to such schemas, if you want to use the CREATE SCHEMA statement to create multiple tables and views in a single transaction.

For example, the schema objects for a specific application might be owned by a given schema. Application users can connect to the database using typical database usernames and use the application and the corresponding objects, if they have the privileges to do so. However, no user can connect to the database using the schema set up for the application. This configuration prevents access to the associated objects through the schema, and provides another layer of protection for schema objects. In this case, the application could issue an ALTER SESSION SET CURRENT_SCHEMA statement to connect the user to the correct application schema.

Shared Schemas

For many applications, users do not need their own accounts--or their own schemas--in a database. These users merely need to access an application schema. For example, users John, Firuzeh and Jane are all users of the Payroll application, and they need access to the Payroll schema on the Finance database. None of them need to create their own objects in the database; in fact, they need only access Payroll objects. To address this issue, Oracle Advanced Security provides enterprise users (schema-independent users).

Enterprise users, users managed in a directory service, can access a shared schema. They do not need to be created as database users; they are shared schema users of the database. Instead of creating a user account (that is, a user schema) in each database an enterprise user needs to access, as well as creating the user in the directory, an administrator can create an enterprise user once, in the directory, and point the user at a shared schema that many other enterprise users can also access.

In the previous example, if John, Firuzeh and Jane all access the Sales database as well as the Finance database, an administrator need only create a single schema in the Sales database, which all three users can access--instead of creating an account for each user on the Sales database. In this case, the DBA for the Sales database creates a shared schema called sales_application, as follows:


The mapping between enterprise users and a schema is done in the directory by means of one or more mapping objects. A mapping object maps the Distinguished Name (DN) of a user to a database schema that the user will access. This can be done in one of two ways:

When the database tries to determine the enterprise user's schema in the directory (that is, the schema to which the database will connect the user), it searches for a full DN mapping. If it does not find a full DN mapping, then it searches for a partial DN. A full DN mapping thus takes precedence over a partial one.

For users authenticated by SSL to the database, or whose X.509 certificate or DN from a certificate is proxied to the database, the database uses the DN to search for the user in the directory. For password-authenticated enterprise users, the DN is obtained from the directory. That is, when a username is presented to the database for authentication (for example, JANE), the database searches internally to find if there is a local user Jane. If not, the database searches the directory for Jane, and retrieves an associated DN for Jane. Afterwards, the database refers to a mapping object as above to determine the correct shared schema to which Jane connects.

If a set of privileges should be granted to a group of users, this can be done by granting roles and privileges to a shared schema. Every user sharing the schema gets these local roles and local privileges in addition to the enterprise roles.

Each enterprise user can be mapped to a shared schema on each database that the user needs to access. These schema-independent users thus need not have a dedicated database schema on each database. Shared schemas therefore lowers the cost of managing users in an enterprise.

See Also:

"Switching to a Different Schema"

Oracle Advanced Security Administrator's Guide  

Managing Object Privileges

As part of designing your application, you need to determine the types of users who will be working with the application, and the level of access they need to accomplish their designated tasks. You must categorize these users into role groups, and then determine the privileges that must be granted to each role. This section covers:

Object Privileges

End users are typically granted object privileges. An object privilege allows a user to perform a particular action on a specific table, view, sequence, procedure, function, or package. Table 11-1 summarizes the object privileges available for each type of object.

Table 11-1 Object Privileges
Object Privilege  Table  View  Sequence  Procedure (1) 












3 (2) 








3 (2) 





3 (3) 







Stand-alone stored procedures, functions, and public package constructs


Privilege that cannot be granted to a role


Can also be granted for snapshots

SQL Statements Permitted by Object Privileges

As you implement and test your application, you should create each necessary role. Test the usage scenario for each role to be certain that the users of your application will have proper access to the database. After completing your tests, coordinate with the administrator of the application to ensure that each user is assigned the proper roles.

Table 11-2 lists the SQL statements permitted by the object privileges shown in Table 11-1.

Table 11-2 SQL Statements Permitted by Database Object Privileges
Object Privilege  SQL Statements Permitted 


ALTER object (table or sequence)

CREATE TRIGGER ON object (tables only) 


DELETE FROM object (table or view) 


EXECUTE object (procedure or function)

References to public package variables 


CREATE INDEX ON object (table or view) 


INSERT INTO object (table or view) 


CREATE or ALTER TABLE statement defining a FOREIGN KEY integrity constraint on object (tables only) 


SELECT...FROM object (table, view, or snapshot) SQL statements using a sequence 

Creating a Role and Protecting Its Use

This section explains how to create a new role and protect its use. This section covers:

Creating and Implementing a New Role

To create a role, you must have the CREATE ROLE system privilege.

The name of a new role must be unique among existing usernames and role names of the database. Roles are not contained in the schema of any user.

Immediately after creation, a role has no privileges associated with it. To associate privileges with a new role, you must grant it privileges or other roles.

Managing Roles

You can create roles such that their use is authorized using information from the operating system, from a network authentication service, or from an LDAP-based directory. This enables role management to be centralized.

Central management of roles provides many benefits. If an employee leaves, for example, all of her roles and permissions can be changed in a single place.

Protecting Role Use

The use of a role can be protected by an associated password. For example:


A user who is granted a role protected by a password can enable or disable the role only by supplying the proper password for the role using a SET ROLE statement. If a role is created without any protection, then any grantee can enable or disable it.

Separate SET ROLE statements can be used to enable one database role, and disable all other roles of a user. This way, the user cannot use privileges (from a role) which were intended for use with another application. With ad hoc query tools such as SQL*Plus or Enterprise Manager, users can explicitly enable only the roles for which they are authorized.

A secure application role can incorporate additional logic to determine under what conditions the role is enabled. The conditions can reference any information available in the user session. This means information accessible through the USERENV application context namespace, such as the IP address from which the session connected, the method of authentication, and whether the user was proxied or not (that is, connected through a middle tier).


See Also:


Enabling and Disabling Roles

When a user has been granted a role, the role must be enabled before the privileges associated with it become available in the user's current session. Some, all, or none of the user's roles can be enabled or disabled. The following sections discuss when roles should be enabled and disabled, and the different ways in which a user can have roles enabled or disabled. Topics in this section include:

When to Enable Roles

In general, a user's security domain should permit him to perform the task at hand, yet limit him from having privileges which are not necessary for the current job. For example, a user should have all the privileges to work with the database application currently in use, but not have any privileges required for any other database applications. Having too many privileges might allow users to access information through unintended methods.

Privileges granted directly to a user are always available to him; therefore, directly granted privileges cannot be selectively enabled and disabled, depending on his current task. By contrast, privileges granted to a role can be selectively made available to any user granted the role. The enabling of roles never affects privileges explicitly granted to the user. The following sections explain how a user's roles can be selectively enabled (and disabled).

Default Roles

A default role is automatically enabled for a user when the user creates a session. A user's list of default roles should include those which correspond to his or her typical job function.

Each user has a list of zero, one, or more default roles. Any role directly granted to a user can potentially be a default role. An indirectly granted role (a role that is granted to a role) cannot be a default role.

The number of default roles for a user should not exceed the maximum number of enabled roles that are allowed per user (as specified by the initialization parameter MAX_ENABLED_ROLES). If the number of default roles for a particular user exceeds this maximum, then errors are returned when the user attempts a connection, and the connection is not allowed.


A default role is automatically enabled for a user when the user creates a session. Placing a role in a user's list of default roles bypasses authentication for the role, whether it is authorized using a password or through the operating system. 

A user's list of default roles can be set and altered using the SQL statement ALTER USER. If the user's list of default roles is specified as ALL, then every role granted to her is automatically added to her list of default roles. Only subsequent modification of the user's default role list can remove newly granted roles from her list of default roles.

Modifications to a user's default role list only apply to sessions created after the alteration or role grant; neither method applies to a session in progress at the time of the user alteration or role grant.

Explicitly Enabling Roles

Any user (or application) can use the SET ROLE statement to enable any granted roles, provided the grantee supplies role passwords, when necessary.

A SET ROLE statement enables all specified roles, provided that they have been granted to the user. All roles granted to the user that are not explicitly specified in a SET ROLE statement are disabled, including any roles previously enabled.

When you enable a role that contains other roles, all the indirectly granted roles are specifically enabled. Each indirectly granted role can be explicitly enabled or disabled for a user.

If a role is protected by a password, then the role can only be enabled by indicating its password in the SET ROLE statement. If the role is not protected by a password, then it can be enabled with a simple SET ROLE statement.

The following examples illustrate how roles can be enabled and disabled.

Assume that user Morris' security domain is as follows:

Morris' currently enabled roles can be changed from his default role, PAYROLL_CLERK, to ACCTS_PAY and ACCTS_REC, by the following statements:

SET ROLE accts_pay IDENTIFIED BY garfield;
SET ROLE accts_pay IDENTIFIED BY accts_rec;

Notice that in the first statement, multiple roles can be enabled in a single SET ROLE statement. The ALL and ALL EXCEPT options of the SET ROLE statement also allow several roles granted directly to the user to be enabled in one statement:

SET ROLE ALL EXCEPT Payroll_clerk;

This statement shows the use of the ALL EXCEPT option of the SET ROLE statement. Use this option when you want to enable most of a user's roles and only disable one or more. Similarly, all of Morris' roles can be enabled by the following statement:


When using the ALL or ALL EXCEPT options of the SET ROLE statement, all roles to be enabled either must not require a password, or must be authenticated using the operating system. If a role requires a password, then the SET ROLE ALL or ALL EXCEPT statement is rolled back and an error is returned. A user can also explicitly enable any indirectly granted roles granted to him or her through an explicit grant of another role. Morris can thus issue the following statement:

SET ROLE Payroll_report;

Enabling and Disabling Roles When OS_ROLES=TRUE

If OS_ROLES is set to TRUE, then any role granted by the operating system can be dynamically enabled using the SET ROLE statement. However, any role not identified in a user's operating system account cannot be specified in a SET ROLE statement. Such a role is ignored, even if a it has been granted using a GRANT statement.

When OS_ROLES is set to TRUE, a user can enable as many roles as are specified by the initialization parameter MAX_ENABLED_ROLES.

See Also:

Oracle9i Database Administrator's Guide for more information about use of the operating system for role authorization 

Dropping Roles

When you drop a role, the security domains of all users and roles granted that role are immediately changed to reflect the absence of the dropped role's privileges. All indirectly granted roles of the dropped role are also removed from affected security domains. Dropping a role automatically removes the role from all users' default role lists.

Because the creation of objects is not dependent upon the privileges received through a role, no cascading effects regarding objects need to be considered when dropping a role. For example, tables or other objects are not dropped when a role is dropped.

You can drop a role using the SQL statement DROP ROLE. For example:

DROP ROLE clerk;

To drop a role, you must have the DROP ANY ROLE system privilege or have been granted the role with the ADMIN OPTION.

Granting and Revoking System Privileges and Roles

The following sections explain how to grant and revoke system privileges and roles.

Granting System Privileges and Roles

System privileges and roles can be granted to other roles or users using the SQL command GRANT, as shown in the following example:


You may need to set up the following data structures for certain examples to work:

CONNECT sys/change_on_install AS sysdba;
CREATE ROLE Payroll_report;
GRANT CREATE TABLE, Accts_rec TO finance IDENTIFIED BY finance;
CONNECT scott/tiger
CREATE VIEW Salary AS SELECT Empno,Sal from Emp_tab;
GRANT CREATE SESSION, Accts_pay TO jward, finance;

Schema object privileges cannot be granted along with system privileges and roles in the same GRANT statement.

Granting System Privileges and Roles with the ADMIN OPTION

A system privilege or role can be granted with the ADMIN OPTION. A grantee with this option has several expanded capabilities:

A grantee without the ADMIN OPTION cannot perform the above operations. Note also that this option is not valid when granting a role to another role.

When a user creates a role, the role is automatically granted to the creator with the ADMIN OPTION.

Assume that you grant the NEW_DBA role to MICHAEL with the following statement:


Not only can the user MICHAEL use all of the privileges implicit in the NEW_DBA role, but he can grant, revoke, or drop the NEW_DBA role, as necessary.

Privileges Required to Grant System Privileges or Roles

To grant a system privilege or role, the grantor requires the ADMIN OPTION for all system privileges and roles being granted. Additionally, any user with the GRANT ANY ROLE system privilege can grant any role in a database.

Revoking System Privileges and Roles

System privileges and roles can be revoked using the SQL command REVOKE. For example:

REVOKE CREATE TABLE, Accts_rec FROM tsmith, finance;

The ADMIN OPTION for a system privilege or role cannot be selectively revoked. To do so, you must first revoke the privilege or role, and then grant it without the ADMIN OPTION.

Privileges Required to Revoke System Privileges and Roles

Any user with the ADMIN OPTION for a system privilege or role can revoke the privilege or role from any other database user or role. The user who revokes a privilege or role need not be the user who originally granted it. Additionally, any user with the GRANT ANY ROLE privilege can revoke any role.

Cascading Effects of Revoking System Privileges

There are no cascading effects when revoking a system privilege related to DDL operations, regardless of whether the privilege was granted with or without the ADMIN OPTION. For example, assume the following:

  1. You grant the CREATE TABLE system privilege to JWARD with the WITH ADMIN OPTION.

  2. JWARD creates a table.

  3. JWARD grants the CREATE TABLE system privilege to TSMITH.

  4. TSMITH creates a table.

  5. You revoke the CREATE TABLE privilege from JWARD.

  6. JWARD's table continues to exist. TSMITH continues to have the CREATE TABLE system privilege, and his table still exists.

Cascading effects can be observed when revoking a system privilege related to a DML operation. For example, if SELECT ANY TABLE is granted to a user, and if that user has created any procedures, then all procedures contained in the user's schema must be reauthorized before they can be used again (after the revoke).

Granting and Revoking Schema Object Privileges and Roles

You can grant schema object privileges to roles or users using the SQL command GRANT. The following statement grants the SELECT, INSERT, and DELETE object privileges for all columns of the EMP_TAB table to the users JWARD and TSMITH:

GRANT SELECT, INSERT, DELETE ON Emp_tab TO jward, tsmith;

To grant the INSERT object privilege for only the ENAME and JOB columns of the EMP_TAB table to the users JWARD and TSMITH, enter the following statement:

GRANT INSERT(Ename, Job) ON Emp_tab TO jward, tsmith;

To grant all schema object privileges on the SALARY view to the user WALLEN, use the ALL shortcut. For example:

GRANT ALL ON Salary TO wallen;

System privileges and roles cannot be granted along with schema object privileges in the same GRANT statement.

The following section explains granting and revoking schema object privileges. It includes:

Granting and Revoking Schema Object Privileges with the GRANT OPTION

A schema object privilege can be granted to a user with the GRANT OPTION. This special privilege allows the grantee several expanded privileges:

The user whose schema contains an object is automatically granted all associated schema object privileges with the GRANT OPTION.


The GRANT OPTION is not valid when granting a schema object privilege to a role. Oracle prevents the propagation of schema object privileges through roles, so that grantees of a role cannot propagate object privileges received through roles. 

Privileges Required to Grant Schema Object Privileges

To grant a schema object privilege, the grantor must either

Revoking Schema Object Privileges

Schema object privileges can be revoked using the SQL command REVOKE. For example, the original grantor can revoke the SELECT and INSERT privileges on the EMP_TAB table from the users JWARD and TSMITH by entering:

REVOKE SELECT, INSERT ON Emp_tab FROM jward, tsmith;

For the table DEPT_TAB, a grantor could also revoke all privileges that he or she granted to the role HUMAN_RESOURCES by entering the following statement:

REVOKE ALL ON Dept_tab FROM human_resources;

The statement is valid even if only one privilege was granted. Note that this statement would only revoke the privileges that the grantor authorized, not the grants made by other users. The GRANT OPTION for a schema object privilege cannot be selectively revoked; the schema object privilege must be revoked and then regranted without the GRANT OPTION. A user cannot revoke schema object privileges from himself.

Revoking Column-Selective Schema Object Privileges

Recall that column-specific INSERT, UPDATE, and REFERENCES privileges can be granted for tables or views. However, it is not possible to revoke column-specific privileges selectively with a similar REVOKE statement. Instead, the grantor must first revoke the schema object privilege for all columns of a table or view, and then selectively grant the new column-specific privileges again.

For example, assume the role HUMAN_RESOURCES has been granted the UPDATE privilege on the DEPTNO and DNAME columns of the table DEPT_TAB. To revoke the UPDATE privilege on just the DEPTNO column, enter the following two statements:

REVOKE UPDATE ON Dept_tab FROM human_resources;
GRANT UPDATE (Dname) ON Dept_tab TO human_resources;

The REVOKE statement revokes the UPDATE privilege on all columns of the DEPT_TAB table from the role HUMAN_RESOURCES. The GRANT statement regrants the UPDATE privilege on the DNAME column to the role HUMAN_RESOURCES.

Revoking the REFERENCES Schema Object Privilege

If the grantee of the REFERENCES object privilege has used the privilege to create a foreign key constraint (that currently exists), then the grantor can only revoke the privilege by specifying the CASCADE CONSTRAINTS option in the REVOKE statement:


When the CASCADE CONSTRAINTS option is specified, any foreign key constraints currently defined that use the revoked REFERENCES privilege are dropped.

Privileges Required to Revoke Schema Object Privileges

To revoke a schema object privilege, the revoker must be the original grantor of the object privilege being revoked.

Cascading Effects of Revoking Schema Object Privileges

Revoking a schema object privilege can have several cascading effects that should be investigated before a REVOKE statement is issued:

How Grants Affect Dependent Objects

Issuing a GRANT statement against a schema object causes the "last DDL time" attribute of the object to change. This can invalidate any dependent schema objects, in particular PL/SQL package bodies that refer to the schema object. These then must be recompiled.

Granting to, and Revoking from, the User Group PUBLIC

Privileges and roles can be granted to and revoked from the user group PUBLIC. Because PUBLIC is accessible to every database user, all privileges and roles granted to PUBLIC are accessible to every database user.

You should only grant a privilege or role to PUBLIC if every database user requires the privilege or role. This recommendation restates the general rule that at any given time, each database user should only have the privileges required to successfully accomplish the current task.


Certain privileges and roles granted to PUBLIC by default may not be needed in your situation. Oracle Corporation highly recommends that system administrators review grants to PUBLIC and revoke privileges that are not absolutely necessary. 

This section explains granting and revoking from the user group PUBLIC. It includes:

Revoking Security-Vulnerable Packages from PUBLIC

All unnecessary privileges, grants, and roles should be revoked from PUBLIC. Any database user can exercise privileges that are granted to PUBLIC. Such privileges include EXECUTE on various PL/SQL packages. These packages may allow minimally privileged users to access and execute packages that they may not have permit to access directly. The more potentially security vulnerable packages include:


Allows arbitrary users to send and receive e-mail messages. Granting this package to PUBLIC may permit unauthorized exchange of e-mail messages.


Allows the database server to establish outgoing network connections any receiving network service. Thus, the database server and any waiting network service exchange data.


Allows a database to request and retrieve data through HTTP. Granting this package to PUBLIC may permit data to be sent through HTML forms to a malicious web site.


Allows text level access to any file on the host operating system, if configure improperly. Even when properly configured, this package does not distinguish between its calling applications may write arbitrary data into the same location that is written by another application.


Allows one to encrypt stored data. Most users should not have the privilege to encrypt data. Encrypted data may be non-recoverable if the keys are not securely generated, stored, and managed. Oracle recommends that EXECUTE on this package be granted to a role, and the role granted to those users who need to encrypt data.

These packages are useful to the applications that need them and warrant proper configuration and usage, but they may not be suitable or required for other applications. If necessary, revoke the package from PUBLIC and database users.

Cascading Effects of Revokes from PUBLIC

Revokes from PUBLIC can cause significant cascading effects, depending on the privilege that is revoked. If any privilege related to a DML operation is revoked from PUBLIC (for example, SELECT ANY TABLE, UPDATE ON EMP_TAB), then all procedures in the database (including functions and packages) must be reauthorized before they can be used again. Therefore, use caution when granting DML-related privileges to PUBLIC.

When Grants and Revokes Take Effect

Depending upon what is granted or revoked, a grant or revoke takes effect at different times:

See Also:

"Listing Privilege and Role Information" in the Oracle9i Database Administrator's Guide  

Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index