Oracle Virtual Private Database provides benefits in security, simplicity, and flexibility.

Attaching Oracle Virtual Private Database security policies to database tables, views, or synonyms, rather than implementing access controls in all your applications, provides the following benefits:

• Security. Associating a policy with a database table, view, or synonym can solve 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 directly 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.

• Simplicity. You add the security policy to a table, view, or synonym only once, rather than repeatedly adding it to each of your table-based, view-based, or synonym-based applications.

• Flexibility. 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 Human Resources clerks to have SELECT privileges for all employee records in their division, but to update only salaries for those employees in their division whose last names begin with A through F. Furthermore, you can create multiple policies for each table, view, or synonym.

Running policy functions multiple times can affect performance.

You can control the performance of policy functions by configuring how Oracle Database caches the Oracle Virtual Private Database predicates.

The following options are available:

• Evaluate the policy once for each query (static policies).

• Evaluate the policy only when an application context within the policy function changes (context-sensitive policies).

• Evaluate the policy each time it is run (dynamic policies).

The CREATE FUNCTION statement and the DBMS_RLS.ADD_POLICY procedure can configure a column-level Oracle Virtual Private Database policy.

Example 12-3 shows an Oracle Virtual Private Database policy in which sales department users cannot see the salaries of people outside the department (department number 30) of the sales department users. The relevant columns for this policy are sal and comm. First, the Oracle Virtual Private Database policy function is created, and then it is added by using the DBMS_RLS PL/SQL package.

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

RETURN VARCHAR2 AS
con VARCHAR2 (200);

BEGIN
 con := 'deptno=30';
 RETURN (con);
END hide_sal_comm;

BEGIN
 DBMS_RLS.ADD_POLICY (
  object_schema     => 'scott', 
  object_name       => 'emp',
  policy_name       => 'hide_sal_policy', 
  policy_function   => 'hide_sal_comm',
  sec_relevant_cols => 'sal,comm');
END;

Be default, column-level Oracle Virtual Private Database restricts the number of rows a query returns that references columns containing sensitive information.

You specify these security-relevant columns by using the SEC_RELEVANT_COLUMNS parameter of the DBMS_RLS.ADD_POLICY procedure, as shown in Example 12-3.

For example, consider sales department users with the SELECT privilege on the emp table, which is protected with the column-level Oracle Virtual Private Database policy created in Example 12-3. The user (for example, user SCOTT) runs the following query:

SELECT ENAME, d.dname, JOB, SAL, COMM 
 FROM emp e, dept d
 WHERE d.deptno = e.deptno;

The database returns the following rows:

ENAME      DNAME          JOB              SAL       COMM
---------- -------------- --------- ---------- ----------
ALLEN      SALES          SALESMAN        1600        300
WARD       SALES          SALESMAN        1250        500
MARTIN     SALES          SALESMAN        1250       1400
BLAKE      SALES          MANAGER         2850           
TURNER     SALES          SALESMAN        1500          0
JAMES      SALES          CLERK            950           
 
6 rows selected.

The only rows that are displayed are those that the user has privileges to access all columns in the row.

If a query references a sensitive column, then by default column-level Oracle Virtual Private Database restricts the number of rows returned.

With column-masking behavior, all rows display, even those that reference sensitive columns. However, the sensitive columns display as NULL values. To enable column-masking, set the SEC_RELEVANT_COLS_opt parameter of the DBMS_RLS.ADD_POLICY procedure.

For example, consider the results of the sales clerk query, described in the previous example. If column-masking is used, then instead of seeing only the row containing the details and Social Security number of the sales clerk, the clerk would see all rows from the emp table, but the ssn column values would be returned as NULL. Note that this behavior is fundamentally different from all other types of Oracle Virtual Private Database policies, which return only a subset of rows.

In contrast to the default action of column-level Oracle Virtual Private Database, column-masking displays all rows, but returns sensitive column values as NULL. To include column-masking in your policy, set the SEC_RELEVANT_COLS_OPT parameter of the DBMS_RLS.ADD_POLICY procedure to DBMS_RLS.ALL_ROWS.

The following considerations apply to column masking:

• Column-masking applies only to SELECT statements.

• Column-masking conditions generated by the policy function must be simple Boolean expressions, unlike regular Oracle Virtual Private Database predicates.

• For applications that perform calculations, or do not expect NULL values, use standard column-level Oracle Virtual Private Database, specifying SEC_RELEVANT_COLS rather than the SEC_RELEVANT_COLS_OPT column-masking option.

• Do not include columns of the object data type (including the XMLtype) in the sec_relevant_cols setting. This column type is not supported for the sec_relevant_cols setting.

• Column-masking used with UPDATE AS SELECT updates only the columns that users are allowed to see.

• For some queries, column-masking may prevent some rows from displaying. For example:

  SELECT * FROM emp
   WHERE sal = 10;
  

  Because the column-masking option was set, this query may not return rows if the salary column returns a NULL value.

The DBMS_RLS.ADD_POLICY procedure can configure column-level Oracle Virtual Private Database column masking.

