Introduction to Oracle Label Security 12c

In the previous post, we looked at how to support fine-grained access control of tables using Oracle VPD technology. In this post, we are going to have a quick look at another technology with similar purpose called OLS (Oracle Label Security) which is built on top of VPD.

A commonly raised question is that if both VPD and OLS serve the same purpose, why do we need OLS? While VPD is a generic solution, OLS addresses a very specific and common use case in many government and corporate environment - allow access to data based on the need to know basis. Unlike VPD which requires administrator to write PL/SQL code for the policy function, OLS requires no programming. You can think of OLS as a out-of-the-box VPD with built-in policies.

What exact does OLS address?
Many government and business organizations, label their data using different sensitivity levels, such as top secret (TS), secret (S), confidential (C), unclassified (UC), depending on the perceived risk levels so that they can selectively protect most valuable assets of the organization. Users are assigned different clearance levels based on their job functions and seniority. Each user is allowed to access only those data records with the sensitivity level no more than the clearance level of the user. For example, if user tom is assigned the clearance level C, he can access data records with the sensitive levels C or UC only. He is not allowed to access data records with sensitivity levels greater than C, that is, TS or S.

How is OLS enforced?
OLS is a security in depth feature, meaning that users must first have the DAC table privileges necessary to access the OLS protected rows. On top that, there could be VPD policies that may be enforced as well. The following diagram shows the order of enforcement. (Not shown in the diagram: a database vault policy may be enforced on the table as well.)

Figure: OLS Enforcement

How does OLS work?
The following diagram shows the example that we will walk through later in this post. The table flight has data of different sensitivity level and based users' clearance levels they see different set of records even though they issue the same query. Row level access control is enforced transparent to end users.

Figure: OLS Example

How are row sensitivity labels assigned?
When a new record is inserted to an OLS protected table, there are three ways to assign sensitivity label to each row:
- Explicit specify a label (which should be between the maximum and minimum clearance of the user)
- Select 'LABEL_DEFAULT' option when creating the policy and the sessions ROW_LABEL value will be used automatically when inserting records
- Create a labeling function and specify when applying the policy on the table. This will automatically call your labeling function when inserting records

How are user clearance levels decided?
If it is a database user, SA_USER_ADMIN.SET_LEVELS procedure assigns a default level to each user. At the time of login user will have this clearance level. In a session, user can change the clearance level to a level less than the maximum level granted using SA_SESSION.SET_LABEL procedure.

If it is an application user who does not have a database user, SA_SESSION.SET_ACCESS_PROFILE procedure allows to set an existing database user's profile to be the profile of the application user.

A working example:
SQL> -- First do the test setup
SQL> conn sys/password@cdb1_pdb1 as sysdba
Connected.
SQL> create user gov identified by gov;
User created.

SQL> create user bob identified by bob;
User created.

SQL> create user tim identified by tim;
User created.

SQL> grant create session to gov, bob, tim;
Grant succeeded.

SQL> grant create table, unlimited tablespace to gov;
Grant succeeded.

