| 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 |
| |
|
|
| PUBL |
Public |
1 |
| CONF |
Confidential |
2 |
| COAT |
Confidential-Attorneys |
3 |
| EYES |
Eyes Only |
4 |
| COCO |
Confidential Outside
Counsel Only |
5 |
Compartments:
| Short Name |
Long Name |
Value |
| AD |
Adversary |
100 |
| IN |
Independent |
200 |
| NA |
NATO |
300 |
| FE |
Five Eyes |
400 |
| US |
Internal |
900 |
| |
|
|
| DOC |
Document |
100 |
| DAT |
Data |
200 |
| TES |
Testimony |
300 |
| IAN |
Interrogatory Answer |
400 |
| RES |
Response to a Request to
Admit |
500 |
| EXH |
Exhibit |
600 |
| OIP |
Other Information
Produced or Disclosed |
700 |
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; |