Example 12-4 shows column-level Oracle Virtual Private Database column masking. It uses the same VPD policy as Example: Creating a Column-Level Oracle Virtual Private Database Policy, but with sec_relevant_cols_opt specified as DBMS_RLS.ALL_ROWS.

BEGIN
 DBMS_RLS.ADD_POLICY(
   object_schema         => 'scott', 
   object_name           => 'emp',
   policy_name           => 'hide_sal_policy', 
   policy_function       => 'hide_sal_comm',
   sec_relevant_cols     =>' sal,comm',
   sec_relevant_cols_opt => dbms_rls.ALL_ROWS);
END;

SELECT ENAME, d.dname, job, sal, comm 
 FROM emp e, dept d
 WHERE d.deptno = e.deptno;

ENAME      DNAME          JOB              SAL       COMM
---------- -------------- --------- ---------- ----------
CLARK      ACCOUNTING     MANAGER
KING       ACCOUNTING     PRESIDENT
MILLER     ACCOUNTING     CLERK
JONES      RESEARCH       MANAGER
FORD       RESEARCH       ANALYST
ADAMS      RESEARCH       CLERK
SMITH      RESEARCH       CLERK
SCOTT      RESEARCH       ANALYST
WARD       SALES          SALESMAN        1250        500
TURNER     SALES          SALESMAN        1500          0
ALLEN      SALES          SALESMAN        1600        300
JAMES      SALES          CLERK            950           
BLAKE      SALES          MANAGER         2850           
MARTIN     SALES          SALESMAN        1250       1400
 
14 rows selected.

You can group multiple security policies together, and apply them to an application.

A policy group is a set of security policies that belong to an application. You can designate an application context (known as a driving context or policy context) to indicate the policy group in effect. Then, when a user accesses the table, view, or synonym column, Oracle Database looks up the driving context to determine the policy group in effect. It enforces all the associated policies that belong to the policy group.

Policy groups are useful for situations where multiple applications with multiple security policies share the same table, view, or synonym. This enables you to identify those policies that 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. Integrating these two policies into the BENEFIT table requires 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 organize security policies into groups. By referring to the application context, Oracle Database determines which group of policies should be in effect at run time. The server enforces all the policies that belong to that policy group.

The DBMS_RLS.ADD_GROUPED_POLICY procedure adds a VPD policy to a VPD policy group.

To specify which policies will be effective, you can add a driving context using the DBMS_RLS.ADD_POLICY_CONTEXT procedure. If the driving context returns an unknown policy group, then an error is returned.

If the driving context is not defined, then Oracle Database runs all policies. Likewise, if the driving context is NULL, then policies from all policy groups are enforced. 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, view, or synonym, and each of them will be processed individually. This enables you to 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 partitioned 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 that 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 data access for Company A only. You would not apply this under the SUBSCRIBER policy, because the policy relates only to Company A, and it is more efficient to segregate the basic hosting policy from other policies.

Within a group of security policies, you can designate one security policy to be the default security policy.

This is useful in situations where you partition security policies by application, so that they will be always be in effect. Default security policies enable developers to base security enforcement under all conditions, while partitioning 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.

Policies defined in this group for a particular table, view, or synonym are run with the policy group specified by the driving context. As described earlier, a driving context is an application context that indicates the policy group in effect. The SYS_DEFAULT policy group may or may not contain policies. You cannot to drop the SYS_DEFAULT policy group. If you do, then Oracle Database displays an error.

If, to the SYS_DEFAULT policy group, you add policies associated with two or more objects, then each 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. Think of them as being organized in the tree structure as follows:

SYS_DEFAULT
  - policy1 (scott/emp)
  - policy3 (scott/emp)
SYS_DEFAULT
  - policy2 (scott/dept)

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

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

All policies applied to a table are enforced with AND syntax. If you have three policies applied to the CUSTOMERS table, then each policy is applied to the table. You can use policy groups and an 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 is always applicable (for example, to enforce data separated by subscriber in a hosting environment).

The package implementing the driving context must correctly validate the application that is being used to connect to the database.

Although Oracle Database checks the call stack to ensure that the package implementing the driving context sets context attributes, inadequate validation can still occur within the package. For example, in applications where database users or enterprise users are known to the database, the user needs the EXECUTE privilege on the package that sets the driving context. Consider a user who knows that the BENEFITS application enables more liberal access than the HR application. The setctx procedure (which sets the correct policy group within the driving context) does not perform any validation to determine which application is actually connecting. That is, the procedure does not check either the IP address of the incoming connection (for a three-tier system) or the proxy_user attribute of the user session.

This user could pass to the driving context package an argument setting the context to the more liberal BENEFITS policy group, and then access the HR application instead. Because the setctx does no further validation of the application, this user bypasses the more restrictive HR security policy.

By contrast, if you implement proxy authentication with Oracle Virtual Private Database, then you can determine the identity of the middle tier (and the application) that is connecting to the database on behalf of a user. The correct policy will be applied for each application to mediate data access.

For example, a developer using the proxy authentication feature could determine that the application (the middle tier) connecting to the database is HRAPPSERVER. The package that implements the driving context can thus verify whether the proxy_user in the user session is HRAPPSERVER. If so, then it can set the driving context to use the HR policy group. If proxy_user is not HRAPPSERVER, then it can deny access.

