Skip Headers

Oracle® Database Security Guide
10g Release 1 (10.1)

Part Number B10773-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

Using Virtual Private Database to Implement Application Security Policies

Oracle Database provides the necessary tools to build secure applications, such as Virtual Private Database (VPD), which is the combination of fine-grained access control and application context. Fine-grained access control enables you to associate security policies to database objects. Application context enables you to define and access application or database session attributes. VPD combines these two features, enabling you to enforce security policies to control access at the row level, based on application or session attributes.

This chapter introduces these features, and then explains how and why you would use them in the following topics:

About Virtual Private Database, Fine-Grained Access Control, and Application Context

Virtual Private Database (VPD) is the aggregation of server-enforced fine-grained access control and a secure application context in the Oracle database server. VPD enables you to build applications that enforce row-level security policies at the object level by dynamically appending predicates (WHERE clauses) to SQL statements that query data you want to protect. Application context is a feature that allows application developers to define, set, and access application attributes and then use these attributes to supply the predicate values for fine-grained access control policies. Local, or session-based, application contexts are stored in the UGA and are invoked each time an application user connects to the database. For multitiered environments where users access the database by way of connection pooling, non-session-based global application context, which stores the application context in the SGA, can be used. Although application context is an integral part of VPD, it can be implemented alone, without fine-grained access control. When application context is implemented alone, it can be used to access session information, such as the client identifier, to preserve user identity across multitiered environments.

The remainder of this chapter discusses how VPD works and introduces its main components--fine-grained access control and application context.

See Also:

Introduction to VPD

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

When a user directly or indirectly accesses a table, view, or synonym that is protected with a VPD 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. VPD policies can be applied to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.


Users need full table access to create table indexes. Consequently, a user who has privileges to maintain an index can see all the row data although the user does not have full table access under a regular query. To prevent this, apply VPD policies to INDEX statements.

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. Using policy functions over synonyms can substitute for maintaining a separate view for each user or class of users, saving substantial overhead in memory and processing resources.

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 Retail Division. When the user initiates the query


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

SELECT * FROM emp WHERE division = 'RETAIL';

Column-level VPD

Column-level VPD enables you to enforce row-level security when a security-relevant column is referenced in a query. You can apply column-level VPD to tables and views, but not to synonyms. By specifying the security-relevant column name with the sec_relevant_cols parameter of the DBMS_RLS.ADD_POLICY procedure, the security policy is applied whenever the column is referenced, explicitly or implicitly, in a query.

For example, users outside of the HR department typically are allowed to view only their own Social Security numbers. When a sales clerk initiates the query

SELECT fname, lname, ssn FROM emp;

the function implementing the security policy returns the predicate ssn='my_ssn' and the database rewrites the query and executes

SELECT fname, lname, ssn FROM emp WHERE ssn = 'my_ssn';
See Also:

"Adding Policies for Column-Level VPD" for information about how to add column-level VPD policies

Column-level VPD with Column Masking Behavior

If a query references a security-relevant column, then the default behavior of column-level VPD restricts the number of rows returned. With column masking behavior, which can be enabled by using the sec_relevant_cols_opt parameter of the DBMS_RLS.ADD_POLICY procedure, all rows display, even those that reference security relevant columns. However, the sensitive columns display as NULL values.

To illustrate this, consider the results of the sales clerk's query, described in the previous example. If column masking behavior is used, then instead of only seeing the row containing the sales clerk's own Social Security number, the clerk would see all rows from emp, but the ssn column values would be returned as NULL. Note that this behavior is fundamentally different from all other types of VPD policies, which return only a subset of rows.

See Also:

"Column Masking Behavior" for information about how to add column-level VPD policies with column masking behavior.

VPD Security Policies and Applications

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

Introduction to Fine-Grained Access Control

