Oracle Label Security Demos
Versions 10.1 - 20c

General
Oracle Label Security (OLS) controls the display of individual table rows using labels that are assigned to specific individual table rows and application users. With well configured OLS security it is possible to design and deploy complex security requirements without the use of complex code and complex maintenance.

Benefits of Oracle Label Security: Oracle Label Security provides several benefits for controlling row level management.
  • Enables row level data classification and provides out-of-the-box access mediation based on the data classification and the user label authorization or security clearance.
  • Enables assignment of label authorizations or security clearances to both database users and application users.
  • Provides both APIs and a graphical user interface for defining and storing data classification labels and user label authorizations.
  • Integrates with Oracle Database Vault and Oracle Advanced Security Data Redaction, enabling security clearances to be use in both Database Vault command rules and Data Redaction policy definitions.

The concept is that the application is an already existing application and that no changes will be required to the application to implement Label Security (OLS)

The application consists of a single table and the data persisted in that table will be used to define multiple access levels without any changes to the existing data.

The current design of the Security Application, SECAPP, does not meet the dynamic needs to change the security level of single data rows without a substantial investment in application development and testing. In addition, management has a very rational concern, based on industry experience, that application changes only impact application users and that those that break in and attack databases are not limited by desire to follow the rules and use the approved application.

What bugs me about other OLS demos is that they rely solely upon the information necessary for labeling to be in the application data which means that it is reasonable to assume that an application modification is required to use OLS. The following demo assumes that 2 pieces of information are present in the application data and the third is provided by the security administrator.

Explain Level: This is essentially the security level such as LOW RISK, MEDIUM RISK, HIGH RISK or sensitivity such as Confidential, Secure, Eyes Only
Explain Compartment: 
Explain Groups:

Label Component Description
Level A hierarchical component that denotes the sensitivity of the data frequently used with government and defense data. Every data label must have a level. An organization might define levels such as Confidential, Sensitive and Highly Sensitive. If an organization doesn’t need multiple levels, a single default level needs to be defined.
Compartment An optional, non-hierarchical, component often referred to as a category. One or more compartments are defined to compartmentalize a row of data. Compartments might be defined for a specific type of data, knowledge area, geography, or project that requires special approval.
Group An optional component similar to the Compartment with a few exceptions. Groups can have a parent child relationship and  Groups are often used to segregate data by organization though they can be used for other purposes as well.

x
 
Application Design
The existing application consists of a single table, residing in its own tablespace, and a stored procedure that can be used to access table data. The application currently has three different users identified by their privilege level (LOW, MEDium, and MAXimum).
Tablespace and Users CREATE TABLESPACE olstbs
DATAFILE 'c:\temp\source\olstbs.dbf' SIZE 104857600
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

-- mech application schema owner
CREATE USER secapp
IDENTIFIED BY oracle
DEFAULT TABLESPACE olstbs
TEMPORARY TABLESPACE temp;

ALTER USER secapp QUOTA UNLIMITED ON olstbs;

-- application user with fewest privileges (one compartment, one group, one level)
CREATE USER lowprivs
IDENTIFIED BY oracle
TEMPORARY TABLESPACE temp;

-- application user with medium privileges (two compartments, two groups, two levels)
CREATE USER medprivs
IDENTIFIED BY oracle
TEMPORARY TABLESPACE temp;

-- application user with highest privileges (all compartments, all groups, all levels)
CREATE USER maxprivs
IDENTIFIED BY oracle
TEMPORARY TABLESPACE temp;
Privilege Grants GRANT alter user TO appdba;
GRANT audit any TO secapp;
GRANT create procedure TO secapp;
GRANT create session TO secapp;
GRANT create table TO secapp;
GRANT create view TO secapp;

-- no privileges have been granted to the three application users
-- this is intentional and part of a good security design
-- application users should not have explicitly granted privs
Create Application Objects and Data and grant object privileges to the OLS administrator conn secaapp/oracle@pdbdev