In this case, the following query is executed:

SELECT * FROM apps.benefit;

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

SELECT * FROM apps.benefit 
 WHERE company = SYS_CONTEXT('ID','MY_COMPANY') 
 AND SYS_CONTEXT('ID','TITLE') = 'MANAGER';

Specifying a policy type for your policies can optimize performance each the Oracle Virtual Private Database policy runs.

Policy types control how Oracle Database caches Oracle Virtual Private Database policy predicates. Consider setting a policy type for your policies, because the execution of policy functions can use a significant amount of system resources. Minimizing the number of times that a policy function can run optimizes database performance.

You can choose from five policy types: DYNAMIC, STATIC, SHARED_STATIC, CONTEXT_SENSITIVE, and SHARED_CONTEXT_SENSITIVE. These enable you to precisely specify how often a policy predicate should change. To specify the policy type, set the policy_type parameter of the DBMS_RLS.ADD POLICY procedure.

The DYNAMIC policy type runs the policy function each time a user accesses the Virtual Private Database-protected database objects.

If you do not specify a policy type in the DBMS_RLS.ADD_POLICY procedure, then, by default, your policy will be dynamic. You can specifically configure a policy to be dynamic by setting the policy_type parameter of the DBMS_RLS.ADD_POLICY procedure to DYNAMIC.

This policy type does not optimize database performance as the static and context sensitive policy types do. However, Oracle recommends that before you set policies as either static or context-sensitive, you should first test them as DYNAMIC policy types, which run every time. Testing policy functions as DYNAMIC policies first enables you to observe how the policy function affects each query, because nothing is cached. This ensures that the functions work properly before you enable them as static or context-sensitive policy types to optimize performance.

You can use the DBMS_UTILITY.GET_TIME function to measure the start and end times for a statement to execute. For example:

-- 1. Get the start time:
SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

  GET_TIME
----------
   2312721

-- 2. Run the statement:
SELECT COUNT(*) FROM HR.EMPLOYEES;

  COUNT(*)
----------
       107

-- 3. Get the end time:
SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

  GET_TIME
----------
   2314319

The DBMS_RLS.ADD_POLICY procedure can create a dynamic Oracle Virtual Private Database policy.

Example 12-5 shows how to create the DYNAMIC policy type.

BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.DYNAMIC);
END;
/

The static policy type enforces the same predicate for all users in the instance.

Oracle Database stores static policy predicates in SGA, so policy functions do not rerun for each query. This results in faster performance.

You can enable static policies by setting the policy_type parameter of the DBMS_RLS.ADD_POLICY procedure to either STATIC or SHARED_STATIC, depending on whether or not you want the policy to be shared across multiple objects.

Each execution of the same cursor could produce a different row set for the same predicate, because the predicate may filter the data differently based on attributes such as SYS_CONTEXT or SYSDATE.

For example, suppose you enable a policy as either a STATIC or SHARED_STATIC policy type, which appends the following predicate to all queries made against policy protected database objects:

WHERE dept = SYS_CONTEXT ('hr_app','deptno')

Although the predicate does not change for each query, it applies to the query based on session attributes of the SYS_CONTEXT. In the case of the preceding example, the predicate returns only those rows where the department number matches the deptno attribute of the SYS_CONTEXT, which is the department number of the user who is querying the policy-protected database object.

The DBMS_RLS.ADD_POLICY procedure can create a static Oracle Virtual Private Database (VPD) policy.

Example 12-6 shows how to create the STATIC policy type.

BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => DBMS_RLS.STATIC);
END;
/

The DBMS_RLS.ADD_POLICY procedure can create a shared static Oracle Virtual Private Database policy to share the policy with multiple objects.

If, for example, you wanted to apply the policy in Example 12-6 to a second table in the HR schema that may contain financial data that you want to side, you could use the SHARED_STATIC setting for both tables.

Example 12-7 shows how to set the SHARED_STATIC policy type for two tables that share the same policy.

-- 1. Create a policy for the first table, employees:
BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.SHARED_STATIC);
END;
/
-- 2. Create a policy for the second table, fin_data:
BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'fin_data',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.SHARED_STATIC);
END;
/

Static policies are ideal when every query requires the same predicate and fast performance is essential, such as hosting environments.

For these situations when the policy function appends the same predicate to every query, rerunning the policy function each time adds unnecessary overhead to the system. For example, consider a data warehouse that contains market research data for customer organizations that are competitors. The warehouse must enforce the policy that each organization can see only their own market research, which is expressed by the following predicate:

WHERE subscriber_id = SYS_CONTEXT('customer', 'cust_num')

Using SYS_CONTEXT for the application context enables the database to dynamically change the rows that are returned. You do not need to rerun the function, so the predicate can be cached in the SGA, thus conserving system resources and improving performance.

Context-sensitive policies are useful when different predicates must be applied depending on which executes the query.

