Oracle8i Application Developer's Guide - Fundamentals
Release 2 (8.1.6)

Part Number A76939-01





Go to previous page Go to next page

Establishing Security Policies

This chapter provides guidance on developing security policies. It includes:

Introduction to Security Policies

This section provides an introduction to discretionary 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 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, a 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.

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

What Discretionary Security Policies Can Cover

In addition to requirements unique to your environment, you should design and implement discretionary security policies to determine the following technical issues:

Features to Use in Establishing Security Policies

This chapter discusses several elements of Oracle8i which you can use in establishing security policies:

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 actually using the application. 

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, the set of books she is authorized to access, and her position in the management hierarchy. You can then retrieve that information later in the session. 

Fine-Grained Access Control 

Use this feature to implement security policies at a low 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. 

Authentication and Auditing Through a Middle Tier 

Use this feature to allow each user to be authenticated by a database password, without the overhead of a separate database connection. You can also use this feature to identify the real user who is accessing the database via a middle tier. 

Data Encryption 

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

Application Security

Draft security policies for each database application. For example, each database application should have one or more application roles that provide different levels of security when executing the application. The application 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:

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

Examples of features whose use is compromised by the One Big Application User model include:

Does Security Enforcement Occur in the Application, or in the Database?

Oracle recommends that, to the extent possible, applications utilize the security enforcement mechanisms of the database. When security is enforced in the database itself rather than the application, it cannot be bypassed by using another application to access data.

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.

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.

If applications enforce their own security mechanisms, it means that each application that accesses data must reimplement security. For example, if an organization implements a new report-writing tool, then the organization must implement security to ensure that users do not get more data access through the report-writing tool than they 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.

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 the security measures in the application.

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:

Overview of Roles and Application Privilege Management

Because most database applications involve many different privileges on many 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. To simplify application privilege management, 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 fewer or more 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. 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).

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

Associating Privileges with the User's Current Application Role

A single user can use many applications and associated roles. However, you should allow a user to have only the privileges associated with the currently running application role. For example, 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.

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 gets all privileges from roles, then the user cannot combine these privileges to perform unauthorized operations.

See Also:

"Enabling and Disabling Roles" 

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 instead of 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 appropriate privileges (that is, that the user has been granted the role that is being set).

Note: If you use DBMS_SESSION.SET_ROLE within an invoker's rights procedure, the role remains in effect until you specifically disable it. In keeping with the principle of "least privilege," (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 does 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.



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:

"Native Dynamic SQL vs. the DBMS_SQL Package"

Restricting Application Roles from Tool Users

Prebuilt database applications explicitly control the potential actions of a user, including the enabling and disabling of the user's roles while using the application. By contrast, ad hoc query tools, such as SQL*Plus, allow a user to submit any SQL statement (which may or may not succeed), including the enabling and disabling of any granted role. This can pose a serious security problem.

This section describes:

A Potential Problem

A user of an application could exercise the privileges attached to that application to issue destructive SQL statements against database tables by using an ad hoc tool.

For example, consider the following scenario:

Now, consider a user who has been granted the VACATION role. Suppose, instead of using the Vacation application, the user executes SQL*Plus. At this point, the user is restricted only by the privileges granted to him explicitly or via roles, including the VACATION role. Because SQL*Plus is an ad hoc query tool, the user is not restricted to a set of predefined actions, as with designed database applications. The user can query or modify data in the EMP_TAB table as he or she chooses.

Limiting Roles Through PRODUCT_USER_PROFILE

Oracle8i offers some capability to limit what roles a user accesses through an application, via the PRODUCT_USER_PROFILE table.

DBAs can use PRODUCT_USER_PROFILE to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis. SQL*Plus--not Oracle--enforces this security. DBAs can even restrict access to the GRANT, REVOKE, and SET ROLE commands in order to control users' ability to change their database privileges.

The PRODUCT_USER_PROFILE table enables you to list roles which you do not want users to activate with an application. You can also explicitly disable use of various commands, such as SET ROLE. For example, you could create an entry in the PRODUCT_USER_PROFILE table to:

Suppose user Jane connects to the database using SQL*Plus. Jane has the CLERK, MANAGER, and ANALYST roles. As a result of the above entry in PRODUCT_USER_PROFILE, Jane is only able to exercise her ANALYST role with SQL*Plus. Also, when Jane attempts to issue a SET ROLE statement, she is explicitly prevented from doing so because of the entry in the PRODUCT_USER_PROFILE table prohibiting use of SET ROLE.

Use of the PRODUCT_USER_PROFILE table does not completely guarantee security, for multiple reasons. In the above example, while SET ROLE is disallowed with SQL*Plus, if Jane had other privileges granted to her directly, she could exercise these using SQL*Plus.

See Also:

SQL*Plus User's Guide and Reference for more information about the PRODUCT_USER_PROFILE table 

Virtual Private Database for Greatest Security

Oracle8i allows user to enforce security--to a fine level of granularity--directly on tables or views using virtual private database: the combination of server-enforced fine-grained access control and application context. Because security policies are attached directly to tables or views and automatically applied whenever a user access data, there is no way to bypass security.

Strong security policies, centrally managed and applied directly to data, enable security to be enforced no matter how a user gets to the data, whether through an application, by a query, or by using a report-writing tool.

With virtual private database, a user directly or indirectly accessing a table or view having a security policy associated with it, causes the server to dynamically modify the statement based on a WHERE condition (known as a predicate) returned by a function which implements the security policy. The user's SQL statement is modified dynamically, transparently to the user, using any condition which can be expressed in, or returned by a function.

Functions which return predicates can also include callouts to other functions. You could embed a C or Java callout within your PL/SQL package that could either access operating system information or return WHERE clauses from an operating system file or central policy store. A policy function can return different predicates for each user, each group of users, or each application.

Application context enables you to securely access attributes on which you base your security policies. For example, users with the attribute of manager would have a different security policy than users with the attribute of employee.

Consider an HR clerk who is only allowed to see employee records in the Aircraft Division. When the user initiates the query