create table sources (
src_id      NUMBER(3),
src_type    VARCHAR2(1)  NOT NULL,  -- IM,IN,AG (imagery,intercept,agent
country     VARCHAR2(2)  NOT NULL,  -- CN,CA,CH,CR
tgt_type    VARCHAR2(12) NOT NULL,  -- Independent, Adversary, NATO, Five Eyes
doc_type    VARCHAR2(12) NOT NULL,  -- finance, military, leadership, education
verified    VARCHAR2(1)  NOT NULL,  -- N,P,C (No, Partial, Complete)
doc_passwd  VARCHAR2(20) NOT NULL);

ALTER TABLE sources
ADD CONSTRAINT pk_sources
PRIMARY KEY(src_id);

INSERT INTO sources VALUES(1,  'IM', 'CN', 'Independent', 'Finance',    'N', 'A');
INSERT INTO sources VALUES(2,  'IM', 'CZ', 'Adversary',   'Military',   'P', 'A');
INSERT INTO sources VALUES(3,  'IM', 'CH', 'NATO',        'Education',  'P', 'A');
INSERT INTO sources VALUES(4,  'IM', 'CR', 'Five Eyes',   'Leadership', 'N', 'A');

INSERT INTO sources VALUES(5,  'IN', 'CH', 'Independent', 'Finance',    'N', 'A');
INSERT INTO sources VALUES(6,  'IN', 'CN', 'Adversary',   'Leadership', 'N', 'A');
INSERT INTO sources VALUES(7,  'IN', 'GB', 'NATO',        'Leadership', 'P', 'A');
INSERT INTO sources VALUES(8,  'IN', 'NZ', 'Five Eyes',   'Education',  'C', 'A');

INSERT INTO sources VALUES(9,  'AG', 'CH', 'Independent', 'Finance',    'P', 'A');
INSERT INTO sources VALUES(10, 'AG', 'CN', 'Adversary',   'Military',   'N', 'A');
INSERT INTO sources VALUES(11, 'AG', 'GB', 'NATO',        'Education',  'C', 'A');
INSERT INTO sources VALUES(12, 'AG', 'NZ', 'Five Eyes',   'Leadership', 'P', 'A');

UPDATE sources SET doc_passwd = dbms_random.string('a', 20);
COMMIT;

GRANT read, update ON sources TO ols_admin;
Enable Connection Auditing AUDIT CONNECT BY lowprivs ON BEHALF OF secapp;
AUDIT CONNECT BY medprivs ON BEHALF OF secapp;
AUDIT CONNECT BY maxprivs ON BEHALF OF secapp;
 
Security Design
Design description
OLS Application Design XX

Levels: Defined by the OLS Administrator
Short Name Long Name Value
P Public 1
C Confidential 2
S Secure 9

Compartments:
Short Name Long Name Value
AD Adversary 100
IN Independent 200
NA NATO 300
FE Five Eyes 400
US Internal 900

Groups: Parents/Children
Short Name Description Value
CL Community Leaders 10
EL Financial Leaders 20
PL Political Leaders 30
MI Military-Intelligence Leaders 90

Compartment-Group Analysis
Compartment Group Level
AD CL P
AD EL C
AD PL S
AD MI S
IN CL P
IN EL P
IN PL P
IN MI C
NA CL P
NA EL C
NA PL C
NA MI S
FE CL P
FE EL C
FE PL S
FE MI S
US CL C
US EL S
US PL S
US MI S



XX

 
Initialize OLS
Text
Create Security Administrator and Grant System Privileges conn / as sysdba

-- label security administrator
CREATE USER ols_admin
IDENTIFIED BY oracle
TEMPORARY TABLESPACE temp;

GRANT create session to ols_admin;
GRANT create table to ols_admin;
GRANT create procedure to ols_admin;
GRANT execute ON lbacsys.lbac_policy_admin TO ols_admin;
GRANT execute ON lbacsys.sa_audit_admin TO ols_admin;
GRANT execute ON lbacsys.sa_components TO ols_admin;
GRANT execute ON lbacsys.sa_label_admin tO ols_admin;
GRANT execute ON lbacsys.sa_user_admin TO ols_admin;
GRANT execute ON sa_sysdba TO ols_admin;
GRANT execute ON to_lbac_data_label TO ols_admin;

GRANT lbac_dba TO ols_admin;
Create Classification Table conn ols_admin/oracle@pdbdev

CREATE TABLE classifier(
lower_boundary  NUMBER(4)   NOT NULL,
upper_boundary  NUMBER(4)   NOT NULL,
assigned_level  VARCHAR2(1) NOT NULL);

INSERT INTO classifier VALUES (0, ,    'P')
INSERT INTO classifier VALUES (1, 900, 'C');
INSERT INTO classifier VALUES (1, 900, 'E');
Unlock LBACSYS Schema

For a container database must be done in CDB$ROOT
conn / as sysdba

ALTER USER lbacsys IDENTIFIED BY pass ACCOUNT UNLOCK;

User altered.
Register OLS conn sys@pdbdev as sysdba

col description format a40
col status format a6

SELECT *
FROM dba_ols_status;

NAME                 STATUS DESCRIPTION
-------------------- ------ --------------------------------------
OLS_CONFIGURE_STATUS FALSE  Determines if OLS is configured
OLS_DIRECTORY_STATUS FALSE  Determines if OID is enabled with OLS
OLS_ENABLE_STATUS    FALSE  Determines if OLS is enabled

exec lbacsys.configure_ols;

PL/SQL procedure successfully completed.

SELECT *
FROM dba_ols_status;

NAME                 STATUS DESCRIPTION
-------------------- ------ --------------------------------------
OLS_CONFIGURE_STATUS TRUE  Determines if OLS is configured
OLS_DIRECTORY_STATUS FALSE  Determines if OID is enabled with OLS
OLS_ENABLE_STATUS    FALSE  Determines if OLS is enabled
Enable OLS conn sys@pdbdev as sysdba

col parameter format a30
col value format a6

SELECT *
FROM v$option
WHERE parameter LIKE '%Secur%';

PARAMETER                      VALUE  CON_ID
------------------------------ ------ ------
Enterprise User Security       TRUE        0
Oracle Label Security          FALSE       0
SecureFiles Encryption         TRUE        0
Real Application Security      TRUE        0

exec lbacsys.ols_enforcement.enable_ols;

PL/SQL procedure successfully completed.

SELECT *
FROM v$option
WHERE parameter LIKE '%Secur%';

PARAMETER                      VALUE  CON_ID
------------------------------ ------ ------
Enterprise User Security       TRUE        0
Oracle Label Security          TRUE        0
SecureFiles Encryption         TRUE        0
Real Application Security      TRUE        0
 
Create Policy
Text
Create DATA_ACCESS Policy conn ols_admin/oracle@pdbdev

exec sa_sysdba.create_policy(policy_name => 'DATA_ACCESS', column_name => 'da_label');

GRANT data_access_DBA TO ols_admin;

exec sa_sysdba.create_policy('FACILITY', 'FACLAB', 'Read_Control, Check_Control, Label_Default, Hide');
Create Policy Levels conn ols_admin/oracle@pdbdev

BEGIN
  sa_components.create_level('DATA_ACCESS', 1, 'P', 'PUBLIC');
  sa_components.create_level('DATA_ACCESS', 2, 'C', 'CONFIDENTIAL');
  sa_components.create_level('DATA_ACCESS', 3, 'S', 'SECURE');
END;
/
Create Policy Compartments conn ols_admin/oracle@pdbdev

BEGIN
  sa_components.create_compartment('DATA_ACCESS', 100, 'AO', 'PUBLIC');
  sa_components.create_compartment('DATA_ACCESS', 200, 'FA', 'SENSITIVE');
  sa_components.create_compartment('DATA_ACCESS', 300, 'IS', 'COMPARTMENTALIZED');
  sa_components.create_compartment('DATA_ACCESS', 800, 'MA', 'COMPARTMENTALIZED');
END;
/
Create Policy Groups conn ols_admin/oracle@pdbdev

BEGIN
  sa_components.create_group('DATA_ACCESS', 10, 'OP', 'OTHER PERSONS',      'GLOBAL');
  sa_components.create_group('DATA_ACCESS', 20, 'IA', 'IT ADMINS,           'GLOBAL'');
  sa_components.create_group('DATA_ACCESS', 30, 'SM', 'SENIOR MGMT',        'GLOBAL');
  sa_components.create_group('DATA_ACCESS', 40, 'EM', 'EXECUTIVE MGMT',     'GLOBAL');
  sa_components.create_group('DATA_ACCESS', 80, 'BD', 'BOARD OF DIRECTORS', 'GLOBAL');
END;
/
Create Policy Groups conn ols_admin/oracle@pdbdev

exec sa_user_admin.set_user_privs('DATA_ACCESS', USER, 'FULL,PROFILE_ACCESS');
Apply Policy To Table conn ols_admin/oracle@pdbdev

exec sa_policy_admin.apply_table_policy('DATA_ACCESS', 'SECAPP', 'AIRPLANES', 'NO_CONTROL');
Enable OLS Auditing conn ols_admin/oracle@pdbdev

exec sa_audit_admin.create_view('DATA_ACCESS', 'OLS_AUD$');

desc  ols_admin.ols_aud_dap$

SELECT * FROM ols_admin.ols_aud$_dap;

exec sa_audit_admin.audit('DATA_ACCESS');
exec sa_audit_admin.audit_label('DATA_ACCESS');
Create Labeling Function conn ols_admin/oracle@pdbdev

-- create function here but cannot apply until after policy is created
CREATE OR REPLACE FUNCTION gen_label(row_string IN VARCHAR2) RETURN VARCHAR2 IS
-- RETURN lbacsys.lbac_label AUTHID CURRENT_USER IS
 delim    VARCHAR2(2) := '::';
 labelVal VARCHAR2(80);
 levelVal VARCHAR2(1);
 targtVal VARCHAR2(12);
 groupVal VARCHAR2(1);
 pos1     PLS_INTEGER;
 pos2     PLS_INTEGER;
BEGIN
  pos1 := INSTR(row_string,',',1,1);
  pos2 := INSTR(row_string,',',1,2);

  SELECT DECODE(SUBSTR(row_string,1,pos1-1), 'P', 'P', 'C', 'C', 'S')
  INTO levelVal FROM dual;

  SELECT DECODE(SUBSTR(row_string,pos1+1,pos2-3), 'Independent', 'I', 'Adversary', 'A',
  'NATO', 'N', 'Five Eyes', 'FE', 'X')
  INTO targtVal FROM dual;

  SELECT DECODE(SUBSTR(row_string,pos2+1, LENGTH(row_string)), 'C', 'C', 'P', 'P', 'N')
  INTO groupVal FROM dual;

--  RETURN lbacsys.to_lbac_data_label('DATA_ACCESS', labelVal);
  RETURN levelVal || delim || targtVal || delim || groupVal;
END gen_label;
/

SELECT gen_label(src_type || ',' || tgt_type || ',' || verified) FROM secapp.sources;

CREATE OR REPLACE FUNCTION get_nxt_del
BEGIN
  RETURN 0;
END get_nxt_del;
/
Initialize Policy Label conn ols_admin/oracle@pdbdev

UPDATE secapp.airplanes SET
 
???
Text
??? SQL>
??? SQL>
??? SQL>
??? SQL>
Create OLS Facility Policy PL/SQL procedure successfully completed.

-- add FACILITY policy labels
BEGIN
  sa_label_admin.create_label('FACILITY', -1000, 'P');
  sa_label_admin.create_label('FACILITY', -2101, 'S::US');
  sa_label_admin.create_label('FACILITY', -3101, 'C::US');
  sa_label_admin.create_label('FACILITY', -2401, 'S::APAC');
  sa_label_admin.create_label('FACILITY', -3401, 'C::APAC');
END;
/
Create OLS Privacy Policy ??? (EM)
Set Authorizations conn lbacsys/??? @pdbdev

BEGIN
 sa_user_admin.set_user_labels('FACILITY', myco_emp', 'P');
 sa_user_admin.set_user_labels('FACILITY', myco_mgr', 'S::US, NATO, APAC');
 sa_user_admin.set_user_labels('FACILITY', myco_pln', 'C::GLOBAL');
 sa_user_admin.set_user_labels('PRIVACY', 'myco_mgr', 'C');
END;
/
Grant access to HR ??? (EM)
Clean-Up OLS Policies ???

SQL>

???
??? ???

SQL>

???
??? ???

SQL>

???
 
???
??? ???
???
OLS Application Design x

Facility Policies
Short Name Long Name Value
P PUBLIC 1000
S SENSITIVE 2000
C COMPARMENTALIZED 3000


x

Facility Policy Groups

Short Name Long Name Value
US United States 101
FE Five Eyes 201
NATO NATO 301
APAC Asia-Pacific Region 401
GLOBAL Global 1000


x

Facility Policy Active Data Labels

Short Name Description Value
P PUBLIC 1000
S::US Sensitive US 2101
S::FE Sensitive Five Eyes 2201
S::NATO Sensitive NATO 2301
S:APAC Sensitive Asia-Pacific Region 2401
S:GLOBAL Sensitive All Regions 2401
C::US Compartmented US 3101
C::FE Compartmented Five Eyes 3501


 x

Privacy Policies

Short Name Long Name Value
C Confidential 1000
S Secret 2000


x

Active Data Labels for Privacy

Label Description Tag
C ? 101000
S ? 102000



SQL>

???

 
 
   
 
 
 
   
 
   
 
 
 
   
 
 
 
   
 
 
Related Queries
Find dynamic SQL in unwrapped database source code.

In version 18.3 this query returns more than 1,500 rows
conn / as sysdba

SELECT owner, name, type, line, text, 'DBMS_SQL used' AS REASON
FROM dba_source
WHERE UPPER(text) LIKE '%DBMS_SQL%'
UNION ALL
SELECT owner, name, type, line, text, 'Native Dynamic used'
FROM dba_source
WHERE UPPER(text) LIKE '%EXECUTE IMMEDIATE%'
ORDER BY 1,2,3;

Related Topics
LBAC_EXP
LBAC_POLICY_ADMIN
LBAC_SESSION
LBAC_STANDARD
LBAC_SYSDBA
OLS$DATAPUMP
OLS_DIP_NTFY
OLS_ENFORCEMENT
OLS_UTIL_WRAPPER
SA_COMPONENTS
SA_LABEL_ADMIN
SA_SYSDBA
SA_USER_ADMIN
TO_LABEL_LIST