0) { obj.className = "imgborder_on"; } } } function hideImage(obj) { if (obj.className.substr(0, 10) == "imgborder_") { obj.src = eyeglass.src; obj.className = "imgborder_off"; } } function showAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { showImage(imgs[i]); } } function hideAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { hideImage(imgs[i]); } } //-->

OBE Home > 10gR2 Single > Security

Using Oracle Label Security

Purpose

The goal of this tutorial is to use Oracle Label Security to set up security based on label policies.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

Overview
Prerequisites

Setup

Creating a Policy
Setting User Authorizations
Applying a Policy to a Table
Adding Labels to the Data
Creating an Index on OLS_COLUMN
Add a VPD WHERE Clause to the Policy
Revoking Access From Admin Users
Testing the Policy Implementation
Cleanup
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 each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview

Oracle Label Security makes separation of duty easy: When LBACSYS, the default Oracle-DBA for OLS, creates a policy, a role with the name " <policy_name>_DBA" is automatically granted to LBACSYS with the 'ADMIN' option, so that it can be granted to other users for them to complete and 'own the policy. In this tutorial, the users are called " sec_admin" and " HR_sec".

There are three parts to an access control policy:

1.

The table containing the sensitive data ( LOCATIONS) and the owner of this data ( hr), who determines the sensitivity of his data and who will get access to which level of sensitivity.

2. The user-related part of the OLS policy is maintained by a user called HR_sec, who creates database users and roles and grants clearances to them.
3. The Oracle Label Security labels (both for data and users), which enables the access mediation defined by the data owner, are created by sec_admin. Furthermore, this user is responsible for maintaining the performance of the application.

When the policy is tested and ready for production, LBACSYS revokes all necessary execution rights and roles from both " HR_sec" and " sec_admin".

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Perform the Installing Oracle Database 10g on Windows tutorial.

2.

Perform the Installing Oracle Label security tutorial.

3.

Download and unzip the ols.zip file into your working directory ( c:\wkdir).

Back to Topic List

Setup

In this tutorial, you create a set of users and roles to demonstrate how OLS works. To create users and roles, perform the following steps:

1.

Open a terminal window and execute the following commands:

                               
                                 
cd \wkdir
sqlplus /nolog
@ols_create_admin_users_and_roles
                              
                            
set echo off
                              


prompt *** Create admin users: sec_admin and hr_sec
                              


prompt
                              


connect system/oracle
                              


grant connect, create any index to sec_admin identified by welcome1;
                              


grant connect, create user, drop user, create role, drop any role
                              


 to hr_sec identified by welcome1;
                              


prompt
                              


prompt ***** Create roles: emp_role
                              


connect hr_sec/welcome1;
                              


create role emp_role;
                              


prompt ***** Grant system and object privileges to roles and users
                              


connect system/oracle;
                              


grant connect to emp_role;
                              


connect hr/hr;
                              


grant select on hr.locations to emp_role;
                              


connect hr_sec/welcome1;
                              


prompt ***** Create Steven King (President)
                              


create user SKING identified by welcome1;
                              


grant emp_role to SKING;
                              


prompt ***** Create Karen Partners (Sales Manager rep. to SKing)
                              


create user KPARTNER identified by welcome1;
                              


grant emp_role to KPARTNER;
                              


prompt ***** Create Louise Doran (Sales Rep in Karen Partners team)
                              


create user LDORAN identified by welcome1;
                              


grant emp_role to LDORAN;

 

Back to Topic List

Creating a Policy

In this section, you will create a policy, grant the role to the admin users, creates the levels and labels for the policy. Perform the following:

1.

LBACSYS creates a policy which will control access to the hr.LOCATIONS table; the name of the policy is ' ACCESS_LOCATIONS'; the name of the hidden column which will be appended to the hr.LOCATIONS table to hold the data labels is called ' OLS_COLUMN'. From a SQL*Plus session, execute the following script to create your policy. 

                               
                                 
@ols_create_policy

connect lbacsys/lbacsys
                                


BEGIN
                                


SA_SYSDBA.CREATE_POLICY (
                                


  policy_name => 'ACCESS_LOCATIONS',
                                


  column_name => 'OLS_COLUMN',
                                


  default_options => 'READ_CONTROL,LABEL_DEFAULT,HIDE');
                                


END;
                                


/

 
2.

When the policy is created, an administration role for this policy is automatically granted to LBACSYS with the 'admin' option. In order to enable proper separation of duty, LBACSYS grants this role and some additional execution rights to the admin users ' HR_sec' and ' sec_admin'. From a SQL*Plus session, execute the following script: 

                               
                                 
@ols_grant_role

Prompt grant ACCESS_LOCATIONS_DBA to sec_admin and HR_sec:
                                


grant ACCESS_LOCATIONS_DBA to sec_admin;
                                


grant ACCESS_LOCATIONS_DBA to HR_sec;
                                


Prompt  grant execute on SA_COMPONENTS to sec_admin:
                                


grant execute on SA_COMPONENTS to sec_admin;
                                


