How Oracle Virtual Private Database Works with Other Oracle Features

The script content on this page is for navigation purposes only and does not alter the content in any way.

You should be aware of the impact of using Oracle Virtual Private Database with other Oracle features.

• Oracle Virtual Private Database Policies with Editions
  You should be aware of how to use Oracle VPD with editions.

• SELECT FOR UPDATE Statement in User Queries on VPD-Protected Tables
  As a general rule, users should not include the FOR UPDATE clause when querying Virtual Private Database-protected tables.

• Oracle Virtual Private Database Policies and Outer or ANSI Joins
  Oracle Virtual Private Database rewrites SQL by using dynamic views.

• Oracle Virtual Private Database Security Policies and Applications
  An Oracle Virtual Private Database security policy is applied within the database itself, rather than within an application.

• Automatic Reparsing for Fine-Grained Access Control Policies Functions
  Queries against objects enabled with fine-grained access control run the policy function so that the most current predicate is used for each policy.

• Oracle Virtual Private Database Policies and Flashback Queries
  Operations on the database use the most recently committed data available.

• Oracle Virtual Private Database and Oracle Label Security
  You can use Oracle Virtual Private Database with Oracle Label Security, but when you do, you should be aware of security exceptions.

• Export of Data Using the EXPDP Utility access_method Parameter
  Be aware if you try to export data from objects that have VPD policies defined on them.

• User Models and Oracle Virtual Private Database
  You can use Oracle Virtual Private Database in several types of user models.

Oracle Virtual Private Database Policies with Editions

You should be aware of how to use Oracle VPD with editions.

If you are preparing an application for edition-based redefinition, and you cover each table that the application uses with an editioning view, then you must move the Virtual Private Database polices that protect these tables to the editioning view.

When an editioned object has a Virtual Private Database policy, then it applies in all editions in which the object is visible. When an editioned object is actualized, any VPD policies that are attached to it are newly attached to the new actual occurrence. When you newly apply a VPD policy to an inherited editioned object, this action will actualize it.

SELECT FOR UPDATE Statement in User Queries on VPD-Protected Tables

As a general rule, users should not include the FOR UPDATE clause when querying Virtual Private Database-protected tables.

The Virtual Private Database technology depends on rewriting the user’s query against an inline view that includes the VPD predicate generated by the VPD policy function. Because of this, the same limitations on views also apply to VPD-protected tables. If a user’s query against a VPD-protected table includes the FOR UPDATE clause in a SELECT statement, in most cases, the query may not work. However, the user’s query may work in some situations if the inline view generated by VPD is sufficiently simple.

Oracle Virtual Private Database Policies and Outer or ANSI Joins

Oracle Virtual Private Database rewrites SQL by using dynamic views.

For SQL that contains outer join or ANSI operations, some views may not merge and some indexes may not be used. This problem is a known optimization limitation. To remedy this problem, rewrite the SQL to not use outer joins or ANSI operations.

Oracle Virtual Private Database Security Policies and Applications

An Oracle Virtual Private Database security policy is applied within the database itself, rather than within an application.

Hence, a user trying to access data by using a different application cannot bypass the Oracle Virtual Private Database security policy. Another advantage of creating the security policy in the database is that you maintain it in one central place, rather than maintaining individual security policies in multiple applications. Oracle Virtual Private Database provides stronger security than application-based security, at a lower cost of ownership.

You may want to enforce different security policies depending on the application that 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 use a policy that limits access based on type of product. At the same time, you may want to have the Order Entry application use a policy that 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 concatenated together, which may not be the result that you want. You can specify two 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 that always apply to data access. In a hosted application, for example, data access should be limited by subscriber ID.

Automatic Reparsing for Fine-Grained Access Control Policies Functions

Queries against objects enabled with fine-grained access control run the policy function so that the most current predicate is used for each policy.

For example, in the case of a time-based policy function, in which queries are only allowed between 8:00 a.m. and 5:00 p.m., a cursor execution parsed at noon runs the policy function at that time, ensuring that the policy is consulted again for the query. Even if the curser was parsed at 9 a.m., when it runs later on (for example, at noon), then the Virtual Private Database policy function runs again to ensure that the execution of the cursor is still permitted at the current time (noon). This ensures that the security check it must perform is the most recent.

Automatic re-execution of the Virtual Private Database policy function does not occur when you set the DBMS_RLS.ADD_POLICY setting STATIC_POLICY to TRUE while adding the policy. This setting causes the policy function to return the same predicate.

Oracle Virtual Private Database Policies and Flashback Queries

Operations on the database use the most recently committed data available.

The flashback query feature enables you to query the database at some point in the past.

To write an application that uses flashback query, you can use the AS OF clause in SQL queries to specify either a time or a system change number (SCN), and then query against the committed data from the specified time. You can also use the DBMS_FLASHBACK PL/SQL package, which requires more code, but enables you to perform multiple operations, all of which refer to the same point in time.

