How Oracle Virtual Private Database Works with Other Oracle Features

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.

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.

Related Topics

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.

Related Topics

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.

Related Topics

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.

Related Topics

Oracle Virtual Private Database and Oracle Label Security

You can use Oracle Virtual Private Database with Oracle Label Security, but 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.

You must perform the following actions:

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

Related Topics

Oracle Virtual Private Database and Oracle Label Security Exceptions

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

These security exceptions are as follows:

Related Topics

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"

Oracle Virtual Private Database Policies and Oracle Flashback Time Travel

Oracle Virtual Private Database policies do not automatically work with Oracle Flashback Time Travel.

After you create an Oracle Virtual Private Database (VPD) policy for a table, consider creating an equivalent policy for the Flashback Archive history table. The following example demonstrates how to do so.

  1. Create a temporary VPD administrative user.

    CREATE USER sysadmin_vpd IDENTIFIED BY password CONTAINER = CURRENT;
GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE TO sysadmin_vpd;
GRANT EXECUTE ON DBMS_SESSION TO sysadmin_vpd;
GRANT EXECUTE ON DBMS_FLASHBACK, DBMS_FLASHBACK_ARCHIVE TO sysadmin_vpd;
GRANT EXECUTE ON DBMS_RLS TO sysadmin_vpd;
GRANT UPDATE ON SCOTT.EMP TO sysadmin_vpd;

  2. Connect to the PDB as the sysadmin_vpd user.

    connect sysadmin_vpd@pdb_name
Enter password: password
Connected.

  3. Create the VPD function.

    For example, the following function shows only rows with department number (deptno) 30 to users other than user SCOTT:

    CREATE OR REPLACE FUNCTION emp_policy_func (
 v_schema IN VARCHAR2,
 v_objname IN VARCHAR2)

RETURN VARCHAR2 AS
condition VARCHAR2 (200);

BEGIN
 condition := 'deptno=30';
 IF sys_context('userenv', 'session_user') IN ('SCOTT') THEN
   RETURN NULL;
 ELSE
   RETURN (condition);
 END IF;
END emp_policy_func;
/

  4. Create the following VPD procedure to attach the emp_policy_func function to the SCOTT.EMP table.

    BEGIN
 DBMS_RLS.ADD_POLICY (
  object_schema     => 'scott',
  object_name       => 'emp',
  policy_name       => 'emp_policy',
  function_schema   => 'sysadmin_vpd',
  policy_function   => 'emp_policy_func',
  policy_type       =>  dbms_rls.dynamic);
END;
/

  5. Create the following test user and grant privileges, including those related to Flashback Archive.

    CREATE USER test IDENTIFIED BY password;
GRANT CREATE SESSION TO test;
GRANT CONNECT, RESOURCE TO test;
GRANT SELECT ON SCOTT.EMP TO test;
GRANT FLASHBACK ARCHIVE ON ftest TO test;
GRANT EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO test;
GRANT EXECUTE ON DBMS_FLASHBACK TO test;
GRANT FLASHBACK ANY TABLE TO PUBLIC;
GRANT EXECUTE ON emp_policy_func TO PUBLIC;

  6. Enable the SCOTT.EMP table for flashback archive, and for transactions

    ALTER TABLE SCOTT.EMP FLASHBACK ARCHIVE;

  7. Perform an update to the SCOTT.EMP table.

    UPDATE SCOTT.EMP SET SAL=SAL+1;
COMMIT;

  8. Put the preceding procedure to sleep for 60 seconds.

    EXEC DBMS_LOCK.SLEEP(60);

  9. Connect as user test.

    connect test@pdb_name
Enter password: password
Connected.

  10. Perform the following query to show only rows that have deptno=30, per the VPD policy:

    SELECT EMPNO,DEPTNO,SAL FROM SCOTT.EMP;

    The VPD policy is not working because all rows are shown.

    SELECT EMPNO,DEPTNO,SAL FROM SCOTT.EMP AS OF TIMESTAMP SYSDATE-1;

  11. Connect as user sysadmin_vpd.

    connect sysadmin_vpd@pdb_name
Enter password: password
Connected.

  12. Find the object ID for the EMP table.

    SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='EMP';

  13. Define a similar VPD policy on the SYS_FBA_HIST_object_id_of_EMP_table table. This table is internally created by Flashback Archive

    BEGIN
 DBMS_RLS.ADD_POLICY (
  object_schema     => 'scott',
  object_name       => 'sys_fba_hist_object_id_of_EMP_table',
  policy_name       => 'emp_hist_policy',
  function_schema   => 'sysadmin_vpd,
  policy_function   => 'emp_policy_func',
  policy_type       =>  dbms_rls.dynamic);
END;
/

  14. Connect as the test user.

    connect test@pdb_name
Enter password: password
Connected.

  15. Test the policy again:

    SELECT EMPNO,DEPTNO,SAL FROM SCOTT.EMP AS OF TIMESTAMP SYSDATE-1;

    Now the VPD policy works, because the query only shows rows with deptno=30.

  16. Connect as a user who can drop user accounts.

    For example:

    connect sec_admin@pdb_name
Enter password: password
Connected.

  17. Drop the sysadmin_vpd user and its objects as follows:

    DROP USER sysadmin_vpd CASCADE;

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:

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 Virtual Private Database and JSON

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

You cannot create VPD policies on JASN relational duality views. Any attempt to do so results in an ORA-42623: Virtual Private Database (VPD) cannot be applied on JSON Relational Duality Views error. However, you can create VPD policies on base tables of JSON relational duality views.

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