/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 17 : ORACLE LABEL SECURITY (OLS)
Topics Covered
---------------
1 Oracle Label Security concept
2 Mandatory Access Control (MAC)
3 Security labels
4 Creating label security policy
5 Defining label levels
6 Assigning labels to rows
7 Assigning labels to users
8 Viewing label security metadata
9 Removing label security policy
10 Enterprise classification model
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Oracle Label Security?
*******************************************************************************************/
-- Oracle Label Security (OLS) implements
-- Mandatory Access Control (MAC).
-- Data rows receive security labels,
-- and users can access rows only within
-- their clearance level.
/*******************************************************************************************
QUESTION 2
How does Label Security differ from normal privileges?
*******************************************************************************************/
-- Traditional security controls object access.
-- Label Security controls row access
-- based on classification levels.
/*******************************************************************************************
QUESTION 3
Example classification hierarchy
*******************************************************************************************/
-- PUBLIC
-- CONFIDENTIAL
-- SECRET
-- TOP_SECRET
/*******************************************************************************************
QUESTION 4
Which package manages Label Security?
*******************************************************************************************/
-- Oracle Label Security uses the package
-- SA_SYSDBA
/*******************************************************************************************
QUESTION 5
Create label security policy
*******************************************************************************************/
BEGIN
SA_SYSDBA.CREATE_POLICY
(
policy_name => 'HR_SECURITY_POLICY',
column_name => 'SECURITY_LABEL'
);
END;
/
/*******************************************************************************************
QUESTION 6
What does the policy define?
*******************************************************************************************/
-- The policy defines a label column
-- that stores classification levels.
/*******************************************************************************************
QUESTION 7
Create security levels
*******************************************************************************************/
BEGIN
SA_COMPONENTS.CREATE_LEVEL
(
policy_name => 'HR_SECURITY_POLICY',
level_num => 10,
short_name => 'PUBLIC',
long_name => 'Public Data'
);
END;
/
/*******************************************************************************************
QUESTION 8
Create CONFIDENTIAL level
*******************************************************************************************/
BEGIN
SA_COMPONENTS.CREATE_LEVEL
(
policy_name => 'HR_SECURITY_POLICY',
level_num => 20,
short_name => 'CONFIDENTIAL',
long_name => 'Confidential Data'
);
END;
/
/*******************************************************************************************
QUESTION 9
Create SECRET level
*******************************************************************************************/
BEGIN
SA_COMPONENTS.CREATE_LEVEL
(
policy_name => 'HR_SECURITY_POLICY',
level_num => 30,
short_name => 'SECRET',
long_name => 'Secret Data'
);
END;
/
/*******************************************************************************************
QUESTION 10
Create TOP_SECRET level
*******************************************************************************************/
BEGIN
SA_COMPONENTS.CREATE_LEVEL
(
policy_name => 'HR_SECURITY_POLICY',
level_num => 40,
short_name => 'TOP_SECRET',
long_name => 'Top Secret Data'
);
END;
/
/*******************************************************************************************
QUESTION 11
Apply policy to HR employees table
*******************************************************************************************/
BEGIN
SA_POLICY_ADMIN.APPLY_TABLE_POLICY
(
policy_name => 'HR_SECURITY_POLICY',
schema_name => 'HR',
table_name => 'EMPLOYEES'
);
END;
/
/*******************************************************************************************
QUESTION 12
Assign label to row
*******************************************************************************************/
UPDATE hr.employees
SET security_label =
SA_LABEL_ADMIN.LABEL_TO_CHAR('HR_SECURITY_POLICY','CONFIDENTIAL')
WHERE employee_id = 100;
/*******************************************************************************************
QUESTION 13
Assign label to user
*******************************************************************************************/
BEGIN
SA_USER_ADMIN.SET_USER_LABELS
(
policy_name => 'HR_SECURITY_POLICY',
user_name => 'TRAINING_USER',
max_read_label => 'CONFIDENTIAL'
);
END;
/
/*******************************************************************************************
QUESTION 14
What happens when user queries data?
*******************************************************************************************/
-- User sees only rows with labels
-- equal to or below their clearance level.
/*******************************************************************************************
QUESTION 15
Example query
*******************************************************************************************/
SELECT employee_id,
first_name
FROM hr.employees;
/*******************************************************************************************
QUESTION 16
Expected behavior
*******************************************************************************************/
-- Rows labeled SECRET or TOP_SECRET
-- are hidden from the user.
/*******************************************************************************************
QUESTION 17
View label security policies
*******************************************************************************************/
SELECT *
FROM DBA_SA_POLICIES;
/*******************************************************************************************
QUESTION 18
View label levels
*******************************************************************************************/
SELECT *
FROM DBA_SA_LEVELS;
/*******************************************************************************************
QUESTION 19
View user label assignments
*******************************************************************************************/
SELECT *
FROM DBA_SA_USER_LABELS;
/*******************************************************************************************
QUESTION 20
Remove label policy
*******************************************************************************************/
BEGIN
SA_SYSDBA.DROP_POLICY
(
policy_name => 'HR_SECURITY_POLICY'
);
END;
/
/*******************************************************************************************
QUESTION 21
Advantages of Label Security
*******************************************************************************************/
-- Enforces mandatory access control
-- Protects classified information
-- Prevents unauthorized data exposure
/*******************************************************************************************
QUESTION 22
Government security example
*******************************************************************************************/
-- Military data classified as
-- CONFIDENTIAL
-- SECRET
-- TOP_SECRET
/*******************************************************************************************
QUESTION 23
Financial institution example
*******************************************************************************************/
-- Sensitive financial records
-- accessible only to authorized analysts.
/*******************************************************************************************
QUESTION 24
Healthcare example
*******************************************************************************************/
-- Patient records classified
-- by sensitivity levels.
/*******************************************************************************************
QUESTION 25
Label security architecture
*******************************************************************************************/
-- USER
-- |
-- v
-- SECURITY LABEL CHECK
-- |
-- v
-- DATABASE ROW ACCESS
/*******************************************************************************************
QUESTION 26
Difference between VPD and Label Security
*******************************************************************************************/
-- VPD filters rows dynamically.
-- Label Security enforces
-- classification-based access.
/*******************************************************************************************
QUESTION 27
Compliance advantages
*******************************************************************************************/
-- Supports regulatory compliance
-- Improves data protection
/*******************************************************************************************
QUESTION 28
Security enforcement
*******************************************************************************************/
-- Users cannot bypass label restrictions
-- even with direct SQL queries.
/*******************************************************************************************
QUESTION 29
Enterprise usage
*******************************************************************************************/
-- Government
-- Defense
-- Intelligence agencies
/*******************************************************************************************
QUESTION 30
Oracle Label Security summary
*******************************************************************************************/
-- Implements mandatory access control.
-- Uses classification labels.
-- Restricts access based on clearance level.
/*******************************************************************************************
END OF PART 17
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied warranties, including but not limited to warranties of accuracy, completeness, reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees that they do so at their own risk. The author shall not be held liable for any direct, indirect, incidental, consequential, special, or exemplary damages, including but not limited to loss of data, loss of profits, system failures, security breaches, or any other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and configurations in a controlled, non-production (test) environment prior to deploying them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author from any claims, damages, liabilities, or expenses arising from their use.
*******************************************************************************************/