Tutorial: Creating an Oracle Virtual Private Database Policy

The ORDERS table in the Order Entry database, OE, contains the following information:

Name                                   Null?    Type
-------------------------------------- -------- ---------------------------------
ORDER_ID                               NOTNULL  NUMBER(12)
ORDER_DATE                             NOTNULL  TIMESTAMP(6) WITH LOCAL TIME ZONE
ORDER_MODE                                      VARCHAR2(8)
CUSTOMER_ID                            NOTNULL  NUMBER(6)
ORDER_STATUS                                    NUMBER(2)
ORDER_TOTAL                                     NUMBER(8,2)
SALES_REP_ID                                    NUMBER(6)
PROMOTION_ID                                    NUMBER(6)

Suppose you want to limit access to this table based on the person who is querying the table. For example, a sales representative should only see the orders that he or she have created, but other employees should not. In this tutorial, you create a sales representative user account and an account for a finance manager. Then, you create an Oracle Virtual Private Database policy that will limit the data access to these users based on their roles.

The Virtual Private Database policy that you will create is associated with a PL/SQL function. Because VPD policies are controlled by PL/SQL functions or procedures, you can design the policy to restrict access in many different ways. For this tutorial, the function you create will restrict access by the employees based on to whom they report. The function will restrict the customer access based on the customer's ID.

You may want to store VPD policies in a database account separate from the database administrator and from application accounts. In this tutorial, you will use the sec_admin account, which was created in "Tutorial: Creating a Secure Application Role", to create the VPD policy. This provides better security by separating the VPD policy from the applications tables.

To restrict access based on the sensitivity of row data, you can use Oracle Label Security (OLS). OLS lets you categorize data into different levels of security, with each level determining who can access the data in that row. This way, the data access restriction is focused on the data itself, rather than on user privileges. See "Enforcing Row-Level Security with Oracle Label Security" for more information.

In this tutorial:

• Step 1: If Necessary, Create the Security Administrator Account

• Step 2: Update the Security Administrator Account

• Step 3: Create User Accounts for This Tutorial

• Step 4: Create the F_POLICY_ORDERS Policy Function

• Step 5: Create the ACCESSCONTROL_ORDERS Virtual Private Database Policy

• Step 6: Test the ACCESSCONTROL_ORDERS Virtual Private Database Policy

• Step 7: Optionally, Remove the Components for This Tutorial