Fine-grained access control enables you to build applications that enforce security policies at a low level of granularity. (These policies are also referred to as VPD policies.) You can use it, for example, to restrict a customer who is accessing an Oracle database 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, view, or synonym on which you have based your application. Then, when a user enters a SELECT or a DML statement (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. You can also enforce security policies on index maintenance operations performed with the DDL statements CREATE INDEX and ALTER INDEX.

Features of Fine-Grained Access Control

Fine-grained access control provides the following capabilities:

Table-, View-, or Synonym-Based Security Policies

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


Attaching a policy to a table, view, or synonym 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, views, or synonyms, 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, view, or synonym means that you make the addition only once, rather than repeatedly adding it to each of your table-, view-, or synonym-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, View, or Synonym

You can establish several policies for the same table, view, or synonym. 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 collaborate on policies and simplifies application development. You can also have a default policy group that always applies (for example, to enforce data separated by subscriber, in a hosting environment).

Grouping of Security Policies

Since multiple applications, with multiple security policies, can share the same table, view, or synonym, it is important to identify those policies which should be in effect when the table, view, or synonym 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.

See Also:

The following topics for information about how to implement fine-grained access control:

About Creating a Virtual Private Database Policy with Oracle Policy Manager

To implement Virtual Private Database (VPD), developers can use the DBMS_RLS package to apply security policies to tables and views. They can also 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.

To create VPD policies, users must provide the schema name, table (or view or synonym) 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.

Oracle Policy Manager is also 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 row-level 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.

See Also:

Oracle Label Security Administrator's Guide for information about using Oracle Policy Manager

Introduction to Application Context

Application context enables you to define, set, and access application attributes that you can use as a secure data cache which is available in UGA and SGA.

Most applications contain the kind of information that can be used for access control. For example, in an order entry application customers can be limited to accessing their own orders (ORDER_NUMBER) and customer number (CUSTOMER_NUMBER). These can be used as security attributes.

As an additional example, consider a user running a human resources 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 affects what data the user can access throughout the session.

You use the SQL function SYS_CONTEXT to configure application context with the following syntax:

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

This section describes application context and how to use it. It includes:

Features of Application Context

Application context provides the following 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 Access to Predefined Attributes through the USERENV Namespace

Oracle database server 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 are 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 connects directly to the database, then she would not be able to access any data.

You can use the PROXY_USER attribute within VPD to ensure that users only access data through a particular middle-tier application. As a different approach, you can develop a secure application role. Then rather than each policy ensuring that users access the database through a specific proxy, the secure application role enforces this.

You can access predefined attributes through the USERENV application context, but you cannot change them. They are listed in Table 13-1.

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

SYS_CONTEXT('userenv', 'attribute')


The USERENV application context namespace replaces the USERENV function provided in earlier database releases.

See Also:
Table 13-1  Key to Predefined Attributes in USERENV Namespace
Predefined Attribute Meaning


Returns the fine-grained auditing cursor ID.


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


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


Returns the background job ID


User-defined client identifier for the session


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


Returns the bind variables for fine-grained auditing. Maximum length is 4K.


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 SQL text of the query that triggers fine-grained audit or row-level security (RLS) policy functions or event handlers. Only valid inside the function or event handler.


Returns 4K length substrings of the SQL query text that triggers fine-grained audit or row-level security (RLS) policy functions or audit event handlers. Only valid inside the RLS policy function or event handler. Maximum length is 32K. For example, if a user issued a 32 K length SQL statement, then CURRENT_SQL returns 0 to 4K, CURRENT_SQL1 returns 5K to 8K, CURRENT_SQL2 returns 9K to 12K, and so on.


Returns the length of the current SQL statement that triggers fine-grained audit or row-level security (RLS) policy functions or event handlers. Only valid inside the function or event handler.


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's rights procedure).


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


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 available auditing entry identifier. Incremented for every audit record for a SQL statement. Note: there can be more than one audit record for the same SQL statement.


Returns the external name of the database user


Returns the foreground job ID


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


Returns the user Login name from Oracle Internet Directory.


Returns the name of the host machine on which the database is running


Returns instance identification number of the current instance


Returns the name of the instance.


Returns the IP address (when available) of the machine from which the client is connected


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


Returns abbreviation for the language name


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


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


Returns the territory of the current session


Returns the currency symbol of the current session


Returns the 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 the operating system username of the client process that initiated the database session


Returns the invoker of row-level security policy functions.


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 hostname of machine on which the instance is running.


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 auditing session identifier


Returns the session number (different from the session ID).


Returns available auditing statement identifier. Incremented once for every SQL statement audited in a session.


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

Externalized Application Contexts

Many applications store attributes used for fine-grained access control within a database metadata table. For example, an EMPLOYEES table could include cost center, title, signing authority, and other information useful for fine-grained access control. Organizations also centralize user information for user management and access control in LDAP-based directories, such as Oracle Internet Directory. 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 then used to initialize an application context.


Enterprise User Security is a feature of Oracle Advanced Security.

See Also:

Ways to Use Application Context with Fine-Grained Access Control

To simplify security policy implementation, you can use application context within a fine-grained access control function.

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 on 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 in the session when you need it.

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

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

You can use application context to return the correct security policy, enforced through a predicate here.

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 preceding example, 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 predicate, which prescribes that 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 executes differently for each customer because the customer number is evaluated at execution time. This value is different for every customer. Use of application context in this case provides optimum performance, as well as row-level security.

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

See Also:

"Examples: Application Context Within a Fine-Grained Access Control Function" which provides a code example.

Introduction to Global Application Context

In many application architectures, the middle tier application is responsible for managing session pooling for application users. Users authenticate themselves to the application, which uses a single identity to log in to the database and maintains all the connections. In this environment, it is not possible to maintain application attributes using session-dependent application context (local 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.
  • Oracle Connection Manager, a router provided with Oracle Net Services, cannot be used with global application context.

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.

Potentially, an application user can 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 SQL*Plus Users from Using Database Roles

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


DBAs can use PRODUCT_USER_PROFILE to disable certain SQL and SQL*Plus commands in the SQL*Plus environment for each user. SQL*Plus, not the Oracle Database, 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 preceding 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 preceding 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

Use 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 the EMPLOYEES table, which enforces that the data can only be updated in normal business hours. Also, rather than grant a 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.

Use Virtual Private Database for Highest Security

VPD provides the benefit of strong security policies, which apply directly to data. When you use VPD, you 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.

See Also:

Virtual Private Database and Oracle Label Security Exceptions and Exemptions

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 the SYS schema. 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. The database user SYS is thus always exempt from VPD or Oracle Label Security enforcement, regardless of the export mode, application, or utility used to extract data from the database. However, SYSDBA actions can be audited by enabling such auditing upon installation and specifying that this audit trail be stored in a secure location in the operating system.

Similarly, database users granted the EXEMPT ACCESS POLICY privilege, either directly or through a database role, are exempt from VPD enforcements. They are also exempt from some Oracle Label Security policy enforcement controls -- READ_CONTROL and CHECK_CONTROL -- regardless of the export mode, application, or utility used to access the database or update its data. However, the following policy enforcement options remain in effect even when EXEMPT ACCESS POLICY is granted:

EXEMPT ACCESS POLICY is a very powerful privilege and should be carefully managed. It is inadvisable to grant this privilege WITH ADMIN OPTION because very few users should have this exemption.

  • 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.
  • The SYS_CONTEXT values that VPD uses are not propagated to secondary databases for failover.

See Also:

Oracle Label Security Administrator's Guide

User Models and Virtual Private Database

Whether the user is a database user or an application user unknown to the database, Oracle provides different ways in which applications can enforce fine-grained access control for each user.

For applications in which the application users are also database users, VPD enforcement is relatively simple. Users connect to the database, and the application sets up application contexts for each session. Each session is initiated under a different username, so that it is simple to enforce different fine-grained access control conditions for different users. This is also possible when using proxy authentication, because each session in OCI or thick JDBC is a distinct database session, and has its own application context.

When proxy authentication is integrated with Enterprise User Security, user roles and other attributes can be retrieved from Oracle Internet Directory to enforce VPD. (In addition, globally initialized application context can also be retrieved from the directory.)

For applications in which a single user (for example, One Big Application User) connects to the database on behalf of all users, it is possible to have fine-grained access control for each user. An application developer can create a global application context attribute to represent the application user (for example, REALUSER). Although all database sessions and audit records are initiated as One Big Application User, each session can have attributes that vary, depending on who the real end user is. This model works best for applications that have a limited number of users and where sessions are not reused. In this model, the option to use roles and perform database auditing is diminished because each session is created as the same database user.

Web-based applications typically have hundreds if not thousands of users. 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. To provide scalability, Web-based applications typically set up and reuse connections instead of having different sessions for each user. 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.

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