However, if you use flashback query against a database object that is protected with Oracle Virtual Private Database policies, then the current policies are applied to the old data. Applying the current Oracle Virtual Private Database policies to flashback query data is more secure because it reflects the most current business policy.

Oracle Virtual Private Database and Oracle Label Security

You can use Oracle Virtual Private Database with Oracle Label Security, but when you do, you should be aware of security exceptions.

• Using Oracle Virtual Private Database to Enforce Oracle Label Security Policies
  Oracle Virtual Private Database policies provide column or row-level access control based on Oracle Label Security user authorizations.

• Oracle Virtual Private Database and Oracle Label Security Exceptions
  Be aware of the security exceptions when you use Oracle Virtual Private Database and Oracle Label Security.

Using Oracle Virtual Private Database to Enforce Oracle Label Security Policies

Oracle Virtual Private Database policies provide column or row-level access control based on Oracle Label Security user authorizations.

In general, you must perform the following steps:

1. When you create the Oracle Label Security policy, do not apply the policy to the table that you want to protect. (The Virtual Private Database policy that you create handles this for you.) In the SA_SYSDBA.CREATE_POLICY procedure, set the default_options parameter to NO_CONTROL.

2. Create the Oracle Label Security label components and authorize users as you normally would.

3. When you create the Oracle Virtual Private Database policy, do the following:

   • In the PL/SQL function you create for the policy, use the Oracle Label Security DOMINATES function to compare the authorization of the user with the label that you created in Step 2. The DOMINATES function determines if the user authorization is equal to, or if it is more sensitive than, the label used in the comparison. If the user authorization passes, then the user is granted access to the column. Otherwise, the user is denied access.

   • In the Virtual Private Database policy definition, apply this function to the table that you want to protect. In the DBMS_RLS.ADD_POLICY procedure, use the sensitive column (SEC_RELEVANT_COLS parameter) and column masking (SEC_RELEVANT_COLS_OPT parameter) functionality to show or hide columns based on Oracle Label Security user authorizations.

Oracle Virtual Private Database and Oracle Label Security Exceptions

Be aware of the security exceptions when you use Oracle Virtual Private Database and Oracle Label Security.

These security exceptions are as follows:

• When you are exporting data, Oracle Virtual Private Database and Oracle Label Security policies are not enforced during a direct path export operation. In a direct path export operation, Oracle Database reads data from disk into the buffer cache and transfers rows directly to the Export client.

• You cannot apply Oracle Virtual Private Database policies and Oracle Label Security policies to objects in the SYS schema. The SYS user and users making a DBA-privileged connection to the database (for example, CONNECT/AS SYSDBA) do not have Oracle Virtual Private Database or Oracle Label Security policies applied to their actions. The database user SYS is thus always exempt from Oracle Virtual Private Database or Oracle Label Security enforcement, regardless of the export mode, application, or utility used to extract data from the database.

  However, you can audit SYSDBA actions by enabling auditing upon installation and specifying that this audit trail be stored in a secure location in the operating system. You can also closely monitor the SYS user by using Oracle Database Vault.

• Constrains are exempt from security policies. Security policies do not apply to the interal queries that are executed to validate constraints.

• Database users who were granted the EXEMPT ACCESS POLICY privilege, either directly or through a database role, are exempt from Oracle Virtual Private Database enforcements. The system privilege EXEMPT ACCESS POLICY allows a user to be exempted from all fine-grained access control policies on any SELECT or DML operation (INSERT, UPDATE, and DELETE). This provides ease of use for administrative activities, such as installation and import and export of the database, through a non-SYS schema.

  However, the following policy enforcement options remain in effect even when EXEMPT ACCESS POLICY is granted:

  • INSERT_CONTROL, UPDATE_CONTROL, DELETE_CONTROL, WRITE_CONTROL, LABEL_UPDATE, and LABEL_DEFAULT

  • If the Oracle Label Security policy specifies the ALL_CONTROL option, then all enforcement controls are applied except READ_CONTROL and CHECK_CONTROL.

  Because EXEMPT ACCESS POLICY negates the effect of fine-grained access control, you should only grant this privilege to users who have legitimate reasons for bypassing fine-grained access control enforcement. Do not grant this privilege using the WITH ADMIN OPTION. If you do, users could pass the EXEMPT ACCESS POLICY privilege to other users, and thus propagate the ability to bypass fine-grained access control. <div class="infoboxnote" markdown="1">

  **Note:**
  
  - 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 was granted the `EXEMPT ACCESS POLICY` privilege.
  
  - The `SYS_CONTEXT` values that Oracle Virtual Private Database uses are not propagated to secondary databases for failover.
  
  </div>
  