Prompt  grant execute on SA_USER_ADMIN to HR_sec:
                                


grant execute on SA_USER_ADMIN to HR_sec;

 
3.

The sec_admin user creates the levels for the policy. Each policy consists of levels (one or more), and optional compartments and groups, which are not included in this example. Execute the following script to create levels for your policy. 

                               
                                 
@ols_create_level

connect sec_admin/welcome1;
BEGIN
SA_COMPONENTS.CREATE_LEVEL (
policy_name => 'ACCESS_LOCATIONS',
level_num => 1000,
short_name => 'PUB',
long_name => 'PUBLIC');
END;
/
execute SA_COMPONENTS.CREATE_LEVEL ('ACCESS_LOCATIONS',2000,'CONF','CONFIDENTIAL');
execute SA_COMPONENTS.CREATE_LEVEL ('ACCESS_LOCATIONS',3000,'SENS','SENSITIVE');

 
4.

The sec_admin user also creates the labels (which only contain levels, no compartments or groups). Execute the following script: 

                               
                                 
@ols_create_label
                              
                            
connect sec_admin/welcome1;
execute SA_LABEL_ADMIN.CREATE_LABEL('ACCESS_LOCATIONS',1000,'PUB');


Prompt execute SA_LABEL_ADMIN.CREATE_LABEL('ACCESS_LOCATIONS',2000,'CONF')
execute SA_LABEL_ADMIN.CREATE_LABEL('ACCESS_LOCATIONS',2000,'CONF');


Prompt execute SA_LABEL_ADMIN.CREATE_LABEL('ACCESS_LOCATIONS',3000,'SENS')
execute SA_LABEL_ADMIN.CREATE_LABEL('ACCESS_LOCATIONS',3000,'SENS');

 

Back to Topic List

Setting User Authorizations

Later, data access rights will be limited by applying the labels you created earlier to the data. Before this, you need to authorize users and grant privileges to the policies, in order to define the matching access rights to these users. Perform the following:

1.

The HR_sec user binds the labels to the users, defining their clearance. From a SQL*Plus session, execute the following script to create user label authorizations: 

                               
                                 
@ols_set_user_label

connect hr_sec/welcome1;
                                


BEGIN
                                


SA_USER_ADMIN.SET_USER_LABELS (
                                


  policy_name => 'ACCESS_LOCATIONS',
                                


  user_name => 'SKING',
                                


  max_read_label => 'SENS',
                                


  max_write_label => 'SENS',
                                


  min_write_label => 'CONF',
                                


  def_label => 'SENS',
                                


  row_label => 'SENS');
                                


END;
                                


/
                                


Prompt  Karin Partners is allowed to read public and confidential data
                                


 Prompt  from hr.LOCATIONS.
                                


execute SA_USER_ADMIN.SET_USER_LABELS 
  ('ACCESS_LOCATIONS','KPARTNER','CONF','CONF','PUB','CONF','CONF');
                                


Prompt  Louise Doran is allowed to read public data from hr.LOCATIONS.
                                


execute SA_USER_ADMIN.SET_USER_LABELS 
  ('ACCESS_LOCATIONS', 'LDORAN', 'PUB','PUB','PUB','PUB','PUB');

 
2.

HR, the owner of the LOCATIONS table, needs FULL access to the table, since the user will later add the data labels into the hidden OLS_COLUMN defined earlier. From a SQL*Plus session, execute the following scrip: 

                               
                                 
@ols_set_user_privs

connect hr_sec/welcome1;
                                


execute SA_USER_ADMIN.SET_USER_PRIVS ('ACCESS_LOCATIONS','HR','FULL');

 

Back to Topic List

Applying a Policy to a Table

You can apply Oracle Label Security policies to entire application schemes or to individual application tables. You will apply it to the LOCATIONS table. Perform the following:

1.

The sec_admin user applies the policy to the table. From now on, since READ_CONTROL has been set in the policy definition and no labels are added to the rows, no one can read the data (except HR). Execute the following script: 

                               
                                 
@ols_apply_policy

connect sec_admin/welcome1;
                                


execute SA_POLICY_ADMIN.APPLY_TABLE_POLICY 
  ('ACCESS_LOCATIONS', 'HR', 'locations');

 

Back to Topic List

Adding Labels to the Data

Before you can test the policy, you must add the label to the data by performing the following:

1.

HR, the owner of the LOCATIONS table, adds the labels for each row into the hidden column ' OLS_COLUMNS'. In this case, you will assign the Sensitive label to the cities: Beijing, Tokyo and Singapore. You will assign the Confidential label to the cities: Munich, Oxford and Roma. And all other cities, you will assign the label Public. 

                               
@ols_add_label_column
                            
connect hr/hr;
update locations 
  set OLS_COLUMN = char_to_label('ACCESS_LOCATIONS','SENS')
  where upper(city) in ('BEIJING', 'TOKYO', 'SINGAPORE');
update locations 
  set OLS_COLUMN = char_to_label('ACCESS_LOCATIONS','CONF')
  where upper(city) in ('MUNICH', 'OXFORD', 'ROMA');