For example, consider the case where managers should have the predicate WHERE group set to managers, and employees should have the predicate WHERE empno_ctx set to emp_id. A context-sensitive policy will enable you to present only the information that the managers must see when the managers log in, and only the information that the employees must see when they log in. The policy uses application contexts to determine which predicate to use.

In contrast to static policies, context-sensitive policies do not always cache the predicate. With context-sensitive policies, the database assumes that the predicate will change after statement parse time. But if there is no change in the local application context, then Oracle Database does not rerun the policy function within the user session. If there is a change in any attribute of any application context during the user session, then by default the database re-executes the policy function to ensure that it captures all changes to the predicate since the initial parsing. This results in unnecessary re-executions of the policy function if none of the associated attributes have changed. You can restrict the evaluation to a specific application context by including both the namespace and attribute parameters.

If you plan to use the namespace and attribute parameters in your policy, then follow these guidelines:

• Ensure that you specify both namespace and attribute parameters, not just one.

• Ensure that your policy has the policy_type argument set to DBMS_RLS.CONTEXT_SENSITIVE or SHARED_CONTEXT_SENSITIVE. You cannot use the namespace and attribute parameters in static or dynamic policies.

If there are no attributes associated with the Virtual Private Database policy function, then Oracle Database evaluates the context-sensitive function for any application context changes.

Shared context-sensitive policies operate in the same way as regular context-sensitive policies, except they can be shared across multiple database objects. For this policy type, all objects can share the policy function from the UGA, where the predicate is cached until the local session context changes.

The DBMS_RLS.ADD_POLICY procedure can create an Oracle Virtual Private Database context-sensitive policy.

Example 12-8shows how to create a CONTEXT_SENSITIVE policy in which the policy is evaluated only for changes to the empno_ctx namespace and emp_id attribute.

BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.CONTEXT_SENSITIVE,
  namespace       => 'empno_ctx',
  attribute       => 'emp_id');
END;
/

The DBMS_RLS.REFRESH_POLICY statement can refresh cached statements for Oracle Virtual Private Database context-sensitive policies.

Example 12-9 shows you can manually refresh all the cached statements that are associated with a Virtual Private Database context-sensitive policy by running the DBMS_RLS.REFRESH_POLICY procedure.

BEGIN
 DBMS_RLS.REFRESH_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update');
END;
/

The DBMS_RLS.ALTER_POLICY procedure can modify an Oracle Virtual Private Database policy.

Example 12-10 shows how you can use the DBMS_RLS.ALTER_POLICY statement to alter an existing context-sensitive policy so that the order_update_pol policy function is executed only if the relevant context attributes change.

BEGIN
 DBMS_RLS.ALTER_POLICY(
  object_schema   => 'oe',
  object_name     => 'orders',
  policy_name     => 'order_update_pol',
  alter_option    =>  DBMS_RLS.ADD_ATTRIBUTE_ASSOCIATION,
  namespace       => 'empno_ctx',
  attribute       => 'emp_role');
END;
/

The DBMS_RLS.ADD_POLICY procedure can create a shared context-sensitive Oracle Virtual Private Database to share a policy that has multiple objects.

Example 12-11 shows how to create two shared context sensitive policies that share a policy with multiple tables, and how to restrict the evaluation only for changes to the empno_ctx namespace and emp_id attribute.

-- 1. Create a policy for the first table, employees:
BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.SHARED_CONTEXT_SENSITIVE,
  namespace       => 'empno_ctx',
  attribute       => 'emp_id');
END;
/
--2. Create a policy for the second table, fin_data:
BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'fin_data',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.SHARED_CONTEXT_SENSITIVE,
  namespace       => 'empno_ctx',
  attribute       => 'emp_id');
END;
/

Use context-sensitive policies when a predicate does not need to change for a user session, but the policy must enforce multiple predicates for different users or groups.

For example, consider a sales_history table with a single policy. This policy states that analysts can see only their own products and regional employees can see only their own region. In this case, the database must rerun the policy function each time the type of user changes. The performance gain is realized when a user can log in and issue several DML statements against the protected object without causing the server to rerun the policy function.

Oracle Virtual Private Database provides five policy types, based on user needs such as hosting environments.

Table 12-2 summarizes the types of policy types available.

^Footnote 1

Each execution of the same cursor could produce a different row set for the same predicate because the predicate may filter the data differently based on attributes such as SYS_CONTEXT or SYSDATE.

This tutorial shows how to create a VPD policy that limits access to orders created by Sales Representative 159 in the OE.ORDERS table.

In essence, the policy translates the following statement:

SELECT * FROM OE.ORDERS;

To the following statement:

SELECT * FROM OE.ORDERS WHERE SALES_REP_ID = 159;

First, you must ensure that OE user account is active.

1. Log on to SQL*Plus as user SYS with the SYSDBA administrative privilege.

   sqlplus sys as sysdba
   Enter password: password
   

2. In a multitenant environment, connect to the appropriate PDB.

   For example:

   CONNECT SYS@hrpdb AS SYSDBA
   Enter password: password
   

   To find the available PDBs, run the show pdbs command. To check the current PDB, run the show con_name command.