the function implementing the security policy returns the predicate division = `AIRCRAFT', and the database transparently rewrites the query. The query actually executed becomes:

SELECT * FROM emp WHERE division = `AIRCRAFT';

The security policy is applied within the database itself, rather than an application, which means that use of a different application will not bypass the security policy. Also, security can be built once, in the database, instead of being reimplemented in multiple applications. Virtual private database therefore provides far stronger security than application-based security, at a lower cost of ownership.

See Also:

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

Recommended Application Design Practices to Reduce Risk

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

Enable and Disable Roles Promptly

Enable the proper role when the application starts, and disable it when the application terminates

  1. Give each application distinct roles:

    One role should contain all privileges necessary to use the application successfully. Depending on the situation, there might be several roles that contain more or fewer privileges to provide tighter or less restrictive security while executing the application. Each application role should be protected by a password (or by operating system authentication) to prevent unauthorized use.

    Another role should contain only non-destructive privileges associated with the application (SELECT privileges for specific tables or views associated with the application). The read-only role allows the application user to generate custom reports using ad hoc tools, such as SQL*Plus. However, this role does not allow the application user to modify table data outside the application itself. A role designed for an ad hoc query tool may or may not be protected by a password (or by operating system authentication).

  2. At startup, each application should use the SET ROLE statement to enable one of the application roles associated with that application. If a password is used to authorize the role, then the password must be included in the SET ROLE statement within the application (encrypted by the application, if possible). If the role is authorized by the operating system, then the system administrator must have set up user accounts and applications so that application users get the appropriate operating system privileges when using the application.

  3. At termination, each application should disable the previously enabled application role.

  4. Application users should be granted application roles, as required.

Note: 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. See "Ways to Use Application Context with Fine Grained Access Control".

Additionally, you can:

  1. Specify the roles to enable when a user starts SQL*Plus, using the PRODUCT_USER_PROFILE table. This functionality is similar to that of a precompiler or Oracle Call Interface (OCI) application that issues a SET ROLE statement to enable specific roles upon application startup.

  2. Disable the use of the SET ROLE statement for SQL*Plus users with the PRODUCT_USER_PROFILE table. This allows a SQL*Plus user only the privileges associated with the roles enabled when the user started SQL*Plus.

Other ad hoc query and reporting tools can also make use of the PRODUCT_USER_PROFILE table to restrict the roles and commands that each user can use while running that product.

See Also:

The appropriate tool manual, such as the SQL*Plus User's Guide and Reference  

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: Human Resources Application #2" 

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, 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. It still has the following vulnerabilities:

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" 

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 via 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 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 via the schema, and provides another layer of protection for schema objects. In this case, the application could issue an ALTER SESSION SET SCHEMA statement to connect the user to the correct application schema.

Schema-independent Users

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 and 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 schema-independent 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, contained in the user's X.509 certificate, 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 first looks for a full DN mapping. If it does not find a full DN mapping, then it looks for a partial one. A full DN mapping thus takes precedence over a partial one.

If a set of privileges need to 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 she needs to access; these schema-independent users thus need not have a dedicated database schema on each database. User-schema separation therefore lowers the cost of managing users in an enterprise.

See Also:

"Renaming the Schema" in Chapter 2, "Managing Schema Objects"

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.

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

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

As you implement and test your application, you should create each of these roles. 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 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.

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 or from a network authentication service. 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 statments can be used to enable one application role, and disable all other roles of a user. This way, the user cannot use privileges (from a role) intended for 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.

See Also:

"Explicitly Enabling Roles" and "Restricting Application Roles from Tool Users"

Oracle8i Administrator's GuideFor information about network authentication services, see Oracle Advanced Security Administrator's Guide

Enabling and Disabling Roles

Although a user can be 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.

When to Enable Roles

In general, a user's security domain should permit the user to perform the task at hand, yet limit the user 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 the user; therefore, directly granted privileges cannot be selectively enabled and disabled, depending on a user's current task. By contrast, privileges granted to a role can be selectively made available for any user granted the role. The enabling of roles never affects privileges explicitly granted to a 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 of the user. 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 user exceeds this maximum, then errors are returned when the user attempts a connection, and the user's 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 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 a user is automatically added to the user's list of default roles. Only subsequent modification of a user's default role list can remove newly granted roles from a user's 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 the role's password in the SET ROLE statement. If the role is not protected by a password, then the role can be enabled with a simple SET ROLE statement. For example, assume that 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:


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


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:


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

CREATE ROLE Payroll_clerk;
CREATE ROLE Payroll_report;

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 via an explicit grant of another role. For example, Morris can 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 (it is ignored), even if a role has been granted using a GRANT statement.

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

See Also:

Oracle8i 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 via 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).

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. (This option is not valid when granting a role to another role.) A grantee with this option has several expanded capabilities:

A grantee without the ADMIN OPTION cannot perform the above operations.

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:


The user MICHAEL cannot only use all of the privileges implicit in the NEW_DBA role, but 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; the privilege or role must be revoked, and then the privilege or role is regranted 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 does not have to be the user that originally granted the privilege or role). Additionally, any user with the GRANT ANY ROLE 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

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 short cut. 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.

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 via roles, so that grantees of a role cannot propagate object privileges received via 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, assuming you are the original grantor, to revoke the SELECT and INSERT privileges on the EMP_TAB table from the users JWARD and TSMITH, enter the following statement:

REVOKE SELECT, INSERT ON Emp_tab FROM jward, tsmith;

A grantor could also revoke all privileges on the table DEPT_TAB (even if only one privilege was granted) that he or she granted to the role HUMAN_RESOURCES by entering the following statement:

REVOKE ALL ON Dept_tab FROM human_resources;

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 him or herself.

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:


Any foreign key constraints currently defined that use the revoked REFERENCES privilege are dropped when the CASCADE CONSTRAINTS option is specified.

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 types of 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 also 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.

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:

Fine-Grained Access Control

Fine-grained access control allows you to build applications that enforce security policies at a low level of granularity. You can use it, for example, to restrict a customer who is accessing an Oracle server to see only his own account, a physician to see only the records of her own patients, or a manager to see only the records of employees who work for him.

