Using Virtual Private Database Policy Groups

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

Virtual Private Database (VPD) enables you to control access to table columns and rows by database users and non-database users (application or end-users).

While access controls for table rows is controlled on a user basis, the control of which column or set of columns to hide or show is determined on a per-policy basis. One VPD policy is implemented for each (set of) sensitive column(s). In cases where multiple sets of columns are sensitive for different user groups, a VPD policy is required for each set. However, if multiple VPD policies are attached to a table, they are equally executed, and their results are ANDed together. In this example, only one of the policies should be enforced. To control which one to enforce, a 'driving application context' is implemented.

The purpose of this tutorial is to demonstrate:

A use-case for virtual private database (VPD) policy groups and driving application context that allows selective hiding of certain application table columns for different application users
How to apply VPD policies to application (end) users that do not have a database account

Time to Complete

Approximately 1 hour.

Overview

In this tutorial you will perform the following tasks:

Create users and grant appropriate privileges
Create VPD policy groups
Create a policy function
Add policies to the policy groups
Create a package
Create an application context
Verfiy your implementation

Software and Hardware Requirements

The following is a list of software requirements:

Oracle Database 11g Release 2

Prerequisites

Before starting this tutorial, you should:

.	Install Oracle Database 11g Release 2.
.	Download and unzip the vpd_groups.zip file into a directory of your choice.

Creating Users and Granting Privileges

To create the users that you will use during this scenario and grant privileges to the users, perform the following tasks.

Creating the APP_SERVER User

.	Open your browser and enter the appropriate URL to launch Enterprise Manager Database Control on your server. Log in as the SYSTEM user.
.	Click the Server tab on the Database Home page.
.	In the Security region, click the Users link.
.	Click Create.
.	Enter app_server in the Name field. Specify a password. Use the flashlight to select a default tablespace and a temporary tablespace. Click the System Privileges tab.
.	Click Edit List.
.	Use the scroll bar to locate CREATE TABLE in the Available System Privileges list. Click Move.
.	CREATE TABLE now appears as a Selected System Privilege. Click OK.
.	CREATE TABLE is now listed as a System Privilege. Click the Quotas tab..
.	For the EXAMPLE tablespace, select Unlimited in the Quota drop-down menu.
.	Click OK.
.	You receive a confirmation message that your user has been created.

Creating the SEC_ADMIN User

.	On the Users page, click Create.
.	Enter sec_admin in the Name field. Specify a password. Use the flashlight to select a default tablespace and a temporary tablespace. Click the System Privileges tab.
.	Click Edit List.
.	Use the scroll bar to locate CREATE ANY CONTEXT in the Available System Privileges list. Click Move. Repeat this procedure for CREATE PROCEDURE and SELECT ANY DICTIONARY.
.	The privileges appear as Selected System Privileges. Click OK.
.	The selected system privileges are listed. Click OK.
.	You receive a confirmation message that the user has been created. Click Logout.
.	Click Login. Enter sys in the User Name field and enter the password for the SYS user. Select SYSDBA in the Connect As menu. Click Login again.
.	Click the Server tab on the Database Home page.
.	Click Users in the Security region.
.	Enter sec_admin in the Object Name field and click Go.
.	Click Edit.
.	Click the Object Privileges tab.
.	Select Package in the Select Object Type menu. Click Add.
.	Enter SYS.DBMS_RLS in the Select Package Objects field. Select EXECUTE in the Available Privileges list and click Move.
.	Click OK.
.	Click Apply.
.	You receive a confirmation message that the SEC_ADMIN user has been modified. Click Logout.
.	Navigate to the directory into which you unzipped the vpd_groups.zip file. Invoke SQLPlus and connect as the APP_SERVER* user.
.	Execute the cr_rfid_data.sql script to create the APP_SERVER.RFID_DATA table and insert rows into the table.
.	Exit from SQL*Plus.

Creating the VPD Policy Groups

Creating the PROVIDER_A_GROUP Policy Group

.	Log in to Enterprise Manager Database Control as the SEC_ADMIN user.
.	Click the Server tab.
.	In the Security region of the Server page, click Virtual Private Database..
.	Click the Advanced tab.
.	Click Create to add the VPD policy group.
.	Enter provider_a_group in the Policy Group Name field. Enter APP_SERVER.RFID_DATA in the Object Name field. Click OK.
.	You receive a confirmation message that the policy group has been created.

Creating the PROVIDER_B_GROUP Policy Group

.	On the Virutal Private Database Policies page (Advanced tab), click Create to create another policy group.
.	Enter provider_b_group in the Policy Group Name field. Enter APP_SERVER.RFID_DATA in the Object Name field. Click OK.
.	You receive a confirmation message that the policy group has been created. Click the Database link to return to the Database Home page.

On the Virutal Private Database Policies page (Advanced tab), click Create to create another policy group.

Enter provider_b_group in the Policy Group Name field. Enter APP_SERVER.RFID_DATA in the Object Name field. Click OK.

You receive a confirmation message that the policy group has been created. Click the Database link to return to the Database Home page.

Creating the Policy Function

To create the policy function, perform the following steps:

.	On the Database Home page, click the Schema tab.
.	Click Functions in the Programs region.
.	Click Create.
.	Enter f_hide_cols in the Name field. Copy and paste the following code into the Source field. Click OK. (schema in varchar2, tab in varchar2) return varchar2 as predicate varchar2(8) default '1=2'; begin return predicate; end;
.	You receive a confirmation message that the function has been created. Click the Database tab to return to the Database Home page.