3. Query the DBA_USERS data dictionary view to find the account status of OE.

   SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'OE';
   

   The status should be OPEN. If the DBA_USERS view lists user OE as locked and expired, then enter the following statement to unlock the OE account and create a new password:

   ALTER USER OE ACCOUNT UNLOCK IDENTIFIED BY password;
   

   Follow the guidelines in Minimum Requirements for Passwords to replace password with a password that is secure. For greater security, do not reuse the same password that was used in previous releases of Oracle Database.

Next, you are ready to create a policy function.

CREATE OR REPLACE FUNCTION auth_orders( 
  schema_var IN VARCHAR2,
  table_var  IN VARCHAR2
 )
 RETURN VARCHAR2
 IS
  return_val VARCHAR2 (400);
 BEGIN
  return_val := 'SALES_REP_ID = 159';
  RETURN return_val;
 END auth_orders;
/

After you create the policy function, you are ready to associate it with a VPD policy.

After you create the Oracle Virtual Private Database policy, it goes into effect immediately.

1. Connect as user OE.

   CONNECT oe -- Or, CONNECT OE@hrpdb
   Enter password: password
   

2. Enter the following SELECT statement:

   SELECT COUNT(*) FROM ORDERS;
   

   The following output should appear:

    COUNT(*)
   ---------
           7
   

   The policy is in effect for user OE: As you can see, only 7 of the 105 rows in the orders table are returned.

   But users with administrative privileges still have access to all the rows in the table.

3. Connect as user SYS with the SYSDBA administrative privilege.

   CONNECT SYS AS SYSDBA -- Or, CONNECT SYS@hrpdb AS SYSDBA
   Enter password: password
   

4. Enter the following SELECT statement:

   SELECT COUNT(*) FROM OE.ORDERS;
   

   The following output should appear:

    COUNT(*)
   ---------
         105

If you no longer need the components of this tutorial, then you can remove them.

1. As user SYS, remove the function and policy as follows:

   DROP FUNCTION auth_orders;
   EXEC DBMS_RLS.DROP_POLICY('OE','ORDERS','ORDERS_POLICY');
   

2. If you need to lock and expire the OE account, then enter the following statement:

   ALTER USER OE ACCOUNT LOCK PASSWORD EXPIRE;

This tutorial shows how to use a database session-based application context to implement a policy in which customers see only their own orders.

If you are using a multitenant environment, then this tutorial applies to the current PDB only.

In this tutorial, you create the following layers of security:

1. When a user logs on, a database session-based application context checks whether the user is a customer. If a user is not a customer, the user still can log on, but this user cannot access the orders entry table you will create for this example.

2. If the user is a customer, he or she can log on. After the customer has logged on, an Oracle Virtual Private Database policy restricts this user to see only his or her orders.

3. As a further restriction, the Oracle Virtual Private Database policy prevents users from adding, modifying, or removing orders.

First, create user accounts and the sample tables.

1. Start SQL*Plus and log on as a user who has administrative privileges.

   sqlplus sys as sysdba
   Enter password: password
   

2. In a multitenant environment, connect to the appropriate PDB.

   For example:

   CONNECT SYS@hrpdb AS SYSDBA
   Enter password: password
   

   To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

3. Create the following administrative user, who will administer the Oracle Virtual Private Database policy.

   The following SQL statements create this user and then grant the user the necessary privileges for completing this tutorial.

   CREATE USER sysadmin_vpd IDENTIFIED BY password CONTAINER = CURRENT;
   GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO sysadmin_vpd;
   GRANT EXECUTE ON DBMS_SESSION TO sysadmin_vpd;
   GRANT EXECUTE ON DBMS_RLS TO sysadmin_vpd;
   

   Follow the guidelines in Minimum Requirements for Passwords to replace password with a password that is secure.

4. Create the following local users:

   CREATE USER tbrooke IDENTIFIED BY password CONTAINER = CURRENT;
   CREATE USER owoods IDENTIFIED BY password CONTAINER = CURRENT;
   
   GRANT CREATE SESSION TO tbrooke, owoods;
   

   Replace password with a password that is secure.

5. Check the account status of the sample user SCOTT, who you will use for this tutorial:

   SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'SCOTT';
   

   The status should be OPEN. If the DBA_USERS view lists user SCOTT as locked and expired, then enter the following statement to unlock the SCOTT account and create a new password for him:

   ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY password;
   

   Follow the guidelines in Minimum Requirements for Passwords to replace password with a password that is secure. For greater security, do not reuse the same password that was used in previous releases of Oracle Database.

6. Connect as user SCOTT.

   CONNECT SCOTT -- Or, CONNECT SCOTT@hrpdb
   Enter password: password
   

7. Create and populate the customers table.

   CREATE TABLE customers (
    cust_no    NUMBER(4), 
    cust_email VARCHAR2(20),
    cust_name  VARCHAR2(20));
   
   INSERT INTO customers VALUES (1234, 'TBROOKE', 'Thadeus Brooke');
   INSERT INTO customers VALUES (5678, 'OWOODS', 'Oberon Woods');
   

   When you enter the user email IDs, enter them in upper-case letters. Later on, when you create the application context PL/SQL package, the SESSION_USER parameter of the SYS_CONTEXT function expects the user names to be in upper case. Otherwise, you will be unable to set the application context for the user.

