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

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

Master Index


Go to previous page Go to next page

Implementing Application Security Policies

This chapter explains how to implement application security policies. Topics in this chapter include:

Introduction to Application Context

Application context allows you to write applications which draw upon certain aspects of a user's session information. 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 a 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 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:

Specifying Attributes for Each Application

Each application can have its own context with its own attributes. Suppose, for example, 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.

Providing Security Validation

Suppose you have a General Ledger application, which has access control based upon the set of books being used. 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 prevent users from changing a context attribute without the above security validation, Oracle ensures that an attribute can be changed only by the designated package which implements the context.

Providing Access to Predefined Attributes Through the USERENV Namespace

Oracle9i provides a built-in application context namespace, USERENV, which provides access to predefined attributes. These attributes are 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 or thick JDBC, 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 the user is not proxied (that is, when the user connects directly to the database), the user would not be able to access any data.

While you can use the PROXY_USER attribute within VPD to ensure that users only access data through a particular middle-tier application, a different approach would be to develop a secure application role. Rather than have each policy ensure that the user accesses the database by being proxied through HRAPPSERVER, you could have the secure application role enforce this.

Although predefined attributes can be accessed through the USERENV application context, they cannot not be changed. They are listed in Table 12-1.

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

SYS_CONTEXT('userenv', 'attribute')


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

See Also:

SYS_CONTEXT in the Oracle9i SQL Reference for complete details about the USERENV namespace and its predefined attributes 

Table 12-1 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 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 NLS calendar used for dates in the current session 


Returns the current date format of the current session 


Returns language used to express dates in the current session 


Indicates whether the sort base is binary or linguistic 


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


Returns the user ID of the user under whose privilege the current session runs. 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 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 of the host 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 (when available) 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. If the user has been authenticated through SSL, or if the user's certificate was proxied to the database, this includes the user's X.509 certificate 


SQL text of the query that triggers fine-grained audit event handler. Only valid inside the event handler 


User-defined client identifier for the session 


Amount of shared memory used by global application context, in bytes 

Externalized Application Contexts

Many applications store attributes used for fine-grained access control within a database metadata table that they use for access control. For example, an EMPLOYEES table could include cost center, title, signing authority, and other information useful for fine-grained access control. However, many organizations centralize user information and user management in an LDAP-based directory such as Oracle Internet Directory. These organizations also wish to centralize the information about users that is used for access control. Application context attributes can be stored in Oracle Internet Directory and assigned to one or more enterprise users. They can be retrieved automatically upon login for an enterprise user, and used to initialize an application context.


Enterprise User Management is a feature of Oracle Advanced Security. 

See Also:

"Initializing Application Context Globally"

Oracle Advanced Security Administrator's Guide 

Ways to Use Application Context with Fine-Grained Access Control

To simplify the implementation of a security policy, you have the option of using application context within a fine-grained access control function.


Using application context with fine-grained access control is called virtual private database (VPD). 

Application context can be used in the following 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 upon customer number. Rather than querying the customer number for a logged-in user each time you need it, you could store the number in the application context. In this way, the customer number is available when you need it.

Application context is especially helpful if your security policy is based upon 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.

Consider an order entry application which enforces the rules, "customers only see their own orders, and clerks see all orders for all customers." These are 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 Similar to 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 customer number is evaluated at execution time. This value is, of course, different for every customer. Use of application context in this case provides optimum performance, as well as fine-grained security.

Note that the SYS_CONTEXT function works much like a bind variable, but only if the SYS_CONTEXT arguments are constants.

User Models and Virtual Private Database

Applications may have differing user models, but you may want to use virtual private database (VPD) to limit access by user. Whether the user is a database user or an application user unknown to the database, Oracle provides different ways in which applications can enforce per-user fine-grained access control.

For applications in which the application users are also database users, VPD enforcement is relatively simple; users connect to the database, and the application can set up per-session application contexts. Each session is initiated under a different username, so that it is simple to enforce different fine-grained access control conditions for users Jane and John. This is also possible with use of proxy authentication, since each "lightweight" session in OCI or thick JDBC is still a distinct database session, and can have its own application context.

Since proxy authentication can be integrated with Enterprise User Security, user roles can be retrieved from Oracle Internet Directory, as well as other attributes that can be used for VPD enforcement.

For applications in which a single user (for example, One Big Application User) connects to the database on behalf of all users, per-user fine-grained access control is still possible. An application developer can create a context attribute to represent the application user (for example, realuser). While all database sessions (and thus all audit records) are initiated as One Big Application User, each session can nonetheless have attributes that vary, depending on who the real user is. This model works best for applications with a limited number of users where there is no requirement for session reuse. Of course, each session, from the database standpoint, is created as the same database user, so that the ability to use roles, database auditing, and others is greatly diminished for reasons previously enumerated.

Web-based applications typically have hundreds if not thousands of users, and the web is stateless. There may be a persistent connection to the database (to support data retrieval for a number of user requests), but these connections are not specific to each web-based user. Web-based applications typically set up and reuse connections instead of having different sessions for each user, to provide scalability. For example, web user Jane and Ajit connect to a middle tier application, which establishes a session in the database used by the application on behalf of both users. Typically, neither Jane nor Ajit are known to the database. The application is responsible for switching the username on the connection, so that, at any given time, it's either Jane or Ajit using the session.

Oracle9i VPD capabilities facilitate connection pooling by allowing multiple connections to access one or more global application contexts, instead of setting up an application context for each distinct user session.

Applications use a CLIENT_IDENTIFIER (which could be an individual application username, or a group) to reference the global application context. Global application contexts provide additional flexibility for web-based applications to use Virtual Private Database, as well as enhanced performance through reuse of common application contexts among multiple sessions instead of setting up per-session application contexts. The CLIENT_IDENTIFIER is also viewable in the user session and accessible in the USERENV naming context.

