Tutorial: Creating a Simple Oracle Virtual Private Database Policy

This tutorial shows how to create a simple Oracle Virtual Private Database policy using the OE user account.

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

• Step 1: Ensure That the OE User Account Is Active
  First, you must ensure that OE user account is active.

• Step 2: Create a Policy Function
  Next, you are ready to create a policy function.

• Step 3: Create the Oracle Virtual Private Database Policy
  After you create the policy function, you are ready to associate it with a VPD policy.

• Step 4: Test the Policy
  After you create the Oracle Virtual Private Database policy, it goes into effect immediately.

• Step 5: Remove the Components of This Tutorial
  If you no longer need the components of this tutorial, then you can remove them.

About This Tutorial

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;

Step 1: Ensure That the OE User Account Is Active

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

1. Log in to a PDB as user SYS with the SYSDBA administrative privilege.

   sqlplus sys@pdb_name as sysdba
   Enter password: password

   To find the available PDBs in a CDB, log in to the CDB root container and then query the PDB_NAME column of the DBA_PDBS data dictionary view. To check the current container, run the show con_name command.

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

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.

Related Topics

• Guidelines for Securing Passwords

Step 2: Create a Policy Function

Next, you are ready to create a policy function.

As user SYS, create the following function, which will append the WHERE SALES_REP_ID = 159 clause to any SELECT statement on the OE.ORDERS table.

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;
/

In this example:

• schema_var and table_var create input parameters to specify to store the schema name, OE, and table name, ORDERS. First, define the parameter for the schema, and then define the parameter for the object, in this case, a table. Always create them in this order. The Virtual Private Database policy you create will need these parameters to specify the OE.ORDERS table.

• RETURN VARCHAR2 returns the string that will be used for the WHERE predicate clause. Remember that return value is always a VARCHAR2 data type.

• IS ... RETURN return_val encompasses the creation of the WHERE SALES_REP_ID = 159 predicate.

Step 3: Create the Oracle Virtual Private Database Policy

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

Create the following policy by using the ADD_POLICY procedure in the DBMS_RLS package.

<pre class="copy"><code>BEGIN   DBMS_RLS.ADD_POLICY (     object_schema    =&gt; &#39;oe&#39;,     object_name      =&gt; &#39;orders&#39;,     policy_name      =&gt; &#39;orders_policy&#39;,     function_schema  =&gt; &#39;sys&#39;,     policy_function  =&gt; &#39;auth_orders&#39;,     statement_types  =&gt; &#39;select&#39;    );  END; /</code></pre>

In this example:

• object_schema => 'oe' specifies the schema that you want to protect, that is, OE.

• object_name => 'orders' specifies the object within the schema to protect, that is, the ORDERS table.

• policy_name => 'orders_policy' names this policy orders_policy.

• function_schema => 'sys' specifies the schema in which the auth_orders function was created. In this example, auth_orders was created in the SYS schema. But typically, it should be created in the schema of a security administrator.

• policy_function => 'auth_orders' specifies a function to enforce the policy. Here, you specify the auth_orders function that you created in the preceding step, when you created the policy function.

• statement_types => 'select' specifies the operations to which the policy applies. In this example, the policy applies to all SELECT statements that the user may perform.

Related Topics

• Step 2: Create a Policy Function

Step 4: Test the Policy

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

The next time a user, including the owner of the schema, performs a SELECT on OE.ORDERS, only the orders by Sales Representative 159 will be accessed.

1. Connect as user OE.

   CONNECT oe@pdb_name
   Enter password: password

2. Enter the following SELECT statement:

   SELECT COUNT(*) FROM ORDERS;

The following output should appear:

<pre class="copy"><code> COUNT(*)  ---------         7</code></pre>

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.

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

   CONNECT SYS@pdb_name AS SYSDBA
   Enter password: password

2. Enter the following SELECT statement:

   SELECT COUNT(*) FROM OE.ORDERS;

The following output should appear:

 COUNT(*)

---------
      105

Step 5: Remove the Components of This Tutorial

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

1. As user SYS in the PDB in which you created the tutorial components, 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;