Adding Policies to the Policy Groups

To add policies to the policy groups, perform the following steps.

Adding Policies to PROVIDER_A_GROUP

.	Click the Server tab on the Database Home page.
.	Click Virtual Private Database in the Security region.
.	Click Advanced.
.	Select PROVIDER_A_GROUP and click Manage.
.	In the Manage Group Policies section, click Add.
.	Enter hide_cols_b_from_a in the Policy Name field. Select SHARED_CONTEXT_SENSITIVE in the Policy Type menu. Ensure that Enabled is selected. Enter SEC_ADMIN.F_HIDE_COLS in the Policy Function field.
.	Scroll down the page. In the Enforcement region, ensure that only SELECT is selected (deselect INSERT, UPDATE, and DELETE). Select Enable Column Masking Behavior. Click Add.
.	Select STORAGE_B and DATE_B. Click Select.
.	You are returned to the Add Policy page. Click Continue.
.	On the Manage Policy Group: PROVIDER_A_GROUP page, click OK.
.	You receive a confirmation message that the policy group has been successfully modified.

Adding Policies to PROVIDER_B_GROUP

.	Select PROVIDER_B_GROUP and click Manage.
.	In the Manage Group Policies section, click Add.
.	Enter hide_cols_a_from_b in the Policy Name field. Select SHARED_CONTEXT_SENSITIVE in the Policy Type menu. Ensure that Enabled is selected. Enter SEC_ADMIN.F_HIDE_COLS in the Policy Function field.
.	Scroll down the page. In the Enforcement region, ensure that only SELECT is selected (deselect INSERT, UPDATE, and DELETE). Select Enable Column Masking Behavior. Click Add.
.	Select STORAGE_A and DATE_A. Click Select.
.	You are returned to the Add Policy page. Click Continue.
.	On the Manage Policy Group: PROVIDER_B_GROUP page, click OK.
.	You receive a confirmation message that the policy group has been successfully modified. Click the Database tab to return to the Database Home page.

Creating a Package

To create a package that will be used by the driving application text, perform the following steps:

.	Click the Schema tab on the Database Home page.
.	Click Packages in the Programs region.
.	Click Create.
.	Enter provider_package in the Name field. Copy and paste the following code into the Source field: as procedure set_provider_context; end; Click OK.
.	You receive a confirmation message.
.	Select Package Body in the Object Type menu and click Create.
.	Enter provider_package in the Name field. Copy and paste the following text into the Source field: AS procedure set_provider_context 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; Click OK.
.	You receive a confirmation message. Click the Database tab to return to the Database Home page.

Creating Application Contexts

To create application contexts, perform the following steps:

.	Click the Server tab on the Database Home page.
.	Click Application Contexts in the Security region.
.	Click Create.
.	Enter PROVIDER_CTX in the Namespace field. Enter SEC_ADMIN.PROVIDER_PACKAGE in the Package Name field. Ensure that "Secure session based - Accessed Locally" is selected in the Type region.
.	Scroll down the page. In the Driving Context region, click Add.
.	Enter APP_SERVER.RFID_DATA in the Object Name field. Enter policy_group in the Attribute field. Click Continue.
.	You are returned to the Create Application Contexts page. Click OK.
.	You receive a confirmation message. Click the Database tab to return to the Database Home page.

Granting EXECUTE Privileges on the Package

To grant EXECUTE privileges on the package, perform the following steps:

.	Click the Server tab on the Database Home page.
.	Click Users in the Security region.
.	Select the APP_SERVER user and click Edit.
.	Click the Object Privileges tab.
.	Select Package in the Select Object Type menu. Click Add.
.	Enter sec_admin.provider_package in the Select Package Objects field. Select EXECUTE in the Available Privileges list. Click Move.
.	Click OK.
.	Click Apply.
.	You receive a confirmation message. Click the Database tab to return to the Database Home page.

Verifying Your Implementation

To verify your implementation of VPD policy groups, perform the following steps:

Note: In the environment used to create this tutorial, the set sqlprompt '_user> ' command was added to the $ORACLE_HOME/sqplus/admin/glogin.sql file.

.	Log in to SQLPlus as the APP_SERVER* user.
.	Execute the exec_pkg_a.sql script. This script switches the context to provider_a and then executes the PROViDER_PACKAGE.SET_PROVIDER_CONTEXT procedure to set the policy group based on context. Finally a query is performed against the RFID_DATA table. Note that as provider_a, on the values in the STORAGE_A and DATE_A columns are displayed.
.	Execute the exec_pkg_b.sql script. This script switches the context to provider_b and then executes the PROViDER_PACKAGE.SET_PROVIDER_CONTEXT procedure to set the policy group based on context. Finally a query is performed against the RFID_DATA table. Note that as provider_b, only the values in the STORAGE_B and DATE_B columns are displayed.
.	Exit from SQL*Plus.

Summary

In this tutorial, you have learned how to:

Create VPD policy groups
Add policies to the policy groups
Create an application context

Resources

Oracle Database Security Guide 11g Release 2 (11.2)

Credits

Lead Curriculum Developer: Donna Keesling
Author: Peter Wahl

	About Oracle \| \| Careers \| Contact Us \| Site Maps \| Legal Notices \| Terms of Use \| Your Privacy Rights