The use of a CLIENT_IDENTIFIER thus functions as an application user proxy, since the CLIENT_IDENTIFIER can be used to capture the 'application username.' The ability to pass a CLIENT_IDENTIFIER to the database for use with global application context is supported in OCI, thick JDBC, and thin JDBC. For OCI-based connections, a change in CLIENT_IDENTIFIER is automatically piggybacked on the next OCI call, for additional performance benefits.

Application user proxy authentication can be used with global application context for additional flexibility and high performance in building applications. For example, suppose a web-based application that provides information to business partners has three types of users: gold partner, silver partner, and bronze partner, representing different levels of information available. Instead of each user having his own session -- with individual application contexts -- set up, the application could set up global application contexts for gold partner, silver partner, or bronze partner and use the client identifier to point the session at the correct context, in order to retrieve the appropriate type of data. The application need only initialize the three global contexts once, and use the client identifier to access the correct application context to limit data access. This provides performance improvements through session reuse, and through accessing global application contexts set up once, instead of having to initialize application contexts for each session individually.

Creating a Virtual Private Database Policy with Oracle Policy Manager

Developers implementing virtual private database (VPD) can use the DBMS_RLS package to apply security policies to tables and views. Also, Developers can use the CREATE CONTEXT command to create application contexts.

Alternatively, developers can use the Oracle Policy Manager graphical user interface, accessed from Oracle Enterprise Manager, to apply security policies to schema objects, such as tables and views, and to create application contexts. Oracle Policy Manager provides an easy-to-use interface to manage security policies and application contexts, and therefore makes VPD easier to develop.

Oracle Policy Manager is the administration tool for Oracle Label Security. Oracle Label Security provides a functional, out-of-the-box VPD policy which enhances your ability to implement row-level security. It supplies an infrastructure--a label-based access control framework--whereby you can specify labels for users and data. It also enables you to create one or more custom security policies to be used for label access decisions. You can implement these policies without any knowledge of a programming language. There is no need to write additional code; in a single step you can apply a security policy to a given table. In this way, Oracle Label Security provides a straightforward, efficient way to implement fine-grained security policies using data labeling technology. Finally, the structure of Oracle Label Security labels provides a degree of granularity and flexibility which cannot easily be derived from the application data alone. Oracle Label Security is thus a generic solution which can be used in many different circumstances.

To create VPD policies, users must provide the schema name, table (or view) name, policy name, the function name that generates the predicate, and the statement types to which the policy applies (that is, SELECT, INSERT, UPDATE, DELETE). Oracle Policy Manager then executes the function DBMS_RLS.ADD_POLICY. You create an application context by providing the name of the context and the package that implements the context.

See Also:

Oracle Label Security Administrator's Guide  

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 for creating the PL/SQL package, followed by a discussion of SYS_CONTEXT syntax and behavior.

Note: A login trigger can be used 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:

Oracle9i Supplied PL/SQL Packages and Types Reference 


The syntax for this function is:

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

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


If you try to execute SYS_CONTEXT in a parallel query environment, you will receive a query error. 

See Also:

"Providing Access to Predefined Attributes Through the USERENV Namespace"

Oracle9i SQL Reference for details about attributes 

Using Dynamic SQL with SYS_CONTEXT


This feature is applicable when COMPATIBLE is set to either 8.0 or 8.1. 

During a session in which you expect a change in policy between executions of a given query, that query must use dynamic SQL. You must use dynamic SQL because static SQL and dynamic SQL parse statements differently.

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.

See Also:

"Automatic Reparse"  

Parallel Query Requires SYS_CONTEXT Directly Within Query

If SYS_CONTEXT is used inside a SQL function which is embedded in a parallel query, the function cannot pick up the application context. This is true because the application context exists only in the user session. To use these features in combination, you must call SYS_CONTEXT directly from the query.

Consider 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, Oracle9i 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, 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 only set the context attributes inside the trusted procedure you named in the CREATE CONTEXT statement. This prevents a malicious user from changing context attributes without proper attribute validation.

Alternatively, you can use the Oracle Policy Manager graphical user interface to create a context and associate it with a PL/SQL package. Oracle Policy Manager, accessed from Oracle Enterprise Manager, enables you to apply policies to database objects and create application contexts. It also can be used to create and manage Oracle Label Security policies.

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

See Also:

"Features of Application Context"

"Introduction to Application Context Accessed Globally" 

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.

Examples: Application Context Within a Fine-Grained Access Control Function

This section provides three examples that use application context within a fine-grained access control function.

Example 1: Implementing the Policy

This 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 assumes a one-to-one relationship between users and customers. It finds the user's customer number (Cust_num), and caches the customer number in the application context. You can later refer to the cust_num attribute of your order entry context (order_entry_ctx) inside the security policy function.

Note that you could use a login trigger to set the initial context.

Step 1. Create a PL/SQL Package Which Sets the Context for the Application

Create the package as follows:

   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 Oracle9i SQL Reference 

Step 2. Create an Application Context

Create an application context by entering:

CREATE CONTEXT Order_entry USING Apps.Oe_ctx;

Alternatively, you can use Oracle Policy Manager to create an application context.

Step 3. Access the Application Context Inside the Package

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

