Chapter 6. Beyond Privileges: Oracle Label Security

In this chapter we will cover:

• Creating and using label components

• Defining and using compartments and groups

• Using label policy privileges

• Using trusted stored units

Introduction

The name "labels" comes from the main component used to secure data, namely the data label. Oracle Label Security (OLS) is a security framework that protects data through the use of a hierarchical access model. A properly designed OLS installation can allow sensitive data to be located within the same table as less sensitive information, by allowing for more fine-grained data access restrictions than can be applied with traditional SELECT privileges, without the complexity involved with writing additional code such as VPD policy functions. Since the controls are implemented by the Oracle kernel at the data row access level, OLS provides a secure protection capability and is often used in environments that need to protect classified information. It is a separate security feature and must be licensed.

Most of the threats, which can be confronted by using OLS, may originate from users with too many rights. Here we can include system, sys, DBA users, and an attacker, who after a...

Creating and using label components

In this recipe, we will create four users and a table called EMPLOYEES_OLS_TBL. The users in this recipe will receive rights to select data from the table REG_DATA_TBS according to their hierarchy level. All steps will be performed using the Oracle labels PL/SQL interface.

Getting ready

As a prerequisite, you must have OLS installed. Details about the installation can be found in the OLS documentation link http://docs.oracle.com/cd/B28359_01/network.111/b28529/getstrtd.htm#CIHBBJFA. Another detailed description can be found on the Oracle Support doc:

How to Install/Deinstall Oracle Label Security [ID 171155.1]

All steps will be performed on the HACKDB database.

How to do it...

In the following steps, we will create a new table EMPLOYESS_OLS_TBL and label it according to each user's hierarchical position:

1. Connect as the HR user and create the table EMPLOYEES_OLS_TBL as follows:

   SQL> conn HR
   Enter password:
   Connected.
   SQL> 
   CREATE TABLE EMPLOYEES_OLS_TBL...

Defining and using compartments and groups

By using compartments and groups, the granularity of access to data might be increased considerably. In this recipe, we will cover how to implement additional groups and compartments in the table EMPLOYEES_OLS_TBL. The compartments will be created based on departments, and the groups will be based on countries and regions.

Getting ready

All steps will be performed on the HACKDB database.

How to do it...

Before we start, it is mandatory to find and design a method to compartmentalize and group the data. This is shown in the following steps:

1. Each department manager must be able to see his own data. There will also be users with permissions to see the compartmentalized and grouped data corresponding to their departments. As the user SKING, issue the following SELECT to gather the needed information:

   SQL> conn SKING
   Enter password:
   Connected.
   SQL> select distinct job_title,country_name,email from hr.EMPLOYEES_OLS_TBL where job_title like '%Manager...

Using label policy privileges

Oracle labels have a set of privileges, which can be used to bypass the current privileges in certain conditions, such as performing exports on label-protected tables or other operations that need to read or update the entire table. The same is true for other DML statements such as INSERT and DELETE.

In the previous recipe, we gave FULL policy privilege to the user HR. In this recipe, we will create a new user OLSAUTH, who will be granted a special privilege called PROFILE_ACCESS.

Getting ready

All the steps will be performed on the database HACKDB.

How to do it...

Usually special privileges should be granted to dedicated users. This can be done as follows:

1. Connect as the user system, create user OLSAUTH and grant create session and SELECT on the table EMPLOYESS_OLS_TBL, as follows:

   SQL> conn system
   Enter password:
   Connected.
   SQL> create user OLSAUTH identified by OLSAUTH;
   
   User created.
   SQL> grant create session to employees_ols_tbl to OLSAUTH;
   SQL>...

Using trusted stored units

Trusted stored units are usually used to allow specific users to perform operations on tables protected by labels. In this recipe, we will grant the READ privilege on a specific result set to the user SMAVRIS, through a trusted stored unit.

Getting ready

All the steps will be performed on the database HACKDB.

How to do it...

We grant the READ privilege to the user SMAVRIS, on a specific result set, using the trusted stored unit as follows:

1. We will create a function which returns a result set from EMPLOYEES_OLS_TBL. Connect as the user HR and create the function ols_tru_stored_unit as follows:

   SQL> conn HR
   Enter password:
   Connected.
   SQL> create or replace function ols_tru_store_unit RETURN sys_refcursor
     2  is
     3  ret_cur sys_refcursor;
     4  begin
     5  open ret_cur for select count(*) as no_employees, department_name as department from employees_ols_tbl
     6  group by department_name;
     7  return ret_cur;
     8  end;
     9  /
   
   Function created.
   
   SQL>
   

2. Test the function...