update locations 
  set OLS_COLUMN = char_to_label('ACCESS_LOCATIONS','PUB')
  where OLS_COLUMN is NULL;

 

Back to Topic List

Creating an Index on OLS_COLUMN

To improve performance of data access, you can create a BITMAP INDEX on the OLS_COLUMN. Perform the following steps:

1.

In order to increase performance, sec_admin creates a BITMAP INDEX on the OLS_COLUMN: 

                               
@ols_create_index
                            
connect sec_admin/welcome1;
                              


create bitmap index hr.LOCATIONS_idx on hr.LOCATIONS (OLS_COLUMN);

 

Back to Topic List

Add a VPD WHERE Clause to the Policy

In order to limit access for all users to a certain range of IP addresses, SEC_ADMIN uses Oracle Policy Manager to add a WHERE clause to the policy. Perform the following steps:

1.

From a DOS prompt, enter the following command:

                               
oemapp opm


2.

Login as the SEC_ADMIN user.

 
3.

Navigate to the protected table and select the Predicate tab.

 
4.

Check the box to make the text field editable and enter the following text and then click Apply. 

sys_context ('userenv','ip_address') between '130.35.44.0' and '130.35.44.255'

 
5.

Select File > Exit.

 

Back to Topic List

Revoking Access from Admin Users

In order to secure the policy you need to revoke policy-specific execution rights and roles from sec_admin and HR_sec. Perform the following steps:

1.

From your SQL*Plus session, execute the following script:

                               
@ols_revoke_access
                            
connect lbacsys/lbacsys;
                              


Prompt revoke ACCESS_LOCATIONS_DBA from sec_admin and HR_sec:
                              


revoke ACCESS_LOCATIONS_DBA from sec_admin;
                              


revoke ACCESS_LOCATIONS_DBA from HR_sec;
                              


Prompt revoke execute on SA_COMPONENTS from sec_admin:
                              


revoke execute on SA_COMPONENTS from sec_admin;
                              


Prompt revoke execute on SA_USER_ADMIN from HR_sec:
                              


revoke execute on SA_USER_ADMIN from HR_sec;

 

Back to Topic List

Testing the Policy Implementation

After establishing policies to tables and users, and adding labels to the data, you can now test them by performing the following:

1.

Execute the following script to test the access from the SKING user. 

                               
@ols_test_policy_sking
                            
connect SKING/welcome1;
                              


col city heading City format a25
                              


col country_id heading Country format a11
                              


col Label format a10
                              


select city, country_id, label_to_char (OLS_COLUMN)
   as Label from hr.locations order by ols_column;

Note that the user SKING can see PUBLIC, CONFIDENTIAL and SENSITIVE data.

 
2.

Now you can test the policy for the KPARTNER user by executing the following script: 

                               
@ols_test_policy_kpartner

connect kpartner/welcome1;
col city heading City format a25
col country_id heading Country format a11
col Label format a10
select city, country_id, label_to_char (OLS_COLUMN) 
  as Label from hr.locations order by ols_column;

Note that KPARTNER can see PUBLIC and CONFIDENTIAL data.

 
3.

Now you can test the PRIVACY policy by executing the following script: 

                               
@ols_test_policy_ldoran

connect ldoran/welcome1;
col city heading City format a25
col country_id heading Country format a11
col Label format a10
select city, country_id, label_to_char (OLS_COLUMN) 
  as Label from hr.locations order by ols_column;

Note that LDORAN can only see PUBLIC data.

 

Back to Topic List

Cleanup

Now that you have tested your policies, you can drop the users and the policies by performing the following:

1.

Execute the following script:

                               
                                 
@ols_cleanup
                              
                            
                               
Prompt ====================================================================
                                


prompt  Clean up:
                                


prompt ====================================================================
                                


Prompt connect LBACSYS
                                


conn lbacsys/lbacsys;
                                


prompt ====================================================================
                                


prompt  Drop policy (and remove label column) in case it already exists.
                                


prompt (Ignore the error message when it does not exist).
                                


prompt ====================================================================
                                


execute sa_sysdba.drop_policy ('ACCESS_LOCATIONS', true);
                                


Prompt connect HR_sec
                                


conn HR_sec/welcome1;
                                


prompt ====================================================================
                                


prompt  HR_sec drops database users and roles (Ignore the error messages
                                


prompt  should they not exist):
                                


prompt ====================================================================
                                


drop user SKING cascade;
                                


drop user KPARTNER cascade;
                                


drop user LDORAN cascade;
                                


drop role emp_role;
                                


Prompt connect SYSTEM
                                


conn system/oracle;
                                


prompt ====================================================================
                                


prompt  SYSTEM drops admin users (Ignore the error messages should
                                


prompt  they not exist):
                                


prompt ====================================================================
                                


drop user sec_admin cascade;
                                


drop user HR_sec cascade;

 

Back to Topic List

Summary

In this tutorial, you learned how to:

Create a policy
Set user authorizations
Apply a policy to a table
Add labels to the data
Test the policy

Back to Topic List

Place your cursor on this icon to hide all screenshots.