Create the policy as follows:


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

    See Also:

    Compare and contrast this example, which uses an application context within the dynamically generated predicate, with "How Fine-Grained Access Control Works", which uses a subquery in the predicate

    Chapter 15, "Using Triggers" 

    Example 2: Controlling User Access by Way of an Application

    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 to Set the Context

    Create a PL/SQL package with a number of functions that set 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

    For example:

    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: Event Triggers, Application Context, Fine-Grained Access Control, and Encapsulation of Privileges

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

    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


    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);

    The security policy associated with this table has two elements:

    Automatic Reparse


    This feature is applicable when COMPATIBLE is set to 9.0.1. 

    Any SQL table or view, that is VPD-policy based, will enable automatic reparse to support dynamic adjustments to policies. Oracle will execute a policy and get the latest policy.

    For example, users can develop policies that are based on time of day. When a parsed cursor is executed, Oracle will execute a policy function automatically to get the up-to-date predicate.


    For policy function that returns the same predicate, we recommend that you mark the policy function DETERMINISTIC to enhance. 

    See Also:

    "Using Dynamic SQL with SYS_CONTEXT" 

    Introduction to Application Context Accessed Globally

    In many application architectures, the middle tier application is responsible for managing session pooling for application users. That is, users authenticate themselves to the application, which uses a single identity to log into the database and maintains all the connections. In this environment, it is not possible to maintain application attributes using session-dependent secure application context because of the sessionless model of the application.

    Another scenario is when a user is connected to the database through an application (such as Oracle Forms) which then spawns other applications (such as Oracle Reports) to connect to the database. These applications may need to share the session attributes such that they appear to be sharing the same database session.

    Global application context is a type of secure application context that can be shared among trusted sessions. In addition to driving the enforcement of the fine-grained access control policies, applications (especially middle-tier products) can use this support to manage application attributes securely and globally.


    Global application context is not available in Real Application Clusters. 

    Using the DBMS_SESSION Interface to Manage Application Context in Client Sessions

    The DBMS_SESSION interface for managing application context has a client identifier for each application context. In this way, application context can be managed globally, yet each client sees only his or her assigned application context. The following interfaces in DBMS_SESSION enable the administrator to manage application context in client sessions:

    The middle-tier application server can use SET_CONTEXT to set application context for a specific client ID. Then, when assigning a database connection to process the client request, the application server needs to issue a SET_IDENTIFIER to denote the ID of the application session. From then on, every time the client invokes SYS_CONTEXT, only the context that was associated with the set identifier is returned. In other words, the application server uses SET_IDENTIFIER to associate the database session with a particular user or a group. Then, the CLIENT_IDENTIFIER is an attribute of the session and can be viewed in session information. Also, CLIENT_IDENTIFIER is the key to accessing the global application context. For example, suppose a web-based application that provides information to business partners has three types of users: gold partner, silver partner, and bronze partner. These users represent different levels of available information. Instead of each user having their own setup session with application contexts, the application could set up global application contexts for gold partner, silver partner, and bronze partner. Afterwards, one can do the following:

    The application need only initialize the three global contexts once, and use CLIENT_IDENTIFIER to access the correct application context to limit data access. This provides performance improvements through session reuse, and through accessing global application contexts setup once, instead of having to initialize application contexts for each session.

    Example 1: Global Access of Application Context

    For an application context accessed globally, the scenario is as follows:

    1. Consider an application server that has assigned the client identifier 12345 to client SCOTT. It then issues the following statement to indicate that, for this client identifier, there is an application context RESPONSIBILITY with a value of 13 in the HR namespace.


      Note that HR must be a global context namespace created as follows:

    2. Then, for each client session using APPSMGR to establish a connection to database, the following command should be issued when client SCOTT is assigned to a new database session to indicate identity:

    3. Within the database session, when there is a SYS_CONTEXT('HR','RESPONSIBILITY') call, the database engine will match the client identifier 12345 to the global context, and return the value 13.

    4. When exiting this database session, middle tier can clear the client identifier by issuing:


    After the client identifier in a session is clear, it takes on a NULL value, implying that any subsequent SYS_CONTEXT call will only retrieve application context with a NULL client identifier, until the client identifier is set again using the SET_IDENTIFIER interface.


    Versioning is not available for application context accessed globally. This will return a point in time SYS_CONTEXT value. Since multiple client sessions could be accessing the same global application context values at any time, versioning is not possible. Simple application context is per session, and can be versioned. 

    Example 2: Global Access of Application Context for Proxy Authentication Applications

    For a proxy authentication application, the scenario is as follows:

    1. The administrator creates the global context namespace by issuing:

    2. The HR application server (AS) starts up and establishes multiple connections to the HR database as user APPSMGR.

    3. User SCOTT logs on to the HR application server.

    4. AS authenticates SCOTT into the application.

    5. AS assigns a temporary session ID (or simply uses the application user ID), 12345, for this connection.

    6. The session ID is returned to SCOTT's browser as part of a cookie or maintained by AS.


      If the application generates a session ID for use as a CLIENT_IDENTIFIER, the session ID must be suitably random, and protected over the network through encryption. If the session ID is not random, then a malicious user could guess the session ID and access another user's data. If the session ID is unencrypted over the network, then a malicious user could retrieve the session ID and access the connection. 

    7. AS initializes application context for this client calling the HR.INIT package, which issues:

      DBMS_SESSION.SET_CONTEXT( 'hr', 'id', 'scott', 'APPSMGR', 12345 );
      DBMS_SESSION.SET_CONTEXT( 'hr', 'dept', 'sales', 'APPSMGR', 12345 );
    8. AS assigns a database connection to this session, and initializes the session by issuing:

    9. All SYS_CONTEXT calls within this database session will return application context values belonging to the client session only. For example, SYS_CONTEXT('hr','id') will return the value SCOTT.

    10. When done with the session, AS can issue the following statement to clean up the client identity:


    Note that even if another database user (ADAMS) had logged into the database, he cannot access the global context set by AS because AS has specified that only the application with logged in user APPSMGR can see it. If AS has used the following, then any user session with client ID set to 12345 can see the global context.

    DBMS_SESSION.SET_CONTEXT( 'hr', 'id', 'scott', NULL , 12345 );
    DBMS_SESSION.SET_CONTEXT( 'hr', 'dept', 'sales', NULL , 12345 );

    This approach enables different users to share the same context.

    The users, however, should be aware of the security implication of different settings of the global context. Basically, NULL in the username means that any user can access the global context. A NULL client ID in the global context means that only a session with an uninitialized client ID can access the global context.

    Users can query the client identifier set in the session as follows:


    The DBA can see which sessions have the client identifier set by querying the V$SESSION view's CLIENT_IDENTIFIER and USERNAME.

    When a user wants to see how much global context area (in bytes) is being used, she can use SYS_CONTEXT('USERENV', 'GLOBAL_CONTEXT_MEMORY')

    See Also:

    Oracle9i SQL Reference

    Oracle9i Supplied PL/SQL Packages and Types Reference

    Oracle9i JDBC Developer's Guide and Reference and Oracle Call Interface Programmer's Guide for client identifier information  

    Initializing Application Context Externally

    This feature lets you specify a special type of namespace that accepts initialization of attribute values from external resources. This enhances performance and enables the automatic propagation of attributes from one session to the other. For example, many organizations want to manage user information centrally, in an LDAP-based directory. Oracle9i Enterprise User Security feature supports centralized user and authorization management in Oracle Internet Directory. However, there may be additional attributes an application wishes to retrieve from LDAP to use for VPD enforcement:

    The ability to initialize application context from external sources such as LDAP helps organizations leverage existing information they have for VPD enforcement, that is centrally managed, without requiring replication or duplication of this information in database tables.

    This section contains these topics:

    Obtaining Default Values from Users

    In some situations it is desirable to obtain default values from users. These default values may serve as hints or preferences initially, and may become trusted context after the values are validated. Similarly, clients may want a convenient way to initialize some default values, and then rely on a login event trigger or applications to validate the values.

    For job queues, administrators may expect the job submission routine to record all the context being set at the time the job is submitted, and restore it when executing the batched job. To maintain the integrity of context, job queues cannot bypass the designated PLSQL package to set the context. Rather, externally initialized application context accepts initialization of context values from the job queue process.

    Whereas automatic propagation of context to a remote session may create security problems, developers or administrators can effectively handle this new type of context that takes default values from resources other than the designated PLSQL procedure. In addition, performance is enhanced because this feature provides an extensible interface for the OCI client to bundle more information to the server in one OCISessionBegin() call.

    Obtaining Values from Other External Resources

    In addition to using the designated trusted package, externally initialized application context can also accept initialization of attributes and values through external resources such as an OCI interface, a job queue process, or a database link. It provides:

    Although this new type of namespace can be initialized by any client program using OCI, there are login event triggers that can verify the values. It is up to the application to interpret and trust the values of the attributes.

    Note that with this feature, the middle-tier server can actually initialize context values on behalf of database users. Context attributes are propagated for the remote session at initiation time, and the remote database accepts the values if the namespace is externally initialized.

    Obtaining Values for Users Not Known to the Database

    Externally initialized application context is especially useful for cases in which users are not known to the database. In these situations, the application typically connects as a single database user, and all actions are taken as that user. Since all user sessions are created as the same user, this security model normally makes it very difficult, if not impossible, to use the virtual private database capability to achieve per user or per customer data separation. However, these applications can use the client identifier as an application user proxy. In this way, the application uses the client identifier to proxy the "real" application user name to the database.

    This approach has several advantages. With application user proxy, the sessions can be reused by multiple users merely by changing the client identifier (which here is employed to capture the name of the real application user). This avoids the overhead of setting up a separate session and separate attributes for the user, and enables reuse of sessions by the application merely by changing the client identifier (to represent the new application user name). When a client changes the client identifier, the change is piggybacked on the next OCI (or thick JDBC) call, for additional performance gains. Application user proxy (via client identifier) is available in available in OCI, thick JDBC, and thin JDBC.

    Suppose, for example, that user Daniel connects to a Web Expense application. Daniel is not a database user, he is a typical Web Expense application user. The application sets up a global application context for a typical web user and sets DANIEL as the client identifier. Daniel completes his Web Expense form and exits the application. Ajit now connects to the Web Expense application. Instead of setting up a new session for Ajit, the application reuses the session that currently exists for Daniel, merely by changing the client identifier to AJIT. This avoids both the overhead of setting up a new connection to the database, and the overhead of initializing a new application context.

    Note that the client identifier can be anything the application wishes to base access control upon; it need not be an application username.

    Another way in which the client identifier can be used for applications whose users are not database users, is to use the client identifier as a type of group or role mechanism. For example, suppose a Marketing application has three types of users: standard partners, silver partners, and gold partners. The application could use the global application context feature to set up three types of contexts (standard, silver, and gold). The application then determines which type of partner a user is, and, passes the client identifier to the database for a session. The client identifier (standard, silver, or gold) here acts like a pointer to the correct application context. There may be multiple sessions that are silver, for example, and yet they all share the same application context.

    See Also:

    Oracle9i JDBC Developer's Guide and Reference

    Oracle Call Interface Programmer's Guide  

    Initializing Application Context Globally

    This feature provides a centralized location to store the user's application context, enabling applications to set up the user's contexts during initialization based upon the user's identity. In particular, it supports Oracle Label Security labels and privileges. This feature makes it much easier for the administrator to manage contexts for large numbers of users and databases.

    This section contains these topics:

    Application Context Utilizing LDAP

    Application context initialized globally utilizes the Lightweight Directory Access Protocol (LDAP). LDAP is a standard, extensible, and efficient directory access protocol. The LDAP directory stores a list of users to which this application is assigned. Oracle9i can use Oracle Internet Directory as the directory service for authentication and authorization of enterprise users. (Note that enterprise user security requires Oracle Advanced Security.)

    The LDAP object orclDBApplicationContext (a subclass of groupOfUniqueNames) has been defined to store the application context values in the directory. The location of the application context object is described in Figure 12-1, which is based upon the Human Resources example.

    Note that an internal C function is required to retrieve the orclDBApplicationContext value. A list of application context values is returned to RDBMS.


    In this example, HR is the namespace, Title and Project are the attributes, and Manager and Promotion are the values. 

    Figure 12-1 Location of Application Context in LDAP Directory Information Tree (DIT)

    Text description of dirig010.gif follows
    Text description of the illustration dirig010.gif

    How Globally Initialized Application Context Works

    The administrator sets up the user's global application context values at the database and the directory.

    When a global user connects to the database, the Oracle Advanced Security option performs authentication to verify the identity of the user connecting to the database. Once the identification is completed, the user's global roles are retrieved from LDAP. Then the user's global application context is retrieved from LDAP. Thus, when the user logs on to the database, her global roles and initial application context are already set up.

    Example: Initializing Application Context Globally

    The initial application context for a user, such as department name, level (title) can be set up and stored in the LDAP directory. The values are retrieved during user login so that the context is set properly. In addition, any information related to the user is retrieved and stored in the application context namespace SYS_USER_DEFAULTS. The following example shows how this is done.

    1. Create an application context in the database.

    2. Create and add new entries in the LDAP directory.

      An example of the entries added to the LDAP directory follows. These entries create an attribute name Title with attribute value Manager for the application (namespace) HR, and assign usernames user1 and user2.

      changetype: add
      cn: OracleDBAppContext
      objectclass: top
      objectclass: orclContainer
      changetype: add
      cn: HR
      objectclass: top
      objectclass: orclContainer
      changetype: add
      cn: Title
      objectclass: top
      objectclass: orclContainer
      cn: Manager
      objectclass: top
      objectclass: groupofuniquenames
      objectclass: orclDBApplicationContext
      uniquemember: CN=user1,OU=Americas,O=Oracle,L=Redwoodshores,ST=CA,C=US
      uniquemember: CN=user2,OU=Americas,O=Oracle,L=Redwoodshores,ST=CA,C=US
    3. If an LDAP inetOrgPerson object entry exists for the user, the connection will also retrieve all the attributes from inetOrgPerson and assign them to the namespace SYS_LDAP_USER_DEFAULT. The following is an example of an inetOrgPerson entry:

      dn: cn=user1,ou=Americas,O=oracle,L=redwoodshores,ST=CA,C=US
      changetype: add
      objectClass: top
      objectClass: person
      objectClass: organizationalPerson
      objectClass: inetOrgPerson
      cn: user1
      sn: One
      givenName: User
      initials: UO
      title: manager, product development
      uid: uone
      telephoneNumber: +1 650 123 4567
      employeeNumber: 00001
      employeeType: full time
    4. Connect to the database.

      When user1 connects to a database that belongs to domain myDomain, user1 will have his Title set to Manager. Any information related to user1 will be retrieved from the LDAP directory. The value can be obtained using the syntax

      SYS_CONTEXT('namespace','attribute name') 

      For example:

      tmpstr1 VARCHAR2(30);
      tmpstr2 VARCHAR2(30);
      tmpstr1 = SYS_CONTEXT('HR','TITLE);
      tmpstr2 = SYS_CONTEXT('SYS_LDAP_USER_DEFAULT','telephoneNumber');
      DBMS_OUTPUT.PUT_LINE('Title is ' || tmpstr1);
      DBMS_OUTPUT.PUT_LINE('Telephone Number is ' || tmpstr2);

      The output of the above example is:

      Title is Manager
      Telephone Number is +1 650 123 4567

    Introduction to 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".


    Although you can define a policy against a table, you cannot select that table from within the policy that was defined against the table. 

    Multiple Policies for Each Table or View

    You can establish several policies for the same table or view. Suppose, for example, 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 policy is applied to any access of the table. You can use policy groups and a driving application context to partition fine-grained access control enforcement so that different policies apply, depending upon which application is accessing data. This eliminates the requirement for development groups to collude on policies and simplifies application development. You can also have a default policy group that always applies (for example, to enforce data separate by subscriber, in a hosting environment).

    Grouping of Security Policies

    Since multiple applications, with multiple security policies, can share the same table or view, it is important to identify those policies which should be in effect when the table or view is accessed.

    For example, in a hosting environment, Company A can host the BENEFIT table for Company B and Company C. The table is accessed by two different applications, HUMAN RESOURCES and FINANCE, with two different security policies. The HUMAN RESOURCES application authorizes users based on ranking in the company, and the FINANCE application authorizes users based on department. To integrate these two policies into the BENEFIT table would require joint development of policies between the two companies, which is not a feasible option. By defining an application context to drive the enforcement of a particular set of policies to the base objects, each application can implement a private set of security policies.

    To do this, you can organize security policies into groups. By referring to the application context, the Oracle server determines which group of policies should be in effect at runtime. The server enforces all the policies which belong to that policy group.

    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 Oracle's high performance features, such as dictionary caching and shared cursors.

    Default Security Policies

    While partitioning security policies by application is desirable, it is also useful to have security policies that are always in effect. In the previous example, a hosted application can always enforce data separation by subscriber_ID, whether you are using the Human Resources application or the Finance application. Default security policies allow developers to have base security enforcement under all conditions, while partitioning of security policies by application (using security groups) enables layering of additional, application-specific security on top of default security policies. To implement default security policies, you add the policy to the SYS_DEFAULT policy group.

    How Fine-Grained Access Control Works

    Fine-grained access control is based on dynamically modified statements, similar to the example described in this section. Suppose you want to attach to the ORDERS_TAB table the following security policy: "Customers can see only their own orders." The process is described in this section.

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


      A predicate is the WHERE clause and, more explicitly, a selection criterion 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.

    Upon execution, the function employs 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 "Introduction to Application Context Accessed Globally", as well as Oracle9i Supplied PL/SQL Packages and Types Reference. 

    How to Establish Policy Groups

    A policy group is a set of security policies which belong to an application. You can designate an application context (known as a driving context) to indicate the policy group in effect. Then, when the tables or views are accessed, the server looks up the driving context (which are also known as policy context) to determine the policy group in effect. It enforces all the associated policies which belong to that policy group.

    This section contains the following topics:

    The Default Policy Group: SYS_DEFAULT

    In the Oracle Policy Manager tree structure, the Fine-Grained Access Control Policies folder contains the Policy Groups folder. The Policy Groups folder contains an icon for each policy group, as well as an icon for the SYS_DEFAULT policy group.

    By default, all policies belong to the SYS_DEFAULT policy group by default. Policies defined in this group for a particular table or view will always be executed along with the policy group specified by the driving context. The SYS_DEFAULT policy group may or may not contain policies. If you attempt to drop the SYS_DEFAULT policy group, an error will be raised.

    If, to the SYS_DEFAULT policy group, you add policies associated with two or more objects, then each such object will have a separate SYS_DEFAULT policy group associated with it. For example, the EMP table in the SCOTT schema has one SYS_DEFAULT policy group, and the DEPT table in the SCOTT schema has a different SYS_DEFAULT policy group associated with it. These are displayed in the tree structure as follows:

      - policy1 (SCOTT/EMP)
      - policy3 (SCOTT/EMP)
      - policy2 (SCOTT/DEPT)


    Policy groups with identical names are supported. When you select a particular policy group, its associated schema and object name are displayed in the property sheet on the right-hand side of the screen 

    New Policy Groups

    When adding the policy to a table or view, you can use the DBMS_RLS.ADD_GROUPED_POLICY interface to specify the group to which the policy belongs. To specify which policies will be effective, you add a driving context using the DBMS_RLS.ADD_POLICY_CONTEXT interface. If the driving context returns an unknown policy group, an error is returned.

    If the driving context is not defined, then all policies are executed. Likewise, if the driving context is NULL, then policies from all policy groups are enforced. In this way, an application accessing the data cannot bypass the security setup module (which sets up application context) to avoid any applicable policies.

    You can apply multiple driving contexts to the same table or view, and each of them will be processed individually. In this way you can configure multiple active sets of policies to be enforced.

    Consider, for example, a hosting company that hosts Benefits and Financial applications, which share some database objects. Both applications are striped for hosting using a SUBSCRIBER policy in the SYS_DEFAULT policy group. Data access is partioned first by subscriber ID, then by whether the user is accessing the Benefits or Financial applications (determined by a driving context). Suppose that Company A, which uses the hosting services, wants to apply a custom policy which relates only to its own data access. You could add an additional driving context (such as COMPANY A SPECIAL) to ensure that the additional, special policy group is applied for Company A's data access only. You would not apply this under the SUBSCRIBER policy, since the policy relates only to Company A, and it is cleaner to segregate the basic hosting policy from other policies.

    Using Oracle Policy Manager to Establish Policy Groups

    Alternatively, with the Oracle Policy Manager graphical user interface, accessed from Oracle Enterprise Manager, you can create a policy group by using the DBMS_RLS.CREATE_POLICY_GROUP command line procedure.


    You may use Oracle Policy Manager to create a policy context. 

    How to Implement Policy Groups

    To create policy groups, the administrator must do two things:

    The following example shows how to perform these tasks.

    Step 1: Set Up a Driving Context

    Begin by creating a namespace for the driving context. For example:

    CREATE CONTEXT appsctx USING apps.apps_security_init;

    Create the package that administers the driving context. For example:

    CREATE OR REPLACE PACKAGE BODY apps.apps_security_init
    PROCEDURE setctx ( policy_group varchar2 )
    REM  Do some checking to determine the current application. 
    REM  You can check the proxy if using the proxy authentication feature.
    REM  Then set the context to indicate the current application.

    Define the driving context for the table APPS.BENEFIT.

    Step 2: Add a Policy to the Default Policy Group.

    Create a security function to return a predicate to divide the data by company.

    CREATE OR REPLACE FUNCTION by_company (schema varchar2, table varchar2)

    Since policies in SYS_DEFAULT are always executed (except for SYS, or users with the EXEMPT ACCESS POLICY system privilege), this security policy (named SECURITY_BY_COMPANY), will always be enforced regardless of the application running. This achieves the universal security requirement on the table: namely, that each company should see its own data, regardless of the application running. The function APPS.APPS_SECURITY_INIT.BY_COMPANY returns the predicate to make sure that you can only see your company's data.

    Step 3: Add a Policy to the HR Policy Group

    First, create the HR group:

    CREATE OR REPLACE FUNCTION hr.security_policy 
    		 RETURN 'SYS_CONTEXT(''ID'',''TITLE'') = ''MANAGER'' ';

    The following adds a policy named HR_SECURITY to the HR policy group. The function HR.SECURITY_POLICY returns the predicate to enforce HR's security on the table APPS.BENEFIT:

    Step 4: Add a Policy to the FINANCE Policy Group

    Create the FINANCE policy:

    CREATE OR REPLACE FUNCTION finance.security_policy 
    		 RETURN 'SYS_CONTEXT(''ID'',''DEPT'') = ''FINANCE'' ';

    Create a policy group named FINANCE:


    Add the FINANCE policy to the FINANCE group:

    'finance_security','finance', 'security_policy');

    As a result, when the database is accessed, the application initializes the driving context after authentication. For example, with the HR application:

    execute apps.security_init.setctx('HR');

    Validation of the Application Used to Connect

    In this regard, one factor is extremely important: The package implementing the driving context must correctly validate the application which is being used. Although the database always ensures that the package implementing the driving context sets context attributes (by checking the call stack), this fact cannot protect against poor or inadequate validation within the package.

    For example, in applications where database users or enterprise users are known to the database, the user needs EXECUTE privilege on the package which sets the driving context. Consider a user who knows that:

    In this situation, the user could pass to the driving context package an argument (BENEFITS) which would set the context to the more liberal BENEFITS policy group--even though this user will, in fact, access the HR application. In this way the user can bypass the intended, more restrictive security policy simply because the package does inadequate validation.

    If, by contrast, you implement proxy authentication with VPD, you can determine the identity of the middle tier (and thus, the application) which is actually connecting to the database on a user's behalf. In this way, the correct, per-application policy will be applied to mediate data access. For example, a developer using the proxy authentication feature could determine that the application (that is, the middle tier) connecting to the database is HRAPPSERVER. The package which implements the driving context can thus verify that the proxy_user in the user session is HRAPPSERVER before setting the driving context to use the HR policy group.

    In this case, when the following query is executed


    Oracle picks up policies from the default policy group (SYS_DEFAULT) and active namespace HR. The query is internally rewritten as follows:


    How to Add a Policy to a Table or View

    The DBMS_RLS package enables you to administer security policies. These procedures allow you to specify the table or view to which you are adding a policy, the name of the policy, the name of the policy group, 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. The package includes the following procedures:

    Table 12-2 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. 


    Use this procedure to enable (or disable) a policy you previously added to a table or view. 


    Use this procedure to create a policy group. 


    Use this procedure to add a policy to the specified policy group. 


    Use this procedure to add the context for the active application. 


    Use this procedure to drop a policy group. 


    Use this procedure to drop a policy which is a member of the specified group. 


    Use this procedure to drop the context for the application. 


    Use this procedure to enable a policy within a group. 


    Use this procedure to reparse the SQL statements associated with a refreshed policy. 

    See Also:

    Oracle9i Supplied PL/SQL Packages and Types Reference  

    Alternatively, you can use Oracle Policy Manager to administer security policies.

    How to Check for Policies Applied to Statement

    V$VPD_POLICY allows one to perform a dynamic view in order to check what policies are being applied to a SQL statement. When debugging, in your attempt to find which policy corresponds to a particular SQL statement, you should use the following table.

    Table 12-3 V$VPD_POLICY
    Column Name  Type 























    EXEMPT ACCESS POLICY System Privilege

    The system privilege EXEMPT ACCESS POLICY allows a user to be exempted from all fine-grained access control policies on any DML operation (SELECT, INSERT, UPDATE, and DELETE). This provides ease of use for such administrative activities as installation, and import and export of the database through a non-SYS schema.

    Also, regardless of the utility or application that is being used, if a user is granted the EXEMPT ACCESS POLICY privilege, then the user is exempt from VPD and Oracle Label Security policy enforcement. That is, the user will not have any VPD or Oracle Label Security policies applied to their data access.

    Since EXEMPT ACCESS POLICY negates the effect of fine-grained access control, this privilege should only be granted to users who have legitimate reasons for bypassing fine-grained security enforcement. This privilege should not be granted WITH ADMIN OPTION, so that users cannot pass on the EXEMPT ACCESS POLICY privilege to other users, and thus propagate the ability to bypass fine-grained access control.

    Fine-Grained Auditing

    This section describes fine-grained auditing in the context of Oracle9i auditing capabilities. It contains the following sections:

    Introduction to Standard Auditing and Fine-Grained auditing

    Standard Oracle9i auditing monitors privileges and objects, and provides triggers to monitor DML operations such as INSERT, UPDATE, and DELETE. By contrast, monitoring SELECT statements is facilitated by fine-grained auditing, which allows the monitoring of data access based on content. In this way, you can specify auditing conditions, and obtain more specific information about the environment and query result. This additional information helps you reconstruct audited events, and determine whether access rights have been violated.

    For example, a drug enforcement agency needs to track in detail access to its informants database. Likewise, a central tax authority needs to track access to tax returns in order to guard against employee snooping. Such agencies need enough detail to determine what data was accessed, not simply that the SELECT privilege was used by SCOTT on the INFORMANTS table.

    Standard Oracle9i Auditing Techniques

    Oracle provides over 170 configurable auditing options for accountability of users and servers. The Oracle9i audit facility allows you to audit database activity by statement, by use of system privilege, by object, or by user. For example, you can audit activity as general as all user connections to the database, and as specific as a particular user creating a table. You can audit only successful operations, or only unsuccessful operations. Auditing unsuccessful SELECT statements may find users attempting to access data that they are not privileged to see.

    Although auditing is highly configurable, standard audit options do not include a lot of detail about the audited events. Typically, an audit record identifies the user, the object accessed, the privilege used, whether the access was successful or unsuccessful, and a timestamp.

    You can use triggers to record customized information that is not automatically included in audit records. In this way, you can further design your own audit auditing conditions and audit record contents. For example, you could define a trigger on the EMP table to generate an audit record whenever an employee's salary is increased by more than 10 percent. This can include selected information, such as before and after values of SALARY:

    CREATE TRIGGER audit_emp_salaries
    for each row
    if (:new.salary> :old.salary * 1.10)
          insert into emp_salary_audit values (

    Furthermore, you can use event triggers to enable auditing options for specific users on login, and disable them upon logoff.

    In some cases, businesses may actually need to capture the statement executed as well as the result set from a query. Fine-grained auditing provides an extensible auditing mechanism that supports definition of key conditions for granular audit, as well as an event handler to actively alert administrators to misuse of data access rights.

    Oracle9i also gives you the option of sending audit records to the database audit trail or your operating system's audit trail, when the operating system is capable of receiving them. This option, coupled with the broad selection of audit options and the ability to customize auditing with triggers or stored procedures, gives you the flexibility of implementing an auditing scheme that suits your specific business needs.

    Fine-Grained Auditing Techniques

    A more granular level of auditing can be achieved with a fine-grained auditing mechanism. This employs simple, user-defined SQL predicates on table objects as conditions for selective auditing. During fetching, whenever policy conditions are met for returning a row from a query block, the query is audited.

    Fine-grained auditing allows organizations to define audit policies, which specify the data access conditions that trigger the audit event, and use a flexible event handler to notify administrators that the triggering event has occurred. For example, an organization may allow HR clerks to access employee salary information, but trigger an audit event when salaries are greater than $500K are accessed. The audit policy (where SALARY > 500000) is applied to the EMPLOYEES table through an audit policy interface (a PL/SQL package).

    For additional flexibility in implementation, organizations can employ a user-defined function to determine the policy condition, and identify an audit column to further refine the audit policy. For example, the function could allow unaudited access to any salary as long as the user is accessing data within the intranet, but audit access to executive-level salaries when they are accessed from the internet. A relevant column helps reduce the instances of false or unnecessary audit records, because the audit need only be triggered when a particular column is referenced in the query. For example, an organization may only wish to audit executive salary access when an employee name is accessed, because accessing salary information alone is not meaningful unless an HR clerk also selects the corresponding employee name.

    You can use the PLSQL package DBMS_FGA to administer these fine-grained audit policies. If any rows returned from a query block match the audit condition, these rows are identified as interested rows. An audit event entry, including username, SQL text, policy name, session id, timestamp, and other attributes, is inserted into the audit trail. You can optionally define an audit event handler to process the event. For example, the event handler could send an alert page to the administrator.

    The following example shows how you can audit SELECT statements on table hr.emp to monitor any query that accesses the salary column of the employee records which belong to sales department:

    object_schema => 'hr',
    object_name   => 'emp',
    policy_name   => 'chk_hr_emp',
    audit_condition => 'dept = ''SALES'' ', 
    audit_column => 'salary');

    Then, either of the following SQL statements will cause the database to log an audit event record.

    SELECT count(*) FROM hr.emp WHERE dept = 'SALES' and salary > 10000000;


    SELECT salary FROM hr.emp WHERE dept = 'SALES';

    With all the relevant information available, and a trigger-like mechanism to use, the administrator can define what to record and how to process the audit event.

    Consider what happens when the following commands are issued. After the fetch of the first interested row, the event is recorded, and the audit function SEC.LOG_ID is executed. The audit event record generated is stored in DBA_FGA_AUDIT_TRAIL, which has reserved columns for recording SQL text, policy name, and other information.

    /* create audit event handler */
    CREATE PROCEDURE sec.log_id (schema varchar2, table varchar2, policy varchar2) 
    UTIL_ALERT_PAGER(schema, table, policy);      -- send an alert note to my pager
    /* add the policy */
    object_schema => 'hr',
    object_name   => 'emp',
    policy_name   => 'chk_hr_emp',
    audit_condition => 'dept = ''SALES'' ', 
    audit_column => 'salary',
    handler_schema => 'sec',
    handler_module => 'log_id',
    enable               =>  TRUE);


    Fine-grained auditing is supported only with cost-based optimization. For query, using rule-based optimization, audit will check before applying row filtering, which could result in an unnecessary audit event trigger.  

    See Also:

    Oracle9i Supplied PL/SQL Packages and Types Reference 

    Enforcing Application Security

    This section contains information about enforcing application security. This section consists of the following topics:

    Use of Ad Hoc Tools a Potential Security Problem

    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.

    An application user can potentially 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 that, 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 through 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.

    Restricting Database Roles from SQL*Plus Users

    This section presents features that you may use in order to restrict database roles from SQL*Plus users and thus, prevent serious security problems. These features include the following:

    Limiting Roles Through PRODUCT_USER_PROFILE

    Oracle9i offers some capability to limit what roles a user accesses through an application, through 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 

    Using Stored Procedures to Encapsulate Business Logic

    Stored procedures encapsulate use of privileges with business logic so that privileges are only exercised in the context of a well-formed business transaction. For example, an application developer might create a procedure to update employee name and address in EMPLOYEES table, which enforces that the data can only be updated in normal business hours. Also, rather than grant an human resources clerk the UPDATE privilege on the EMPLOYEES table, a developer (or application administrator) may grant the privilege on the procedure only. Then, the human resources clerk can exercise the privilege only in the context of the procedures, and cannot update the EMPLOYEES table directly.

    Using Virtual Private Database for Highest Security

    Oracle9i enables you to enforce security, to a fine level of granularity, directly on tables or views by implementing virtual private database (VPD). Because security policies are attached directly to tables or views and automatically applied whenever a user accesses data, there is no way to bypass security.

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

    When a user directly or indirectly accesses a table or view associated with a VPD security policy, the server dynamically modifies the user's SQL statement. The modification is based on a WHERE condition (known as a predicate) returned by a function which implements the security policy. The 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. Within your PL/SQL package, you can embed a C or Java callout that can 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, for each group of users, or for each application.

    Application context enables you to securely access the attributes on which you base your security policies. For example, users with the position attribute of manager would have a different security policy than users with the position 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

    SELECT * FROM emp;

    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 within an application. This means that use of a different application will not bypass the security policy. Security can thus 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.

    It may be desirable to enforce different security policies depending on which application is accessing data. Consider a situation in which two applications, Order Entry and Inventory, both access the ORDERS table. You may want to have the Inventory application apply to the table a policy which limits access based on type of product. At the same time, you may want to have the Order Entry application apply to the same table a policy which limits access based on customer number.

    In this case, you must partition the use of fine-grained access by application. Otherwise, both policies would be automatically ANDed together--which is not the desired result. You can specify one or more policy groups, and a driving application context that determines which policy group is in effect for a given transaction. You can also designate default policies which always apply to data access. In a hosted application, for example, data access should always be limited by subscriber ID.

    See Also:

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

    Virtual Private Database and Oracle Label Security

    Virtual private database and Oracle Label Security are not enforced during DIRECT path export. Also, Virtual private database policies and Oracle Label Security policies cannot be applied to objects in schema SYS. As a consequence, the SYS user and users making a DBA-privileged connection to the database (for example, CONNECT/AS SYSDBA) do not have VPD or Oracle Label Security policies applied to their actions. Database administrators need to be able to administer the database. It would not suffice to export part of a table due to a VPD policy being applied.

    Database users who are granted the Oracle9i EXEMPT ACCESS POLICY privilege, directly or through a database role, are exempt from Virtual Private Database and Oracle Label Security enforcement. The users are exempt from Virtual Private Database and Oracle Label Security enforcement regardless of the export mode, application, or utility used to extract data from the database. EXEMPT ACCESS POLICY privilege is a powerful privilege and should be carefully managed.


    The EXEMPT ACCESS POLICY privilege does not affect the enforcement of object privileges such as SELECT, INSERT, UPDATE, and DELETE. These privileges are enforced even if a user has been granted the EXEMPT ACCESS POLICY privilege.  

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

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

    Master Index