When you use fine-grained access control, you create security policy functions attached to the table or view on which you have based your application. Then, when a user enters a DML statement (SELECT, INSERT, UPDATE, or DELETE) on that object, Oracle dynamically modifies the user's statement--transparently to the user--so that the statement implements the correct access control.

This section covers:

Features of Fine-Grained Access Control

Fine-grained access control provides the following capabilities:

Table- Or View-based Security Policies

Attaching security policies to tables or views, rather than to applications, provides greater security, simplicity, and flexibility.


Attaching a policy to a table or view overcomes a potentially serious application security problem. Suppose a user is authorized to use an application, and then, drawing on the privileges associated with that application, wrongfully modifies the database by using an ad hoc query tool, such as SQL*Plus. By attaching security policies to tables or views, fine-grained access control ensures that the same security is in force, no matter how a user accesses the data. 


Adding the security policy to the table or view means that you make the addition only once, rather than repeatedly adding it to each of your table- or view-based applications. 


You can have one security policy for SELECT statements, another for INSERT statements, and still others for UPDATE and DELETE statements. For example, you might want to enable a Human Resources clerk to SELECT all employee records in her division, but to UPDATE only salaries for those employees in her division whose last names begin with "A" through "F." 

Multiple Policies for Each Table or View

You can establish several policies for the same table or view. For example, suppose you have a base application for Order Entry, and each division of your company has its own special rules for data access. You can add a division-specific policy function to a table without having to rewrite the policy function of the base application.

Note that all policies applied to a table are enforced with AND syntax. Thus, if you have three policies applied to the CUSTOMERS table, each of these policies is applied to any access of the table. You cannot partition the policies by application.

High Performance

With fine-grained access control, each policy function for a given query is evaluated only once, at statement parse time. Also, the entire dynamically modified query is optimized and the parsed statement can be shared and reused. This means that rewritten queries can take advantage of the high performance features of Oracle such as dictionary caching and shared cursors.


For performance reasons, parsed static SQL statements in instantiated PL/SQL packages may not get re-parsed. If you want to force the re-evaluation of the policy function, Oracle recommends that you use dynamic SQL. For more information, see "Dynamic SQL Recommended for Use with SYS_CONTEXT"


How to Add a Policy to a Table or View

The DBMS_RLS package enables you to administer security policies. This package includes four procedures:

Table 11-3 DBMS_RLS Procedures
Procedure  Purpose 


Use this procedure to add a policy to a table or view. 


Use this procedure to drop a policy from a table or view. 


Use this procedure to force a reparse of open cursors associated with a policy, so that a new policy or change to a policy can take effect immediately. 


To enable (or disable) a policy you have previously added to a table or view. 

These procedures allow you to specify the table or view to which you are adding a policy, the name of the policy, the function which implements the policy, the type of statement to which the policy applies (that is, SELECT, INSERT, UPDATE, or DELETE), and additional information.

For example, the ADD_POLICY procedure includes an UPDATE_CHECK parameter to check the policy before and after insert (or update). If you cannot see it after update or insert, then the update or insert is not allowed. Oracle recommends that you use the UPDATE_CHECK parameter to spare users the frustration of being able to insert records which they cannot later select.

See Also:

Oracle8i Supplied PL/SQL Packages Reference  

Example of a Dynamically Modified Statement

Suppose you want to attach to the ORDERS_TAB table the following security policy: "Customers can see only their own orders." The process would be as follows.

  1. You create a function to add a predicate to a user's DML statement.


    A predicate is the WHERE clause and, more explicitly, a selection criteria clause based on one of the operators (=, !=, IS, IS NOT, >, >=). 

    In this case, you might create a function that adds the following predicate:

     Cust_no = (SELECT Custno FROM Customers WHERE Custname = 
                SYS_CONTEXT ('userenv','session_user')) 
  2. A user enters the statement:

    SELECT * FROM Orders_tab
  3. The Oracle server calls the function you created to implement the security policy.

  4. The function dynamically modifies the user's statement to read:

    SELECT * FROM Orders_tab WHERE Custno = (
       SELECT Custno FROM Customers 
           WHERE Custname = SYS_CONTEXT('userenv', 'session_user'))
  5. The Oracle server executes the dynamically modified statement.

  6. Upon execution, the function uses the username returned by SYS_CONTEXT ('userenv','session_user') to look up the corresponding customer and to limit the data returned from the ORDERS_TAB table to that customer's data only.

    See Also:

    For more information on using fine-grained access control, see "Examples" and Oracle8i Supplied PL/SQL Packages Reference. 

Application Context

Application context allows you to write applications which draw upon certain aspects of a user's session information. This is especially useful in developing secure applications based on a user's access privileges. It provides a way to define, set, and access attributes that an application can use to enforce access control--specifically, fine-grained access control.

Most applications contain information about the basis on which access is to be limited. In an order entry application, for example, customers would be limited to access their own orders (ORDER_NUMBER) and customer number (CUSTOMER_NUMBER). These can be used as security attributes.

Consider a user running the Oracle Human Resource application. Part of the application's initialization process is to determine the kind of responsibility that the user can assume, based on the user's identity. This responsibility ID becomes part of the Oracle Human Resource application context; it will affect what data the user can access throughout the session.

This section explains the use of application context. It includes:

Features of Application Context

Application context provides important security features:

Security Tailored to the Attributes You Specify for Each Application

Each application can have its own context with its own attributes. For example, suppose you have three applications: General Ledger, Order Entry, and Human Resources. You can specify different attributes for each application. Thus,

In each case, you can adapt the application context to your precise security needs.

Security Through Validation

Suppose you have a General Ledger application, which has access control based on set of books. If a user accessing this application changes the set of books he is working on from 01 to 02, the application context can ensure that:

The validation function can check application metadata tables to make this determination and ensure that the attributes in combination are in line with the overall security policy. To restrict a user from changing a context attribute without the above security validation, Oracle verifies that only the designated package which implements the context, changes the attribute.

USERENV Application Context Namespace for Access to Predefined Attributes