8. User sysadmin_vpd will need SELECT privileges for the customers table, so as user SCOTT, grant him this privilege.

   GRANT READ ON customers TO sysadmin_vpd;
   

9. Create and populate the orders_tab table.

   CREATE TABLE orders_tab (
     cust_no  NUMBER(4),
     order_no NUMBER(4));
   
   INSERT INTO orders_tab VALUES (1234, 9876);
   INSERT INTO orders_tab VALUES (5678, 5432);
   INSERT INTO orders_tab VALUES (5678, 4592);
   

10. Users tbrooke and owoods need to query the orders_tab table, so grant them the READ object privilege.

    GRANT READ ON orders_tab TO tbrooke, owoods;
    

Next, you are ready to create the database session-based application context.

1. Connect as user sysadmin_vpd.

   CONNECT sysadmin_vpd -- Or, CONNECT sysadmin_vpd@hrpdb
   Enter password: password
   

2. Enter the following statement:

   CREATE OR REPLACE CONTEXT orders_ctx USING orders_ctx_pkg;
   

   This statement creates the orders_ctx application context. Remember that even though user sysadmin_vpd has created this context and it is associated with the sysadmin_vpd schema, the SYS schema owns the application context.

After you create the application context, you are ready to create a package to set the context.

The logon trigger runs the PL/SQL package procedure so that the next time a user logs on, the application context is set.

The logon trigger sets the application context for the user when the trigger runs the sysadmin_vpd.orders_ctx_pkg.set_custnum procedure.

1. Connect as user tbrooke.

   CONNECT tbrooke -- For a CDB, connect to the PDB, e.g., @hrpdb 
   Enter password: password
   

2. Execute the following query:

   SELECT SYS_CONTEXT('orders_ctx', 'cust_no') custnum FROM DUAL;
   

   The following output should appear:

   EMP_ID
   -------------------------------------------------------------------
   1234

The next step is to create a PL/SQL function to control the display of the user’s query.

1. Connect as user sysadmin_vpd.

   CONNECT sysadmin_vpd -- Or, CONNECT sysadmin_vpd@hrpdb
   Enter password: password
   

2. Create the following function:

   CREATE OR REPLACE FUNCTION get_user_orders(
     schema_p   IN VARCHAR2,
     table_p    IN VARCHAR2)
    RETURN VARCHAR2
    AS
     orders_pred VARCHAR2 (400);
    BEGIN
     orders_pred := 'cust_no = SYS_CONTEXT(''orders_ctx'', ''cust_no'')'; 
    RETURN orders_pred;
   END;
   /
   

Finally, you are ready to create the VPD security policy.

Now that you have created all the components, you are ready to test the policy.

1. Connect as user tbrooke.

   CONNECT tbrooke -- Or, CONNECT tbrooke@hrpdb
   Enter password: password
   

   User tbrooke can log on because he has passed the requirements you defined in the application context.

2. As user tbrooke, access your purchases.

   SELECT * FROM scott.orders_tab;
   

   The following output should appear:

      CUST_NO    ORDER_NO
   ----------  ----------
         1234        9876
   

   User tbrooke has passed the second test. He can access his own orders in the scott.orders_tab table.

3. Connect as user owoods, and then access your purchases.

   CONNECT owoods -- For a CDB, connect to the PDB, e.g., @hrpdb
   Enter password: password
   
   SELECT * FROM scott.orders_tab
   

   The following output should appear:

      CUST_NO    ORDER_NO
   ----------  ----------
         5678        5432
         5678        4592
   

   As with user tbrooke, user owoods can log on and see a listing of his own orders.

If you no longer need the components of this tutorial, then you can remove them.

1. Connect as user SCOTT.

   CONNECT SCOTT -- Or, CONNECT SCOTT@hrpdb
   Enter password: password
   

2. Remove the orders_tab and customers tables.

   DROP TABLE orders_tab;
   DROP TABLE customers; 
   

3. Connect as user SYS, connecting with AS SYSDBA.

   CONNECT SYS AS SYSDBA -- Or, CONNECT SYS@hrpdb AS SYSDBA
   Enter password: password
   

4. Run the following statements to drop the components for this tutorial:

   DROP CONTEXT orders_ctx;
   DROP USER sysadmin_vpd CASCADE;
   DROP USER tbrooke;
   DROP USER owoods;

This tutorial shows how you can use Oracle Virtual Private Database (VPD) to create a policy group.

Oracle Virtual Private Database Policy Groups describes how you can group a set of policies for use in an application. When a nondatabase user logs onto the application, Oracle Database grants the user access based on the policies defined within the appropriate policy group.

For column-level access control, every column or set of hidden columns is controlled by one policy. In this tutorial, you must hide two sets of columns. So, you must create two policies, one for each set of columns that you want to hide. You only want one policy for each user; the driving application context separates the policies for you.

First, you must create user accounts and tables for this tutorial, and grant the appropriate privileges.

1. Log on as user SYS with the SYSDBA administrative privilege.

   sqlplus sys as sysdba
   Enter password: password
   

