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.
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.
3 comments:
Thank you very much for simplifying things for the public. Keep Posting more!! God Bless!!
Thank you for this information.
Oracle dba training in chennai | Oracle course in Chennai
Nice one. There might be a slight catch, we must configure and enable Oracle label security before execute the procedures.
Post a Comment