OracleAppsDBAKK1: Master Note For Oracle Virtual Private Database ( VPD / FGAC / RLS ) (Doc ID 1352641.1)

his Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 12.1.0.2B1 [Release 8.1.7 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 21-Feb-2013***

PURPOSE

This Master Note is intended to provide an index and references to the most frequently used My Oracle Support Notes with respect to Oracle Virtual Private Database. This Master Note is subdivided into categories to allow for easy access and reference to notes that are applicable to your area of interest.

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

SCOPE

This document is meant for use as a guide by those who are configuring or managing/troubleshooting the Oracle Virtual Private Database.

DETAILS

Oracle Virtual Private Database Concepts and Overview

Oracle Virtual Private Database (VPD) allows you to create security policies to control the access at the row and column level. These security policies are enforced by the database rather than an application, which means that use of a different application will not bypass the security policy.

Oracle adds dynamically and transparently a WHERE clause( predicate ) to a SQL statement that is executed against the object (table, view or synonym) to which a VPD policy was applied. The predicate (WHERE clause) is returned by a custom function which implements the security policy. It is your responsability to write correctly this function so that it will return the expected predicates in various scenarios.

To make the implementation of a security policy easier, you have the option of using application context within a fine grained access control(FGAC) function. Virtual private database (VPD) is the term used for the combination of fine grained access control(FGAC) with the application contexts.
The VPD policies can be applied to the following statements:

SELECT
INSERT
UPDATE
INDEX
DELETE

Note: The VPD policies do not restrict in any way the DDL statements

Note: Database users who were granted the EXEMPT ACCESS POLICY privilege, either directly or through a database role and the users who are connecting as SYSDBA are exempt from Oracle Virtual Private Database enforcements.

Oracle Virtual Private Database Configuration and Administration

The VPD policies are created and maintained via the package DBMS_RLS.

Row level VPD

Let's assume that we want to allow database users KING and QUEEN to see only the rows pertaining to a certain department from USER1.EMP. We can create the following (very simple) policy function in which we establish what are the departments these users have access to :

create or replace function pol_emp(obj_owner in varchar2, obj_name in varchar2) return varchar2
as
   deptno number;
   predicate varchar2(200);
begin

   predicate := '1=2';

   if SYS_CONTEXT('userenv','POLICY_INVOKER')= 'KING' then
     predicate := 'deptno='||10;
   end if;

   if SYS_CONTEXT('userenv','POLICY_INVOKER') = 'QUEEN' then
     predicate := 'deptno='||20;
   end if;

   return predicate;
end;
/

We then create a policy for the access to table USER1.EMP and specify what is the policy function, how that policy should work, etc.:

BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'USER1',
object_name => 'emp',
policy_name => 'secure_emp',
policy_function => 'pol_emp',
statement_types => 'SELECT');
END;
/

When user KING performs the following query:

SELECT * FROM USER1.EMP;

... the VPD policy dynamically appends the statement with the WHERE clause(predicate) returned by the above function:

SELECT * FROM USER1.EMP
WHERE deptno = 10;

You can see more examples of using VPD in the following notes:

Note 67977.1 - Oracle8i Fine Grained Access Control - Worked Examples
Note 281829.1 - Evolution of Fine Grain Access Control FGAC Feature From 8i To 10g

Column level VPD

There are cases when only certain columns are sensitive and as such it is needed to restrict the access only when these columns are queried or modified. By specifying the sensitive column name with the sec_relevant_cols parameter of the DBMS_RLS.ADD_POLICY procedure, the security policy is applied(and the number of the returned rows is reduced) whenever the column is referenced. The policy creation procedure should specify the sec_relevant_cols parameter:

BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'USER1',
object_name => 'emp',
policy_name => 'secure_emp',
policy_function => 'pol_emp',
statement_types => 'SELECT',
sec_relevant_cols=>'COMM');
END;
/

The policy created with the above statement will not rewrite the query(will not add a predicate) if column COMM is not used explicitly or implicitly(using the * wildcard). See Note 250795.1 for more details.

Column masking VPD

This is a variant of Column level VPD which allows you to specify that the VPD policy should hide ( mask ) the sensitive data rather than removing entire rows from the result set. One can obtain this behaviour by specifying the sensitive column names with the sec_relevant_cols parameter and by setting parameter sec_relevant_cols_opt to DBMS_RLS.ALL_ROWS:

BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'USER1',
object_name => 'emp',
policy_name => 'secure_emp',
policy_function => 'pol_emp',
statement_types => 'SELECT',
sec_relevant_cols=>'COMM,SAL',
sec_relevant_cols_opt=>DBMS_RLS.ALL_ROWS);
END;
/

When columns COMM or SAL are referenced in a query their values will be replaced with NULLs for those rows that are excluded by the predicate.

Views [ALL|DBA|USERS]_POLICIES contain the necessary information to determine what
policy function(s) will be applied when a statement is issued and the source for the policy function may be reviewed in views [ALL|DBA|USERS]_SOURCE:

SQL> select * from dba_policies where object_owner='USER1' and object_name='EMP';