2. In a multitenant environment, connect to the appropriate PDB.

   For example:

   CONNECT SYS@hrpdb AS SYSDBA
   Enter password: password
   

   To find the available PDBs, run the show pdbs command. To check the current PDB, run the show con_name command.

3. Create the following local users:

   CREATE USER apps_user IDENTIFIED BY password CONTAINER = CURRENT;
   GRANT CREATE SESSION TO apps_user;
   CREATE USER sysadmin_pg  IDENTIFIED BY password CONTAINER = CURRENT;
   GRANT CREATE SESSION, CREATE PROCEDURE, CREATE ANY CONTEXT TO sysadmin_pg;

   Follow the guidelines in Minimum Requirements for Passwords to replace password with a password that is secure.

4. Grant the following additional privilege to user sysadmin_pg:

   GRANT EXECUTE ON DBMS_RLS TO sysadmin_pg;
   

5. Log on as user OE.

   CONNECT OE -- Or, CONNECT OE@hrpdb
   Enter password: password
   

   If the OE account is locked and expired, then reconnect as user SYS with the SYSDBA administrative privilege and enter the following statement to unlock the account and give it s new password:

   ALTER USER OE ACCOUNT UNLOCK IDENTIFIED BY password;
   

   Replace password with a password that is secure. For greater security, do not reuse the same password that was used in previous releases of Oracle Database.

6. Create the product_code_names table:

   CREATE TABLE product_code_names(
   group_a     varchar2(32),
   year_a      varchar2(32),
   group_b     varchar2(32),
   year_b      varchar2(32));
   

7. Insert some values into the product_code_names table:

   INSERT INTO product_code_names values('Biffo','2008','Beffo','2004');
   INSERT INTO product_code_names values('Hortensia','2008','Bunko','2008');
   INSERT INTO product_code_names values('Boppo','2006','Hortensia','2003');
   
   COMMIT;
   

8. Grant the apps_user user SELECT privileges on the product_code_names table.

   GRANT SELECT ON product_code_names TO apps_user;

Next, you must create a policy group for each of the two nondatabase users, provider_a and provider_b.

1. Connect as user sysadmin_pg.

   CONNECT sysadmin_pg -- Or, CONNECT sysadmin_pg@hrpdb
   Enter password: password
   

2. Create the provider_a_group policy group, to be used by user provider_a:

   BEGIN
    DBMS_RLS.CREATE_POLICY_GROUP(
    object_schema   => 'oe',
    object_name     => 'product_code_names',
    policy_group    => 'provider_a_group');
   END;
   /
   

3. Create the provider_b_group policy group, to be used by user provider_b:

   BEGIN
    DBMS_RLS.CREATE_POLICY_GROUP(
    object_schema   => 'oe',
    object_name     => 'product_code_names',
    policy_group    => 'provider_b_group');
   END;
   /

A policy group must have a function that defines how the application can control data access for users.