SQL> conn gov/gov@cdb1_pdb1
Connected.
SQL> create table flight(flight# number, destination varchar2(100), payload varchar2(100));
Table created.

SQL> grant select, insert on flight to bob, tim, scott;
Grant succeeded.

SQL> -- insert test data
SQL> insert into flight values (505, 'Iraq', 'Weapon');
1 row created.
SQL> insert into flight values (506, 'Canada', 'Charcoal');
1 row created.
SQL> insert into flight values (706, 'Japan', 'Battery');
1 row created.
SQL> insert into flight values (501, 'Syria', 'Weapon');
1 row created.
SQL> insert into flight values (508, 'Israel', 'Jets');
1 row created.
SQL> insert into flight values (509, 'India', 'Aid');
1 row created.

SQL> 
SQL> conn lbacsys/password@cdb1_pdb1
Connected.
SQL> BEGIN
  2  -- Create OLS Policy
  3  -- Notice that the default_options is set to no_control to disable the policy
  4  -- in order add labels to the existing data items
  5  SA_SYSDBA.CREATE_POLICY(
  6    policy_name => 'ols_pol1',
  7    column_name => 'lb_col',
  8    default_options => 'no_control'
  9  );
 10  

 11  -- Create label component levels

 12  -- TOP_SECRET has the highest level of access

 13  SA_COMPONENTS.CREATE_LEVEL(

 14    policy_name => 'ols_pol1',

 15    level_num   => 4,

 16    short_name => 'TS',

 17    long_name   => 'top_secret'

 18  );

 19  

 20  

 21  SA_COMPONENTS.CREATE_LEVEL(

 22    policy_name => 'ols_pol1',

 23    level_num   => 3,

 24    short_name => 'S',

 25    long_name   => 'secret'

 26  );

 27  

 28  SA_COMPONENTS.CREATE_LEVEL(

 29    policy_name => 'ols_pol1',

 30    level_num   => 2,

 31    short_name => 'C',

 32    long_name   => 'confidential'

 33  );

 34  

 35  SA_COMPONENTS.CREATE_LEVEL(

 36    policy_name => 'ols_pol1',

 37    level_num   => 1,

 38    short_name => 'UC',

 39    long_name   => 'unclassified'

 40  );

 41  

 42  -- Create data labels

 43  SA_LABEL_ADMIN.CREATE_LABEL(

 44    policy_name => 'ols_pol1',

 45    label_tag   => 40,

 46    label_value => 'TS',

 47    data_label  => TRUE

 48  );

 49  

 50  SA_LABEL_ADMIN.CREATE_LABEL(

 51    policy_name => 'ols_pol1',

 52    label_tag   => 30,

 53    label_value => 'S',

 54    data_label  => TRUE

 55  );

 56  

 57  SA_LABEL_ADMIN.CREATE_LABEL(

 58    policy_name => 'ols_pol1',

 59    label_tag   => 20,

 60    label_value => 'C',

 61    data_label  => TRUE

 62  );

 63  

 64  SA_LABEL_ADMIN.CREATE_LABEL(

 65    policy_name => 'ols_pol1',

 66    label_tag   => 10,

 67    label_value => 'UC',

 68    data_label  => TRUE

 69  );

 70  

 71  -- Apply access_pol policy on table gov.flight

 72  SA_POLICY_ADMIN.APPLY_TABLE_POLICY(

 73    policy_name       => 'ols_pol1',

 74    schema_name       => 'gov',

 75    table_name        => 'flight',

 76    table_options  => null,

 77    label_function => null,

 78    predicate         => null

 79  );

 80  

 81  -- Add user authorizations (i.e. clearance levels)

 82  SA_USER_ADMIN.SET_LEVELS(

 83    policy_name => 'ols_pol1',

 84    user_name   => 'bob',

 85    max_level   => 'S',

 86    min_level   => 'UC',

 87    def_level   => 'S',

 88    row_level   => 'S'

 89  );

 90  

 91  SA_USER_ADMIN.SET_LEVELS(

 92    policy_name => 'ols_pol1',

 93    user_name   => 'tim',

 94    max_level   => 'UC',

 95    min_level   => 'UC',

 96    def_level   => 'UC',

 97    row_level   => 'UC'

 98  );

 99  END;

100  /

PL/SQL procedure successfully completed.

SQL> 

SQL> conn system/password@cdb1_pdb1

Connected.

SQL> -- Add labels to rows

SQL> update gov.flight set lb_col = char_to_label('ols_pol1', 'TS') where payload in ('Weapon');

2 rows updated.

SQL> update gov.flight set lb_col = char_to_label('ols_pol1', 'S') where payload in ('Jets');

1 row updated.

SQL> update gov.flight set lb_col = char_to_label('ols_pol1', 'C') where payload in ('Battery');

1 row updated.

SQL> update gov.flight set lb_col = char_to_label('ols_pol1', 'UC') where payload in ('Charcoal', 'Aid');

2 rows updated.

SQL> 

SQL> conn lbacsys/password@cdb1_pdb1

Connected.

SQL> BEGIN

  2  -- Now we change the policy to enfoce on read by first altering the policy

  3  -- and then removing and applying the policy again

  4  SA_SYSDBA.ALTER_POLICY(

  5    policy_name => 'ols_pol1',

  6    default_options => 'read_control, label_default'

  7  );

  8  

  9  SA_POLICY_ADMIN.REMOVE_TABLE_POLICY(

 10    policy_name => 'ols_pol1',

 11    schema_name => 'gov',

 12    table_name  => 'flight',

 13    drop_column => false

 14  );

 15  

 16  SA_POLICY_ADMIN.APPLY_TABLE_POLICY(

 17    policy_name => 'ols_pol1',

 18    schema_name => 'gov',

 19    table_name  => 'flight'

 20  );

 21  END;

 22  /

PL/SQL procedure successfully completed.

SQL> -- This is to demonstrate OLS can be bypassed with

SQL> -- special privileges. Here, scott is granted to read

SQL> -- the OLS protected table without any specific 

SQL> -- clearance level  

SQL> BEGIN

  2  SA_USER_ADMIN.SET_USER_PRIVS(

  3    policy_name  => 'ols_pol1',

  4    user_name    => 'scott',

  5    privileges   => 'READ'

  6  );

  7  END;

  8  /

PL/SQL procedure successfully completed.

SQL> column flight# format 9999;

SQL> column destination format a15;

SQL> column payload format a15;

SQL> 

SQL> 

SQL> conn bob/bob@cdb1_pdb1

Connected.

SQL> -- The following demonstrates how to change the row label

SQL> -- of the current session so that that label is used

SQL> -- when bob is inserting new records to gov.flight. 

SQL> select SA_SESSION.ROW_LABEL('ols_pol1') from DUAL;

SA_SESSION.ROW_LABEL('OLS_POL1')

--------------------------------

S

SQL> select SA_SESSION.LABEL('ols_pol1') from DUAL;

SA_SESSION.LABEL('OLS_POL1')

----------------------------

S

SQL> 

SQL> BEGIN

  2  SA_SESSION.SET_ROW_LABEL(

  3    policy_name  => 'ols_pol1',

  4    label        => 'UC'

  5  );

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL> -- The label of this row is automatically assigned as UC

SQL> insert into gov.flight (flight#, destination, payload) values (599, 'Peru', 'Medicine');

1 row created.

SQL> select flight#, destination, payload from gov.flight;

FLIGHT# DESTINATION     PAYLOAD

------- --------------- ---------------

    506 Canada          Charcoal

    706 Japan           Battery

    508 Israel          Jets

    509 India           Aid

    599 Peru            Medicine

SQL> 

SQL> conn tim/tim@cdb1_pdb1

Connected.

SQL> select flight#, destination, payload from gov.flight;

FLIGHT# DESTINATION     PAYLOAD

------- --------------- ---------------

    506 Canada          Charcoal

    509 India           Aid

    599 Peru            Medicine

SQL> 

SQL> conn scott/tiger@cdb1_pdb1

Connected.

SQL> select * from gov.flight;

FLIGHT# DESTINATION     PAYLOAD             LB_COL

------- --------------- --------------- ----------

    505 Iraq            Weapon                  40

    506 Canada          Charcoal                10

    706 Japan           Battery                 20

    501 Syria           Weapon                  40

    508 Israel          Jets                    30

    509 India           Aid                     10

    599 Peru            Medicine                10

7 rows selected.

SQL> -- Now we do the clean up

SQL> conn lbacsys/password@cdb1_pdb1

Connected.

SQL> BEGIN

  2  SA_SYSDBA.DROP_POLICY(

  3    policy_name => 'ols_pol1'

  4  );

  5  END;

  6  /

PL/SQL procedure successfully completed.

SQL> 

SQL> conn sys/password@cdb1_pdb1 as sysdba

Connected.

SQL> drop user gov cascade;

User dropped.

SQL> drop user bob cascade;

User dropped.

SQL> drop user tim cascade;

User dropped.

There is more to OLS:
I only scratched the surface of OLS. A data label is actually consists of three components:

1. Sensitivity level - has an ordering relationship
2. Compartment (optional) - used to segregate data within a level
3. Group (optional) - used to organizationally segregate data within a level - has a hierarchical relationship

In order for a user to able to access a labeled record, the following conditions must be satisfied:
a. User's clearance level should be at least as high as the the data record's sensitivity level
b. User has all the compartments associated with the data record
c. User belongs to one of the groups associated with the data record or a parent group of one of the associated groups

With the quick understanding of the OLS technology, I encourage you to look the Oracle documentation to learn more about OLS and its new powerful features.

Introduction to Oracle Virtual Private Database (VPD) 12c

Before we get our hands on Virtual Private Databases (VPDs), let's try to understand if there is a real use case for VPD? Organizations have data of different sensitivity levels. For example, highly confidential trade secrets to classified publicly available news articles. From the point of view of databases, organizations needs to store data of different sensitivity levels in database tables and allow different users to access different set of data. For example, a manager is allowed to access records for all employees working under him/her whereas an engineer is allowed to access only his employment record. Oracle DAC privileges (or the privileges in any other major database system for that matter) either allow or deny access to the complete database objects such as tables, and views. There is no way to grant partial access to tables or views. What can we do about it?

One possible solution is to segregate data into different tables based on access control requirements. For the employee table example above, for each manager, one employee table with all of his/her subordinates could be created. That's a little too many tables! A better idea would be to create one table and multiple views on the same table for each manager. Even such an approach could be difficult to manage with so many objects and grants. Further, such approaches are not transparent to users as users need to know which table/view they can access in order to view employee records. Is there a magical way to do this segregation overcoming the above challenges? VPD is the answer! A bunch of smart people at Oracle got together and implemented a way to transparently present different records to different users based on context information. VPD has been around since Oracle 8i. Over time, now 12c, VPD has matured and evolved to support many powerful features. In this post, we are going to look at the very basic features of VPD as our goal is to get an idea of what VPD does. Once you have a hang of it, it is just a matter of referring to the user guide to absorb more advanced features of VPD.

So, what exactly VPD does? The basic idea behind VPD is really simple. It modifies user queries to add a predicate to show different set of records to different users. The query modification is transparent to users and based on the context information. The following diagram shows a very simple example.

Figure: A simple VPD example

As you can see from the above diagram, the context sensitive access policy is as follows:
"An employee can access her records and her subordinates' records if she is a manager"

Notice that VPD is transparent to database users. All employees execute the same query "select * from hr.emp". However, based on the access control policy, different users see different set of records. VPD does this by adding the predicate "upper(user) = SYS_CONTEXT('USERENV', 'CURRENT_USER') OR upper(manager) = SYS_CONTEXT('USERENV', 'CURRENT_USER'), where SYS_CONTEXT('USERENV', 'CURRENT_USER') returns the current database user.

Simple Code Example:
The following CDB setting is used to illustrate a dynamic VPD policy in action. (There are a few types of VPD polices such static, shared-static, and shared context-sensitive. These different types decide when to re-evaluate VPD polices as they use different caching strategies. For dynamic VPD policies, no caching strategy is used and policies are evaluated every time the a related statement is parsed or executed.)

Figure: CDB with a single PDB

Create the necessary users and grant privileges.
SQL> CONN sys/password@cdb1_pdb1 AS SYSDBA
Connected.

SQL> CREATE USER bob IDENTIFIED BY bob;
User created.

SQL> CREATE USER anne IDENTIFIED BY anne;
User created.

SQL> CREATE USER tim IDENTIFIED BY tim;
User created.

SQL> CREATE USER hr IDENTIFIED BY hr;
User created.

SQL> CREATE USER vpdadmin IDENTIFIED BY vpdadmin;
User created.

SQL> 
SQL> GRANT CREATE SESSION TO hr, bob, anne, tim, vpdadmin;
Grant succeeded.

SQL> GRANT CREATE TABLE, UNLIMITED TABLESPACE TO hr;
Grant succeeded.

Notice that one needs execute privilege on DBMS_RLS package in order to manage VPD policies.
SQL> GRANT EXECUTE ON DBMS_RLS TO vpdadmin;
Grant succeeded.

SQL> GRANT CREATE PROCEDURE TO vpdadmin;
Grant succeeded.

SQL> 
SQL> CONN hr/hr@cdb1_pdb1
Connected.
SQL> CREATE TABLE emp (ename VARCHAR2(50), title VARCHAR2(50), manager VARCHAR2(50), deptid NUMBER, salary NUMBER);
Table created.

SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('bob', 'manager', NULL, 1, 10000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('alice', 'engineer', 'bob', 1, 8000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('tim', 'engineer', 'bob', 1, 8000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('eve', 'analyst', 'bob', 1, 7500);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('anne', 'manager', NULL, 2, 10500);
1 row created.

SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('denis', 'engineer', 'anne', 2, 9000);
1 row created.
SQL> INSERT INTO emp (ename, title, manager, deptid, salary) VALUES ('jack', 'engineer', 'anne', 2, 9500);
1 row created.

SQL> 
SQL> GRANT SELECT ON emp TO bob, anne, tim;
Grant succeeded.

Now vpdadmin user creates the VPD policy; first creates the policy function and then uses it to create the policy.
SQL> 
SQL> CONN vpdadmin/vpdadmin@cdb1_pdb1
Connected.
SQL> -- Should have two input arguments and a return value - all of them are
SQL> -- of type VARCHAR2
SQL> -- Return value should be a predicate (that fits in the WHERE clause).
SQL> CREATE OR REPLACE FUNCTION get_emp_pred
  2    (schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
  3    BEGIN
  4      RETURN 'upper(ename) = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'') OR upper(manager) = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
  5    END;
  6  /
Function created.


SQL> -- create VPD policy
SQL> -- Note: for INSERT statements, you need to specify update_check => true as well
SQL> BEGIN
  2    SYS.DBMS_RLS.ADD_POLICY(
  3      object_schema   => 'hr',
  4      object_name     => 'emp',
  5      policy_name     => 'emp_vpd1',
  6      function_schema => 'vpdadmin',
  7      policy_function => 'get_emp_pred',
  8      statement_types => 'select'
  9    );
 10  END;
 11  /
PL/SQL procedure successfully completed.

SQL> 
SQL> column ename format a10;
SQL> column title format a10;
SQL> column manager format a10;
SQL> column deptid format 99;
SQL> column salary format 999999;
SQL> 
SQL> CONN bob/bob@cdb1_pdb1

Connected.

SQL> select * from hr.emp;

ENAME      TITLE      MANAGER    DEPTID  SALARY

---------- ---------- ---------- ------ -------

bob        manager                    1   10000

alice      engineer   bob             1    8000

tim        engineer   bob             1    8000

eve        analyst    bob             1    7500

SQL> 

SQL> CONN tim/tim@cdb1_pdb1

Connected.

SQL> select * from hr.emp;

ENAME      TITLE      MANAGER    DEPTID  SALARY

---------- ---------- ---------- ------ -------

tim        engineer   bob             1    8000

SQL> 

SQL> CONN anne/anne@cdb1_pdb1

Connected.

SQL> select * from hr.emp;

ENAME      TITLE      MANAGER    DEPTID  SALARY

---------- ---------- ---------- ------ -------

anne       manager                    2   10500

denis      engineer   anne            2    9000

jack       engineer   anne            2    9500

Now we are going to clean up.

SQL> 

SQL> CONN vpdadmin/vpdadmin@cdb1_pdb1

Connected.

SQL> BEGIN

  2    SYS.DBMS_RLS.DROP_POLICY(

  3      object_schema => 'hr',

  4      object_name   => 'emp',

  5      policy_name   => 'emp_vpd1'

  6    );

  7  END;

  8  /

PL/SQL procedure successfully completed.

SQL> 

SQL> CONN sys/password@cdb1_pdb1 as SYSDBA

Connected.

SQL> DROP USER bob CASCADE;

User dropped.

SQL> DROP USER anne CASCADE;

User dropped.

SQL> DROP USER tim CASCADE;

User dropped.

SQL> DROP USER hr CASCADE;

User dropped.

SQL> DROP USER vpdadmin CASCADE;

User dropped.

Today we looked at a very basic example of VPD. In a real world application, a user defined context is utilized to construct the VPD predicate. In addition to row level control, VPD allows column level control  as well.