OBJECT_OWN OBJECT_NAM POLICY_GROUP POLICY_NAM PF_OWNER PACKAGE FUNCTION SEL INS UPD DEL IDX CHK ENA STA POLICY_TYPE LON
---------- ---------- --------------- ---------- ------------ ---------- ------------------------------ --- --- --- --- --- --- --- --- ------------------------ ---
USER1 EMP SYS_DEFAULT SECURE_EMP SYS POL_EMP YES NO NO NO NO NO YES NO DYNAMIC NO

SQL>

SQL> set linesize 200
SQL> select text from dba_source where name='POL_EMP';

TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
function pol_emp(obj_owner in varchar2, obj_name in varchar2) return varchar2
as
deptno number;
predicate varchar2(200);
begin

predicate := '1=2';

if SYS_CONTEXT('userenv','POLICY_INVOKER')= 'KING' then
predicate := 'deptno='||10;
end if;

TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

if SYS_CONTEXT('userenv','POLICY_INVOKER') = 'QUEEN' then
predicate := 'deptno='||20;
end if;
end;

16 rows selected.

Oracle Virtual Private Database HOWTOs

Note 67977.1   - Oracle8i Fine Grained Access Control - Worked Examples
Note 281829.1 - Evolution of Fine Grain Access Control FGAC Feature From 8i To 10g
Note 967042.1 - How to Investigate Query Performance Regressions Caused by VPD (FGAC) Predicates?
Note 250795.1 - 10g: Policy Enforced Only When the Relevant Column is Queried in Any Way
Note 281970.1 - 10g: Enhancement on STATIC_POLICY with POLICY_TYPE Behaviors in DBMS_RLS.ADD_POLICY Procedure
Note 315687.1 - 10g: What Is INDEX statement_type Used For In By DBMS_RLS Policies ?
Note 119335.1 - How To Solve the Problem of Circular Row Level Policies
Note 174799.1 - How to Bypass Fine-Grained Security Enforcement
Note 69573.1   - How to Determine Active Context (DBMS_SESSION.LIST_CONTEXT)
Note 162914.1 - How to Skip Tables when Exporting a User or an Entire Database
Note 99250.1   - Understanding Fine-Grained Access Control (DBMS_RLS) on INSERT
Note 174368.1 - Policies on Synonyms
Note 125511.1 - How to Generate Tracing when using Fine Grained Access Control
Note 155477.1 - Parameter DIRECT: Conventional Path Export Versus Direct Path Export
Note 187239.1 - Execution plan may change when you use Fine Grained Access Control (FGAC)
Note 1637312.1 Example: How to Move a RLS Policy Between Databases Using Datapump

Oracle Virtual Private Database Troubleshooting

NOTE 386755.1 - How To Implement A VPD Policy Working With Materialized Views to Avoid ORA-30372
Note 69401.1 - How to resolve ORA-28110 or ORA-28112 on SELECT or DML
Note 100130.1 - ORA-1031 when setting Attribute via DBMS_SESSION.SET_CONTEXT
Note 331862.1 - ORA-28113 when a Policy Predicate is Fetched from a Context
Note 113970.1 - SELECT Statement Hangs when using Fine Grained Access Control
Note 168056.1 - Select on Table With Policy Defined on it Fails With ORA-28110
Note 175658.1 - RLS Policy Function Appears to Run in a New Session
Note 277606.1 - How to Prevent EXP-00079 or EXP-00080 Warning (Data in Table xxx is Protected) During Export
Note 567521.1 - ORA-28112: Select on a Table with FGAC Policy Enabled
Note 130652.1 - A policy does not work as defined, though UPDATE_CHECK is set to TRUE
Note 117058.1 - ORA-439 When Trying to Use DBMS_RLS
Note 179379.1 - Querying Against a Partitioned Table With FGAC Fails With ORA-01762
Note 158187.1 - Create Materialized View Fails With ORA-30372
Note 172423.1 - ORA-12015 when Creating Materialized View with Defined Fine Grain Access Control
Note 153978.1 - Oracle9i Export of Table with Row Level Security Aborts with ORA-1406 and EXP-0
Note 219911.1 - Fine Grained Access Control Feature Is Not Available In the Oracle Server Standard Edition
Note 250094.1 - How to Know the Exact Cause of an ORA-28113 Error After Setting a FGAC Policy
Note 278577.1 - FGAC Policy Causes Ora-00903 When Using A Function With UNION Operator And PK On Function Tables
Note 293301.1 - ORA-14136 When Exchanging Partition With a Table That Has a RLS Policy Enabled
Note 312030.1 - DBMS_OUTPUT.PUT_LINE Fires Multiple Times From FGAC Policy Function
Note 361345.1 - Ora-3001: "Unimplemented Feature" On Query Using "WITH" and FGAC
Note 422480.1 - ORA-39181:Only Partial Table Data Exported Due To Fine Grain Access Control
Note 1090749.1- Dependent Objects Gets Invalidated When Policy Is Added Or Dropped
Note 782462.1 -ORA-28113 Policy Predicate Has Error Even When The VPD Function is Flawless

Note 2199556.1 ORA-28113 & ORA-00904 On A 12c Database with VPD, FGA And Extended Statistics(Virtual Columns)

OracleAppsDBAKK1

Monday, December 2, 2019

Master Note For Oracle Virtual Private Database ( VPD / FGAC / RLS ) (Doc ID 1352641.1)