Export of Data Using the EXPDP Utility access_method Parameter

Be aware if you try to export data from objects that have VPD policies defined on them.

If you try to use the Oracle Data Pump Export (EXPDP) utility with the access_method parameter set to direct_path to export data from a schema that contains an object that has a Virtual Private Database policy defined on it, then an ORA-31696 error message may appear and the export operation will fail.

The error message is as follows:

ORA-31696: unable to export/import TABLE_DATA:"schema.table" using client specified DIRECT_PATH method

This problem occurs when you perform a schema-level export or a full database export, which requires the EXP_FULL_DATABASE role. To perform an export with VPD policies in place using the access_method=direct_path parameter, the exporting user must be granted the system privilege EXEMPT ACCESS POLICY. EXEMPT ACCESS POLICY bypasses Virtual Private Database policies. Note that the EXP_FULL_DATABASE role does not include the EXEMPT ACCESS POLICY system privilege.

To find the underlying problem, try the EXPDP invocation again, but do not set the access_method parameter to direct_path. Instead, use either automatic or external_table. The underlying problem could be a permissions problem, for example:

ORA-39181: Only partial table data may be exported due to fine grain access control on "schema_name"."object_name"

User Models and Oracle Virtual Private Database

You can use Oracle Virtual Private Database in several types of user models.

These user models are as follows:

• Application users who are also database users. Oracle Database enables applications to enforce fine-grained access control for each user, regardless of whether that user is a database user or an application user unknown to the database. When application users are also database users, Oracle Virtual Private Database enforcement works as follows: users connect to the database, and then the application sets up application contexts for each session. (You can use the default USERENV application context namespace, which provides many parameters for retrieve different types of user session data.) As each session is initiated under a different user name, it can enforce different fine-grained access control conditions for each user.

• Proxy authentication using OCI or JDBC/OCI. Proxy authentication permits different fine-grained access control for each user, because each session (OCI or JDBC/OCI) is a distinct database session with its own application context.

• Proxy authentication integrated with Enterprise User Security. If you have integrated proxy authentication by using Enterprise User Security, you can retrieve user roles and other attributes from Oracle Internet Directory to enforce Oracle Virtual Private Database policies. (In addition, globally initialized application context can also be retrieved from the directory.)

• Users connecting as One Big Application User. Applications connecting to the database as a single user on behalf of all users can have fine-grained access control for each user. The user for that single session is often called One Big Application User. Within the context of that session, however, an application developer can create a global application context attribute to represent the individual application user (for example, REALUSER). Although all database sessions and audit records are created for One Big Application User, the attributes for each session can vary, depending on who the end user is. This model works best for applications with a limited number of users and no reuse of sessions. The scope of roles and database auditing is diminished because each session is created as the same database user.

• Web-based applications. Web-based applications typically have hundreds of users. Even when there are persistent connections to the database, supporting data retrieval for many user requests, these connections are not specific to particular Web-based users. Instead, Web-based applications typically set up and reuse connections, to provide scalability, rather than having different sessions for each user. For example, when Web users Jane and Ajit connect to a middle tier application, it may establish a single database session that it uses on behalf of both users. Typically, neither Jane nor Ajit is known to the database. The application is responsible for switching the user name on the connection, so that, at any given time, it is either Jane or Ajit using the session.

  Oracle Virtual Private Database helps with connection pooling by allowing multiple connections to access more than one global application context. This ability makes it unnecessary to establish a separate application context for each distinct user session.

The following table summarizes how Oracle Virtual Private Database applies to user models.

User Model Scenario	Individual Database Connection	Separate Application Context per User	Single Database Connection	Application Must Switch User Name
Application users are also database users	Yes	Yes	No	No
Proxy authentication using OCI or JDBC/OCI	Yes	Yes	No	No
Proxy authentication integrated with Enterprise User Security1	No	No	Yes	Yes
One Big Application User	No	No2	No	Yes2
Web-based applications	No	No	Yes	Yes

Related Topics

• Oracle Database Development Guide for detailed information about editions
• Oracle Database SQL Language Reference for more information about the restrictions of the FOR UPDATE clause in the SELECT statement
• Tutorial: Implementing an Oracle Virtual Private Database Policy Group
• Oracle Database Development Guide for more information about the flashback query feature and how to write applications that use it
• Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_FLASHBACK PL/SQL package
• Oracle Label Security Administrator’s Guide for more information about the dominance functions
• Oracle Database Utilities for more information about direct path export operations
• Global Application Contexts

1. User roles and other attributes, including globally initialized application context, can be retrieved from Oracle Internet Directory to enforce Oracle Virtual Private Database. ↩

2. Application developers can create a global application context attribute representing individual application users (for example, REALUSER), which can then be used for controlling each session attributes, or for auditing. ↩ ↩²