Oracle8i provides a built-in application context namespace, USERENV, which provides access to predefined attributes (session primitives--information which the database captures regarding a user's session). For example, the IP address from which a user connected, the username, and a proxy username (in cases where a user connection is proxied through a middle tier), are all available as predefined attributes through the USERENV application context.

Predefined attributes can be very useful for access control. For example, if you are using a three-tier application which creates lightweight user sessions through OCI, you can access the PROXY_USER attribute in the USERENV application context to determine whether the user's session was created by a middle tier application. Your policy function could allow a user to access data only for connections where the user is proxied. If not (that is, in cases where the user is connecting directly to the database), the user would not be able to access any data.

Predefined attributes can be accessed through the USERENV application context, but cannot not be changed. They are listed in Table 11-4; see SYS_CONTEXT in the Oracle8i SQL Reference for complete details about the USERENV namespace and its predefined attributes.

Use the following syntax to return information about the current session.

SYS_CONTEXT('userenv', 'attribute') 

Note: The USERENV application context namespace is intended to replace the USERENV function provided in earlier database releases.

Table 11-4 Key to Predefined Attributes in USERENV Namespace  
Predefined Attribute  Meaning 


Returns the operating system identifier for the client of the current session. "Virtual" in TCP/IP. 


Returns the language and territory currently used by the session, along with the database character set in the form: language_territory.characterset


Returns abbreviation for the language name. 


Returns auditing session identifier. 


Returns the instance identification number of the current instance. 


Returns available auditing entry identifier. 


Returns TRUE if you currently have the DBA role enabled and FALSE if you do not.  


Returns up to 64 bytes of user session information that can be stored by an application using the DBMS_APPLICATION_INFO package. 


Returns the territory of the current session. 


Returns the currency symbol of the current session. 


Returns the NLS calendar used for dates in the current session. 


Returns the current date format of the current session. 


Returns the language used for expressing dates in the current session. 


Indicates whether the sort base is binary or linguistic. 


Returns the name of the user whose privilege the current session is under. Can be different from SESSION_USER from within a stored procedure (such as an invoker-rights procedure). 


Returns the user ID of the user whose privilege the current session is under. Can can be different from SESSION_USERID from within a stored procedure (such as an invoker-rights procedure). 


Returns the database user name by which the current user is authenticated. 


Returns the identifier of the database user name by which the current user is authenticated. 


Returns the name of the default schema being used in the current session. This can be changed with an ALTER SESSION SET SCHEMA statement. 


Returns the identifier of the default schema being used in the current session. This can be changed with an ALTER SESSION SET SCHEMAID statement. 


Returns the name of the database user (typically middle tier) who opened the current session on behalf of SESSION_USER


Returns the identifier of the database user (typically middle tier) who opened the current session on behalf of SESSION_USER


Returns the domain of the database as specified in the DB_DOMAIN initialization parameter. 


Returns the name of the database as specified in the DB_NAME initialization parameter. 


Returns the name for the hose machine on which the database is running. 


Returns the operating system username of the client process that initiated the database session. 


Returns the external name of the database user. 


Returns the IP address of the machine from which the client is connected. 


Returns the protocol named in the connect string (PROTOCOL=protocol). 


Returns the background job ID. 


Returns the foreground job ID. 


Shows how the user was authenticated (DATABASE, OS, NETWORK, PROXY). 


Returns the data being used to authenticate the login user. (Returns the certificate content, if one exists.) 

Feature Design Principles for Application Context

Fine-grained access control with application context was designed to handle static applications; that is, those for which the security attributes contained within application contexts are static within the user sessions. In other words, the feature is best used for applications in which the user logs in, his application context is set for the session, and the context does not change until the user logs off. This design principle enables application context to be highly scalable, because many users can share the same fully-parsed, optimized statement.

If an application needs to change a context attribute within a session, then you may need to programmatically ensure that new application context attributes are reflected in the cursor which the user executes. For example, suppose an application wishes to change a user's position attribute within the Human Resources context to allow the user to view more data within her session. The application may need to force a cursor reparse to ensure that the new position attribute is used to limit access to data.

There are at least three ways to force a cursor reparse:

Refresh the Policy

The administrative interface to fine-grained access control is the DBMS_RLS package. You can use the DBMS_RLS.REFRESH_POLICY procedure to refresh the security policy. This will force a commit of the current transaction. A drawback of this approach is that it will flush all cursors associated with the specified table or view, including cursors of users whose application context has not changed. This approach will achieve the desired affect, but it may degrade performance.

See Also:

Oracle8i Supplied PL/SQL Packages Reference  

Force Explicit Reparse

Force an explicit reparse of the cursor. For applications using the Oracle Call Interface, you can explicitly reparse an open cursor by issuing a REPARSE statement. This forces a SQL statement to be reparsed, which then picks up any change in a users' application context and applies it to the (newly-parsed) statement. PL/SQL, however, does not allow an explicit reparse of a statement, so this approach will not work for PL/SQL statements.

Use Dynamic SQL

For PL/SQL statements, you can use dynamic SQL to create a package associated with a unique context for your application. This approach is described in the following section.

Ways to Use Application Context with Fine Grained Access Control

To make the implementation of a security policy easier, you have the option of using application context within a fine-grained access control function. Virtual private database (VPD) is the term used for a combination of fine-grained access control with application context.

Application context can be used in very specific ways with fine-grained access control:

Using Application Context as a Secure Data Cache

Accessing an application context inside your fine-grained access control policy function is like writing down an often-used phone number and posting it next to your phone, where you can find it easily, rather than looking it up every time you need it.

For example, suppose you base access to the ORDERS_TAB table on customer number. Rather than querying the customer number for a logged-in user each time you need it, you could store it in the application context. This way, the customer number is available when you need it.

Application context is especially helpful if your security policy is based on multiple security attributes. For example, a policy function which bases a predicate on four attributes (such as employee number, cost center, position, spending limit), would have to execute multiple subqueries to retrieve this information. If all of this data is already available through application context, then performance will be much faster.

Using Application Context to Return a Specific Predicate (Security Policy)

You can use application context to return the correct predicate--that is, the correct security policy.

An order entry application enforces the policy "customers only see their own orders and clerks see all orders for all customers." Here you have two different policies. You could define an application context with a position attribute, and this attribute could be accessed within the policy function to return the correct predicate, depending on the value of the attribute. Thus, you can enable a user in the Clerk position to retrieve all orders, but a user in the Customer position to see his own records only.

To design a fine-grained access control policy to return a specific predicate for an attribute, access the application context within the function that implements the policy. For example, to limit customers to seeing their own records only, use fine-grained access control to dynamically modify the user's query from this:

SELECT * FROM Orders_tab

to this:

SELECT * FROM Orders_tab 
   WHERE Custno = SYS_CONTEXT ('order_entry', 'cust_num');

Using Application Context to Provide Attributes as Bind Variables in a Predicate

Continuing with the example above, suppose you have 50,000 customers, and you do not want to have a different predicate returned for each customer. Customers all share the same policy (that is, they can only see their own orders); it is merely their customer numbers which are different.

Using application context, you can return one predicate within a policy function which applies to 50,000 customers. As a result, there is one shared cursor which nonetheless executes differently for each customer, because the bind variable (the customer number) is evaluated at execution time. This variable is, of course, different for every customer. Use of application context in this case provides optimum performance, as well as fine-grained security.

How to Use Application Context

To use application context, you perform the following tasks:

Task 1: Create a PL/SQL Package that Sets the Context for Your Application

Begin by creating a PL/SQL package with functions that set the context for your application. This section presents an example, followed by a discussion of SQL_CONTEXT syntax and behavior.


A login trigger is highly recommended because the user's context (information such as EMPNO, GROUP, MANAGER) should be set before the user accesses any data. 


The following example creates the package app_security_context.

   PROCEDURE Set_empno;

   PROCEDURE Set_empno
   Emp_id NUMBER;
    SELECT Empno INTO Emp_id FROM Emp_tab 
    DBMS_SESSION.SET_CONTEXT('app_context', 'empno', Emp_id);

See Also:

Oracle8i Supplied PL/SQL Packages Reference 


The syntax for this function is:

SYS_CONTEXT ('namespace', 'attribute', [length])

It returns the value of attribute as defined in the package currently associated with the context namespace. It is evaluated once for each statement execution, and is treated like a constant during type checking for optimization. You can use the pre-defined namespace USERENV to access primitive contexts such as userid and NLS parameters.

See Also:

"USERENV Application Context Namespace for Access to Predefined Attributes" on . Also see Oracle8i SQL Reference for details about attributes. 

Dynamic SQL Recommended for Use with SYS_CONTEXT

During a session in which you expect a change in policy between executions of a given query, that query must use dynamic SQL.

This is because static SQL and dynamic SQL parse statements differently. With static SQL, statements are parsed at compile time; for performance reasons, they are not reparsed at execution. With dynamic SQL, by contrast, statements are parsed every time they are executed.

Consider a situation in which policy A is in force when you compile a SQL statement--and then you switch to policy B and execute the statement. With static SQL, policy A remains in force: the statement is parsed at compile time and not reparsed upon execution. With dynamic SQL, however, the statement is parsed upon execution, and so the switch to policy B is carried through.

For example, consider the following policy:

EMPLOYEE_NAME = SYS_CONTEXT ('userenv', 'session_user')

The policy "Employee name matches database user name" is represented in the form of a SQL predicate: the predicate is basically a policy. If the predicate changes, the statement must be reparsed in order to produce the correct result.

Parallel Query Not Recommended for Use with SYS_CONTEXT

If SYS_CONTEXT is used inside a SQL function which is embedded in a parallel query, the function will not be able to pick up the application context. The application context exists only in the user session.

Consider, for example, a user-defined function within a SQL statement, which sets the user's ID to 5:

     IF SYS_CONTEXT ('hr', 'id') = 5

Now consider the statement:

SELECT * FROM EMP WHERE proc1( ) = 1;

If this statement is run as a single query (that is, if one process is used to run the entire query), there will be no problem.

However, if this statement is run as a parallel query, the parallel execution servers (query slave processes) do not have access to the user session which contains the application context information. The query will not produce the expected results.

By contrast, if you use the SYS_CONTEXT function within a query, there is no problem. For example,


In this way, it works like a bind variable: the query coordinator can access the application context information and pass it on to the parallel execution servers.

Versioning in Application Context

When you execute a statement, Oracle8i takes a snapshot of the entire application context being set up by SYS_CONTEXT. Within the duration of a query, the context remains the same for all fetches of the query.

If you (or a function) attempt to change the context within a query, the change will not take effect in the current query. In this way, SYS_CONTEXT enables you to store variables in a session.

Task 2: Create a Unique Context and Associate It with the PL/SQL Package

To perform this task, you use the CREATE CONTEXT statement. Each context must have a unique attribute and belong to a namespace. That is, context names must be unique within the database, not just within a schema. Contexts are always owned by the schema SYS.

For example:

CREATE CONTEXT order_entry USING oe_context;

where order_entry is the context namespace, and oe_context is the trusted package that can set attributes in the context namespace.

After you have created the context, you can set or reset the context attributes by using the DBMS_SESSION.SET_CONTEXT package. The values of the attributes you set remain either until you reset them or until the user ends the session.

You can set the context attributes inside only the trusted procedure you named in the CREATE CONTEXT statement. This prevents a malicious user from changing context attributes without proper attribute validation.

Task 3: Set the Context Before the User Retrieves Data

Always use an event trigger on login to pull session information into the context. This sets the user's security-limiting attributes for the database to evaluate, and thus enables it to make the appropriate security decisions.

Other considerations come into play if you have a changing set of books, or if positions change constantly. In these cases, the new attribute values may not be picked up right away, and you must force a cursor reparse to pick it up.

See Also:

"Feature Design Principles for Application Context"


Task 4. Use the Context in a Policy Function

Now that you have set up the context and the PL/SQL package, you can go ahead and have your policy functions use the application context to make policy decisions based on different context values.


This section provides three examples, each using application context within a fine-grained access control function.

Example 1: Order Entry Application

This simple example uses application context to implement the policy, "Customers can see their own orders only." This example guides you through the following steps in building the application:

The procedure in this example:

You can later refer to the cust_num attribute of your order entry context (order_entry_ctx) inside the security policy function.


You could use a login trigger to set the initial context. 

See Also:

Compare and contrast this example, which uses an application context within the dynamically generated predicate, with "Example of a Dynamically Modified Statement", which uses a subquery in the predicate.

Chapter 12, "Using Triggers" 

Step 1. Create a PL/SQL package which sets the context for the application
   PROCEDURE set_cust_num ;

   PROCEDURE set_cust_num IS
     custnum NUMBER;
      SELECT cust_no INTO custnum FROM customers WHERE username =
         SYS_CONTEXT('USERENV', 'session_user');
     /* SET cust_num attribute in 'order_entry' context */
         DBMS_SESSION.SET_CONTEXT('order_entry', 'cust_num', custnum);
         DBMS_SESSION.SET_CONTEXT('order_entry', 'cust_num', custnum);
   END set_cust_num;


This example does not treat error handling.

You can access predefined attributes--such as session user--by using SYS_CONTEXT('USERENV', session_primitive).

For more information, see Oracle8i SQL Reference. 

Step 2. Create an application context

Create an application context by entering:

CREATE CONTEXT Order_entry USING Apps.Oe_ctx;
Step 3. Access the application context inside the package that implements the security policy on the database object


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


The package body appends a dynamic predicate to SELECT statements on the ORDERS_TAB table. This predicate limits the orders returned to those of the user's customer number by accessing the cust_num context attribute, instead of a subquery to the customers table.


/* limits select statements based on customer number: */
    D_predicate VARCHAR2 (2000)
     D_predicate = 'cust_no = SYS_CONTEXT("order_entry", "cust_num")';
     RETURN D_predicate;    
    END Custnum_sec;
END Oe_security;
Step 4. Create the new security policy


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

CONNECT sys/change_on_install AS sysdba;

DBMS_RLS.ADD_POLICY ('scott', 'orders_tab', 'oe_policy', 'secusr',
                     'oe_security.custnum_sec', 'select')

This statement adds a policy named OE_POLICY to the ORDERS_TAB table for viewing in schema SCOTT. The SECUSR.OE_SECURITY.CUSTNUM_SEC function implements the policy, is stored in the SECUSR schema, and applies to SELECT statements only.

Now, any select statement by a customer on the ORDERS_TAB table automatically returns only that customer's orders. In other words, the dynamic predicate modifies the user's statement from this:

SELECT * FROM Orders_tab;

to this:

SELECT * FROM Orders_tab 
   WHERE Custno = SYS_CONTEXT('order_entry','cust_num'); 

Note the following with regard to this example:

  • You could set your context attributes based on data from a database table or tables, or from a directory server using LDAP (Lightweight Directory Access Protocol).

    Example 2: Human Resources Application #1

    This example uses application context to control user access by way of a Human Resources application. It guides you through the following three tasks, each of which is described more fully below.

    In this example, assume that the application context for the Human Resources application is assigned to the HR_CTX namespace.

    Step 1. Create a PL/SQL package with a number of functions that sets the context for the application


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

    CREATE OR REPLACE PACKAGE apps.hr_sec_ctx IS 
       PROCEDURE set_resp_id (respid NUMBER);
       PROCEDURE set_org_id (orgid NUMBER);
      /* PROCEDURE validate_respid (respid NUMBER); */
      /* PROCEDURE validate_org_id (orgid NUMBER); */
    END hr_sec_ctx;

    APPS is the schema owning the package.

    /* function to set responsibility id */
    PROCEDURE set_resp_id (respid NUMBER) IS
    /* validate respid based on primitive and other context */
    /*    validate_respid (respid); */
    /* set resp_id attribute under namespace 'hr_ctx'*/
        DBMS_SESSION.SET_CONTEXT('hr_ctx', 'resp_id', respid);
    END set_resp_id;
    /* function to set organization id */
    PROCEDURE set_org_id (orgid NUMBER) IS
    /* validate organization ID */
    /*    validate_org_id(orgid); /*
    /* set org_id attribute under namespace 'hr_ctx' */
        DBMS_SESSION.SET_CONTEXT('hr_ctx', 'org_id', orgid);
    END set_org_id;
    /* more functions to set other attributes for the HR application */
    END hr_sec_ctx;
    Step 2. Create the context and associate it with the package
    CREATE CONTEXT Hr_ctx USING Apps.Hr_sec_ctx;
    Step 3. Create the initialization script for the application

    Suppose that the execute privilege on the package HR_SEC_CTX has been granted to the schema running the application. Part of the script will make calls to set various attributes of the HR_CTX context. Here, we do not show how the context is determined. Normally, it is based on the primitive context or other derived context.


    The SYS_CONTEXT function can be used for data access control based on this application context. For example, the base table HR_ORGANIZATION_UNIT can be secured by a view that restricts access to rows based on attribute ORG_ID:


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

    CREATE TABLE hr_organization_unit (organization_id NUMBER);

    CREATE VIEW Hr_organization_secv AS 
       SELECT * FROM hr_organization_unit 
          WHERE Organization_id = SYS_CONTEXT('hr_ctx','org_id');

    Example 3: Human Resources Application #2


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

    CREATE TABLE Payroll(
       Srate  NUMBER, 
       Orate  NUMBER, 
       Acctno NUMBER, 
       Empno  NUMBER, 
       Name   VARCHAR2(20));
    CREATE TABLE Directory_u(
       Empno NUMBER, 
       Mgrno NUMBER, 
       Rank  NUMBER);

    This example illustrates the use of the following security features in Oracle8i:

    In this example, we associate a security policy with the table called DIRECTORY which has the following columns:


    identification number for each employee 


    employee identification number for the manager of each employee 


    position of the employee in the corporate hierarchy 

    The security policy associated with this table has two elements:

    Authentication Through a Middle Tier

    The growth of three-tier systems (for example, browser to application server to database) has increased dramatically with the growth of the Internet. Indeed, three-tier applications are often referred to as the "Internet computing model."

    Oracle8i n-tier authentication addresses many security difficulties that arise in three-tier applications. It enables organizations to reap the benefits of Internet computing, while minimizing the security risks of three-tier systems. This section includes:

    Advantages of n-Tier Authentication

    Three-tier systems provide many benefits to organizations.

    In addition, Oracle n-tier authentication delivers the following security benefits:

    Security Challenges of Three-tier Computing

    While three-tier computing provides many benefits, it raises a number of new security issues:

    Who Is the Real User?

    Most organizations want to know the identity of the actual user who is accessing the database, for reasons of access control or auditing. User accountability is diminished if the identity of the users cannot be traced through all tiers of the application.

    Furthermore, if only the application server knows who the user is, then all per-user security enforcement must be done by the application itself. Application-based security is very expensive. If each application that accesses the data must enforce security, then security must be reimplemented in each and every application. It is often preferable to build security on the data itself, with per-user accountability enforced within the database.

    Does the Middle Tier Have Too Much Privilege?

    Some organizations are willing to accept three-tier systems within the enterprise, in which "all-privileged" middle tiers, such as transaction processing (TP) monitors, can perform all actions for all users. In this architecture, the middle tier connects to the database as the same user for all application users. It therefore needs to have all privileges that application users need to do their jobs.

    This computing model may be undesirable in the Internet, where the middle tier resides outside, on, or just inside a firewall. More desirable, in this context, is a limited trust model, in which the identity of the real client is known to the data server, and the application server (or other middle tier) has a restricted privilege set.

    Also useful is the ability to limit the users on whose behalf a middle tier can connect, and the roles the middle tier can assume for the user. For example, many organizations would prefer that users have different privileges depending on where they are connecting from. A user connecting to a web server or application server on the firewall might only be able to use very minimal privileges to access data, whereas a user connecting to a web server or application server within the enterprise might be able to exercise all privileges she is otherwise entitled to have.

    How to Audit? Whom to Audit?

    Accountability through auditing is a basic principle of information security. Most organizations want to know on whose behalf a transaction was accomplished, not just that a particular application server performed a transaction. A system must therefore be able to differentiate between a user performing a transaction, and an application server performing a transaction on behalf of a user.

    Auditing in three-tier systems should be tied to the issue of knowing the real user: if you cannot preserve the user's identity through the middle tier of a three-tier application, you cannot audit actions on behalf of the user.

    Can the User Be Reauthenticated to the Database?

    In client/server systems, authentication tends to be straightforward: the client authenticates to the server. In three-tier systems authentication is more difficult, because there are several potential authentications.

    Client to Middle Tier Authentication

    Client authentication to the middle tier is clearly required if a system is to conform with basic security principles. The middle tier is typically the first gateway to useful information that the user can access. Users must, therefore, authenticate to the middle tier. Note that such authentication may be mutual; that is, the middle tier authenticates to the client just as the client authenticates to the middle tier.

    Middle Tier to Database Authentication

    Since the middle tier must typically initiate a connection to a database to retrieve data (whether on its own behalf or on behalf of the user), this connection clearly must be authenticated. In fact, the Oracle8i database does not allow unauthenticated connections. Again, middle tier to database authentication may also be mutual.

    Client Reauthentication Through Middle Tier to Database

    Client reauthentication from the middle tier to the database is problematic in three-tier systems. The username may not be the same on the middle tier and the database. In this case, users may need to reenter a username and password, which the middle tier uses to connect on their behalf. Or, more commonly, the middle tier may need to map the username provided, to a database username. This mapping is often done in an LDAP-compliant directory service, such as Oracle Internet Directory.

    For the client to reauthenticate himself to the database, the middle tier either needs to ask the user for a password (which it then must be trusted to pass to the database), or the middle tier must retrieve a password for the user and use that to authenticate the user. Both approaches involve security risks, because the middle tier is trusted to handle the user's password properly, and not use it maliciously.

    One of the only cases for which reauthentication does not involve trusting the middle tier occurs when a middle tier downloads an applet to a client, and the client connects directly to the database via the applet. In this case, the application server is literally just that: it serves the application (applet) to the user, and has no part in further authentication of the user.

    Reauthenticating the client to the back-end database is not always beneficial. First, two sets of authentication handshakes per user involves considerable network overhead. Second, you must trust the middle tier to have authenticated the user. (You clearly must trust the middle tier if it retrieves or otherwise is privy to the user's password.) It is therefore not unreasonable for the database to simply accept that the middle tier has performed proper authentication. In other words, the database accepts the identity of the real client without requiring the real client to authenticate herself.

    For some authentication protocols, client reauthentication is just not possible. For example, many browsers and application servers support the Secure Sockets Layer (SSL) protocol. Both the Oracle8i database (through Oracle Advanced Security) and Oracle Application Server support the use of SSL for client authentication. However, SSL is a point-to-point protocol, not an end-to-end protocol. It cannot be used to reauthenticate a browser client (through the middle tier) to the database.

    The reason for this is that a user cannot securely give up his private key to the middle tier in order for the reauthentication of the client to occur. Once the user's private key is compromised, the user's very identity is compromised. In addition, there is no way to "tunnel" through a middle tier so that the authentication of the browser client to the database can occur directly.

    In short, organizations deploying three-tier systems require flexibility as regards reauthentication of the client. In some cases, they cannot reauthenticate the client; in other cases, they may choose whether or not to reauthenticate the client.

    Oracle8i n-Tier Authentication Solutions

    The following sections explain how Oracle8i addresses each of the challenges listed above.

    Passing Through the Identity of the Real User

    Many organizations want to know who the real user is through all tiers of an application, without sacrificing the benefits of a middle tier. Oracle8i provides the ability to preserve client identity through the Oracle Call Interface (OCI).

    OCI enables a middle tier to set up, within a single database connection, a number of "lightweight" user sessions, each of which uniquely identifies a connected user. These lightweight sessions reduce the network overhead of creating separate network connections from the middle tier to the database. The application can switch between these sessions as required to process transactions on behalf of users.

    The full authentication sequence from the client to the middle tier to the database occurs as follows:

    1. The client authenticates to the middle tier, using whatever form of authentication the middle tier will accept. For example, the client could authenticate to the middle tier using a username/password, or an X.509 certificate by means of SSL.

    2. The middle tier authenticates itself to Oracle8i, using whatever form of authentication Oracle8i will accept. This could be a password, or an authentication mechanism supported by Oracle Advanced Security, such as a Kerberos ticket or an X.509 certificate (SSL).

    3. The middle tier then creates one or more sessions for users using the Oracle Call Interface. The lightweight session information must include username as a minimum. The middle tier may optionally provide a password for the client, and the roles for the client.

    4. Since the database cannot require the middle tier to provide a password for the client, authentication is performed by OCI. To create the session for the client, the middle-tier server calls the OCISessionBegin function. Prior to calling OCISessionBegin, the OCIAttrSet function is called to provide the needed information about the client to the middle tier server. It is called in turn with the following attributes:


      Sets the database user name of the client. This attribute is mandatory.  


      If the client has provided a database password to be validated by the database, then the middle tier server passes it along with the username. If this attribute is not provided, then it is assumed that the middle tier server has authenticated the client. 


      This attribute tells the server that the client is connecting through a middle tier server. 


      If the middle tier server wants to activate a set of roles upon connecting as the client, then the list is passed along with this attribute. 

    5. The database verifies that the middle tier is privileged to create sessions on behalf of the user, using the roles provided. (See "Limiting the Privilege of the Middle Tier," below).

      The OCISessionBegin call will fail if the application server is not allowed to proxy on behalf of the client by the administrator, or if the application server is not allowed to activate the specified roles.

    Limiting the Privilege of the Middle Tier

    "Least privilege" is the principle that users should have the fewest privileges necessary to perform their duties, and no more. As applied to middle tier applications, this means that the middle tier should not have more privileges than it needs. Oracle8i enables you to limit the middle tier such that it can connect only on behalf of certain users, using only specific roles.

    For example, suppose that user Sarah wants to connect to the database through a middle tier, appsrv (which is also a database user). Sarah has multiple roles, but it is desirable to restrict the middle tier to exercise only the clerk role on her behalf.

    A DBA could effectively grant permission for appsrv to initiate connections on behalf of Sarah using her clerk role only, using the following syntax:


    By default, the middle tier cannot create connections for any client. The permission must be granted on a per-user basis.

    To allow appsrv to use all of the roles granted to the client Sarah, the following statement would be used:


    Each time a middle tier initiates a lightweight (OCI) session for another database user, the database verifies that the middle tier is privileged to connect for that user, using the role specified.

    Reauthenticating the Real User

    As described above, it is not always beneficial to reauthenticate users to the database after they have been authenticated by the middle tier. However, if you wish to do this for an added measure of security, you can pass the database the user's password using the OCI_ATTR_PASSWORD attribute of the OCIAttrSet call.

    See Also:

    For more information about security in three-tier architectures, see the Oracle Call Interface Programmer's Guide. 

    Auditing Actions Taken on Behalf of the Real User

    The n-tier authentication features of Oracle8i enable you audit actions a middle tier performs on behalf of a user. For example, suppose an application server hrappserver creates multiple lightweight sessions for users Ajit and Jane. A DBA could enable auditing for SELECTs on the bonus table that hrappserver initiates for Jane as follows:

    AUDIT SELECT ON bonuses BY hrappserver ON BEHALF OF Jane;

    Alternatively, the DBA could enable auditing on behalf of multiple users (in this case, both Jane and Ajit) connecting through a middle tier as follows:

    AUDIT SELECT ON bonuses BY hrappserver ON BEHALF OF ANY;

    This auditing option only audits SELECT statements being initiated by hrappserver on behalf of other users. A DBA can enable separate auditing options to capture SELECTs against the bonus table from clients connecting directly to the database:

    AUDIT SELECT ON bonuses;

    Data Encryption

    For certain applications, you may decide to encrypt data as an additional measure of security.

    Most issues of data security can be handled by appropriate authentication and access control, ensuring that only properly identified and authorized users can access data. Data in the database, however, cannot normally be secured against the database administrator's access, since a DBA has all privileges. Likewise, organizations may have concerns about securing sensitive data stored offline, such as backup files stored with a third party.

    Information which may be especially sensitive and warrant encryption could include credit card numbers; national identity numbers in countries with strict privacy laws; or trade secrets, such as industrial formulas. Applications for which a user is authenticated to the application, rather than to the database, may also use encryption to protect the application user password or cookie.


    For applications dealing with this highly sensitive data, Oracle provides the
    DBMS_OBFUSCATION_TOOLKIT PL/SQL package to encrypt and decrypt data, including string inputs and raw inputs. The function is limited to selected algorithms, such as the Data Encryption Standard (DES). Developers may not plug in their own encryption algorithms, and the key length is also fixed. The function prohibits making multiple passes of encryption; that is, you cannot nest encryption calls, thereby encrypting an encrypted value. These restrictions are required by U.S. laws governing the export of cryptographic products.

    The cryptographic functions provide encrypt and decrypt services only and do not provide any built-in key management services (such as automatic key recovery). Developers using the package must handle key storage programmatically. For example, encryption keys could be stored apart from the database if the intention is to prevent the DBA from seeing encrypted data.

    Development Considerations

    In applications using cryptography, encryption must not interfere with other access controls. That is, it must not prevent users from accessing an object they are otherwise privileged to access. Otherwise, their ability to perform their jobs is impaired. For example, a user who has SELECT privilege on EMP should not be limited by the encryption mechanism from seeing all the data he is otherwise privileged to see. There is little benefit to encrypting part of a table with one key and part of a table with another key if users need to see all encrypted data in the table; it merely adds to the overhead of decrypting data before users can read it.

    Encrypting indexed data is not supported.

    Prudent security practice dictates periodically changing an encryption key to mitigate the threat of a compromised key. Changing the key requires that the encrypted object (or objects) be decrypted and reencrypted using the new key or keys. This process may be time-consuming and would probably have to be done when the data is not being accessed.

    See Also:

    PL/SQL User's Guide and Reference 

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

    All Rights Reserved.