1. Create the vpd_function_provider_a function, which restricts the data accessed by user provider_a.

   CREATE OR REPLACE FUNCTION vpd_function_provider_a 
    (schema in varchar2, tab in varchar2) return varchar2 as 
     predicate  varchar2(8) default NULL;
     BEGIN
      IF LOWER(SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')) = 'provider_a' 
       THEN predicate := '1=2';
      ELSE NULL;
     END IF;
     RETURN predicate;
   END;
   /
   

   This function checks that the user logging in is really user provider_a. If this is true, then only the data in the product_code_names table columns group_a and year_a will be visible to provider_a. Data in columns group_b and year_b will not appear for provider_a. This works as follows: Setting predicate := '1=2' hides the relevant columns. In Step 5: Add the PL/SQL Functions to the Policy Groups, you specify these columns in the SEC_RELEVANT_COLS parameter.

2. Create the vpd_function_provider_b, function, which restricts the data accessed by user provider_a.

   CREATE OR REPLACE FUNCTION vpd_function_provider_b 
    (schema in varchar2, tab in varchar2) return varchar2 as 
     predicate  varchar2(8) default NULL;
     BEGIN
      IF LOWER(SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')) = 'provider_b' 
       THEN predicate := '1=2';
      ELSE NULL;
     END IF;
     RETURN predicate;
   END;
   /
   

   Similar to the vpd_function_provider_a function, this function checks that the user logging in is really user provider_b. If this is true, then only the data in the columns group_b and year_b will be visible to provider_b, with data in the group_a and year_a not appearing for provider_b. Similar to the vpd_function_provider_a function, predicate := '1=2' hides the relevant columns specified Step 5: Add the PL/SQL Functions to the Policy Groups in the SEC_RELEVANT_COLS parameter.

The application context determines which policy the nondatabase user who is the logging on should use.

1. As user sysadmin_pg, create the driving application context as follows:

   CREATE OR REPLACE CONTEXT provider_ctx USING provider_package;
   

2. Create the PL/SQL provider_package package for the application context.

   CREATE OR REPLACE PACKAGE provider_package IS
    PROCEDURE set_provider_context (policy_group varchar2 default NULL);
   END;
   /
   CREATE OR REPLACE PACKAGE BODY provider_package AS
    PROCEDURE set_provider_context (policy_group varchar2 default NULL) IS
    BEGIN
     CASE LOWER(SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER'))
      WHEN 'provider_a' THEN
       DBMS_SESSION.SET_CONTEXT('provider_ctx','policy_group','PROVIDER_A_GROUP');
      WHEN 'provider_b' THEN
       DBMS_SESSION.SET_CONTEXT('provider_ctx','policy_group','PROVIDER_B_GROUP');
     END CASE;
    END set_provider_context;
   END;
   /
   

3. Associate the provider_ctx application context with the product_code_names table, and then provide a name.

   BEGIN
    DBMS_RLS.ADD_POLICY_CONTEXT(
    object_schema  =>'oe',
    object_name    =>'product_code_names',
    namespace      =>'provider_ctx',
    attribute      =>'policy_group');
   END;
   /
   

4. Grant the apps_user account the EXECUTE privilege for the provider_package package.

   GRANT EXECUTE ON provider_package TO apps_user;

Now that you have created the necessary functions, you are ready to associate them with their appropriate policy groups.

1. Add the vpd_function_provider_a function to the provider_a_group policy group.

   BEGIN 
    DBMS_RLS.ADD_GROUPED_POLICY(
    object_schema         => 'oe',
    object_name           => 'product_code_names',
    policy_group          => 'provider_a_group',
    policy_name           => 'filter_provider_a',
    function_schema       => 'sysadmin_pg',
    policy_function       => 'vpd_function_provider_a',
    statement_types       => 'select',
    policy_type           => DBMS_RLS.CONTEXT_SENSITIVE,
    sec_relevant_cols     => 'group_b,year_b',
    sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS,
    namespace             => 'provider_ctx',
    attribute             => 'provider_group');
   END;
   /
   

   The group_b and year_b columns specified in the sec_relevant_cols parameter are hidden from user provider_a.

2. Add the vpd_function_provider_b function to the provider_b_group policy group.

   BEGIN 
    DBMS_RLS.ADD_GROUPED_POLICY(
    object_schema         => 'oe',
    object_name           => 'product_code_names',
    policy_group          => 'provider_b_group',
    policy_name           => 'filter_provider_b',
    function_schema       => 'sysadmin_pg',
    policy_function       => 'vpd_function_provider_b',
    statement_types       => 'select',
    policy_type           => DBMS_RLS.CONTEXT_SENSITIVE,
    sec_relevant_cols     => 'group_a,year_a',
    sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS,
    namespace             => 'provider_ctx',
    attribute             => 'provider_group');
   END;
   /

   The group_a and year_a columns specified in the sec_relevant_cols parameter are hidden from user provider_b.

Now you are ready to test the two policy groups.

1. Connect as user apps_user and then enter the following statements to ensure that the output you will create later on is nicely formatted.

   CONNECT apps_user -- Or, CONNECT apps_user@hrpdb
   Enter password: password
   
   col group_a format a16
   col group_b format a16;
   col year_a format a16;
   col year_b format a16;
   

2. Set the session identifier to provider_a.

   EXEC DBMS_SESSION.SET_IDENTIFIER('provider_a');
   

   Here, the application sets the identifier. Setting the identifier to provider_a sets the apps_user user to a user who should only see the products available to products in the provider_a_group policy group.

3. Run the provider_package to set the policy group based on the context.

   EXEC sysadmin_pg.provider_package.set_provider_context;
   

   At this stage, you can check the application context was set, as follows:

   SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') AS END_USER FROM DUAL;
   

   The following output should appear:

   END_USER
   -----------------
   provider_a
   

4. Enter the following SELECT statement:

   SELECT * FROM oe.product_code_names;
   

   The following output should appear:

   GROUP_A          YEAR_A           GROUP_B          YEAR_B
   ---------------- ---------------- ---------------- ----------------
   Biffo            2008
   Hortensia        2008
   Boppo            2006
   

5. Set the client identifier to provider_b and then enter the following statements:

   EXEC DBMS_SESSION.SET_IDENTIFIER('provider_b');
   EXEC sysadmin_pg.provider_package.set_provider_context;
   SELECT * FROM oe.product_code_names;
   

   The following output should appear:

   GROUP_A          YEAR_A           GROUP_B          YEAR_B
   ---------------- ---------------- ---------------- ----------------
                                     Beffo            2004
                                     Bunko            2008
                                     Hortensia        2003

If you no longer need the components of this tutorial, then you can remove them.

1. Connect as user OE.

   CONNECT OE -- Or, CONNECT OE@hrpdb
   Enter password: password
   

2. Drop the product_code_names table.

   DROP TABLE product_code_names;
   

3. Connect as user SYS with the SYSDBA administrative privilege.

   CONNECT SYS AS SYSDBA -- Or, CONNECT SYS@hrpdb AS SYSDBA
   Enter password: password
   

4. Drop the application context and users for this tutorial.

   DROP CONTEXT provider_ctx;
   DROP USER sysadmin_pg cascade;
   DROP USER apps_user;

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.

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.

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