Step 5: Create the ACCESSCONTROL_ORDERS Virtual Private Database Policy

Now that you have created the Virtual Private Database policy function, you can create the Virtual Private Database policy, accesscontrol_orders, and then attach it to the ORDERS table. To increase performance, add the CONTEXT_SENSITIVE parameter to the policy, so that Oracle Database only executes the f_policy_orders function when the content of the application context changes, in this case, when a new user logs on. Oracle Database only activates the policy when a user performs a SQL SELECT statement on the ORDERS table. Hence, the user cannot run the INSERT, UPDATE, and DELETE statements, because the policy does not allow him or her to do so.

To create the ACCESSCONTROL_ORDERS Virtual Private Database policy:  

1. In Database Control, click the Database Instance link to display the Database Home page.

2. Click Server to display the Server subpage.

3. In the Security section, click Virtual Private Database.

   The Virtual Private Database Policies page appears.

4. Click Create.

   The Create Policy page appears, with the Policy subpage displaying.

5. Under General, enter the following:

   • Policy Name: ACCESSCONTROL_ORDERS

   • Object Name: OE.ORDERS

   • Policy Type: Select CONTEXT_SENSITIVE.

     This type reevaluates the policy function at statement run-time if it detects context changes since the last use of the cursor. For session pooling, where multiple clients share a database session, the middle tier must reset the context during client switches. Note that Oracle Database does not cache the value that the function returns for this policy type; it always runs the policy function during statement parsing. The CONTEXT_SENSITIVE policy type applies to only one object.

     To enable the Policy Type, select the Enabled box.

6. Under Policy Function, enter the following:

   • Policy Function: Enter the name of the function that generates a predicate for the policy, in this case, SEC_ADMIN.F_POLICY_ORDERS.

   • Long Predicate: Do not select this box.

     Typically, you select this box to return a predicate with a length of up to 32K bytes. By not selecting this box, Oracle Database limits the predicate to 4000 bytes.

7. Under Enforcement, select the SELECT option but deselect the remaining options that already may be selected.

8. Do not select any options under Security Relevant Columns.

9. Click OK.

   The Virtual Private Database Policies page appears, with the ACCESSCONTROL_ORDERS policy listed in the list of policies.