Using Oracle Label Security User Clearances Within Virtual Private Database Policies

Purpose

This tutorial shows you how to use Oracle Label Security user authorizations within Virtual Private Database policies.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Creating a Label Security Policy
 Setting User Security Clearances
 Creating and Applying the Virtual Private Database Policy
 Querying the HR_INFO Table
 Removing the Objects
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

What is Oracle Label Security?

Oracle Label Security enables powerful row level access controls in the Oracle Database using data sensitivity labels. Policy based administration provides flexibility for a wide range of use cases from healthcare to law enforcement. Oracle Label Security extends database security authorizations beyond traditional roles, enabling powerful factors for use in Oracle Database Vault and other security products.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g Release 1. Create a database with the sample schemas (either during the installation process or afterwards).

2.

Install Oracle Label Security.

3.

Download and unzip the olsvpd.zip file.

4.

Grant the SELECT ANY DICTIONARY system privilege to the LBACSYS user so the user can log in to Enterprise Manager Database Control and create label policies.

Back to Topic List

Creating a Label Security Policy

Oracle Label Security consists of two parts: user clearance labels and data classification labels (row labels). The policy itself determines access rights to rows by comparing the user's clearance with data classification labels. In this example, the Label Security policy will not be applied to a table. No additional column is appended to the protected table, which would otherwise hold the row labels.To create a Label Security policy that stores user clearance labels, perform the following steps:

1.

Enter the following URL to log in to Enterprise Manager Database Control (change <hostname> to localhost or your specific hostname).

https://<hostname>:<port>/em

 

2.

Log in to Enterprise Manager Database Control as the LBACSYS user. Click Login.

 

3.

Click the Server tab and select Oracle Label Security in the Security section .

 

4.

On the Label Security Policies page, click Create .

 

5.

Enter PROTECT_PII in the Name field and LABEL_COLUMN in the Label Column field. Deselect (uncheck) "Enabled." Because this policy will never be attached to a table, the default policy enforcement option can be set to 'NO_CONTROL', which minimizes any overhead caused by a truly active policy. Accept the default of " Apply No Policy Enforcements (NO_CONTROL)" Click Label Components.

 

6.

In the Levels section, enter information as follows:

Long Name Short Name Numeric Tag
CONFIDENTIAL C 1000
SENSITIVE S 2000

 

 

7.

In the Compartments section, enter information as follows. Click OK.

Long Name Short Name Numeric Tag
PERS_INFO PII 100

 

 

8.

You receive a message that the policy has been created successfully.

 

Back to Topic List

 

Setting User Security Clearances

Only the HR_MANAGER user gets a label which is equal to or dominates the S:PII label. This allows HR_MANAGER to see all columns of the table which contain PII information. All other users have labels that do not dominate the S:PII label. For those users, the SALARY column will be empty. Now that you have created the label security policy, perform the following steps to set the user security clearances:

1.

Log in to SQL*Plus as the SYSTEM user and execute the olsvpd_cr_hrapp.sql script to create the HR_APP user and the HR_APP.HR_INFO table.

 

2.

Return to your Enterprise Manager Database Control session. On the Label Security Policies page, select the PROTECT_PII policy. Select Authorization in the Actions menu and click Go.

 

3.

Click Add Users.

 

4.

In the Non Database Users section, click Add 5 Rows.

 

5.

Enter HR_MANAGER in the Name field and click Next.

 

6.

On the Add Users Privileges page, click Next.

 

7.

On the Add Users: Levels, Compartments, and Groups page, enter S in the Maximum Level, Default Level, and Row Level fields. Enter C in the Minimum Level field.

 

8.

Click Add in the Compartments section.

 

9.

Select PII and click Select.

 

10.

Select PII. Select Default. Click Next.

 

11.

On the Add Users: Audit page, click Next.

 

 

12.

On the Add Users: Review page, review the information you have entered. Click Finish.

 

13.

You receive a confirmation message. Click Add Users to add another user.

 

14.

In the Non Database Users section, click Add 5 Rows .

15.

Enter HR_CLERK in the Name field and click Next.

 

16.

On the Add Users: Privileges page, click Next.

 

17.

On the Add Users: Levels, Compartments, and Groups page, enter C in the Maximum Level, Minimum Level, Default Level, and Row Level fields. Click Next.

 

18.

On the Add Users: Audit page, click Next.

 

 

19.

On the Add Users: Review page, review the information you have entered. Click Finish.

 

20.

You receive a confirmation message. Click Add Users to return to the Add Users: Users page.

 

21.

In the Database Users section, click Add.

 

22.

On the Search and Select page, enter HR_APP and click Go.

 

23.

Select HR_APP and click Select.

 

24.

Select HR_APP and click Next.

 

25.

On the Add Users Privileges page, select " Assume profile of another user through set_access_profile (PROFILE_ACCESS)" and click Next.

 

26.

On the Add Users: Levels, Compartments And Groups page click Next.

 

27.

On the Add Users: Audit page click Next.

 

28.

On the Add Users: Review page, review the information and click Finish.

 

29.

You receive a confirmation message. Click Label Security Policies to return to the Label Security Policies page.

 

Back to Topic List

Creating and Applying the Virtual Private Database Policy

In a previous topic, you created user clearance labels. Now you will create the Virtual Private Database (VPD) policy. The VPD policy will do the following:

Get the numerical label tag from the user's current label
Get the numerical label tag from the 'S:PII' label
User label ≥ 'S:PII' allows access to all rows in sensitive columns
User label < 'S:PII' allows access to all rows, but sensitive PII column is null

Perform the following steps to create and apply the Virtual Private Database policy:

1.

Log in to SQL*Plus as the LBACSYS user and execute the olsvpd_cr_protpii_fcn.sql script to create the function that will be used in the Virtual Private Database policy.

 

2.

If you are no longer logged in to Enterprise Manager Database Control, log in again as the LBACSYS user.

3.

Navigate to the Server page and select Virtual Private Database Policies.

4.

On the Virtual Private Database Policies page, click Create.

 

5.

In the General section, enter vpd_protect_pii in the Policy Name field, hr_app.hr_info in the Object Name field, and select CONTEXT_SENSITIVE in the Policy Type drop-down menu.

 

6.

In the Policy Function section of the page, enter LBACSYS.F_PROTECT_PII .

 

7.

In the Enforcement section of the page select SELECT.

 

8.

In the Security Relevant Columns section, click Add.

 

9.

Select the SALARY column and click Select.

 

10.

Select Enable Column Masking Behavior. Click OK.

 

11.

You receive a confirmation message that the policy has been created.

 

12.

Click the Database Instance link to return to the Server page.

Back to Topic List

Querying the HR_INFO Table

Perform the following steps to query the HR_INFO table. Note the difference in display of values in the SALARY column:

1.

Log in to SQL*Plus as the HR_APP user with a password of welcome1 . Execute the olsvpd_query_manager.sql script.

 

2.

Execute the olsvpd_query_clerk.sql script.

 

Back to Topic List

Removing the Objects

Perform the following steps to remove the objects you created in this tutorial:

1.

Log in to SQL*Plus as the LBACSYS user. Execute the olsvpd_remove_policy.sql script to remove the PROTECT_PII policy.

 

 

2.

Connect as the SYSTEM user. Execute the olsvpd_remove_hrapp.sql script to delete the HR_APP user.

 

 

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Create a label security policy
 Set user security clearances
 Create and apply the virtual private database policy

Back to Topic List

 Place the cursor over this icon to hide all screenshots.