oracle-db12c-logo

Oracle Label Security


Using User Security Clearances with Data Redaction


Oracle Label Security user security clearance (label) can be used within Oracle Advanced Security Data Redaction policy condition to decide whether to redact data or not.
Start the Oracle Database and login as a user who has account management privileges:

  $ sqlplus ACCTS_ADMIN_ACE/password
 

Create two users for the HR application: HR_ADMIN_HARVEY and HR_SUPPORT_HENRY:

  SQL> GRANT CREATE SESSION TO HR_ADMIN_HARVEY identified by password;
  SQL> GRANT CREATE SESSION TO HR_SUPPORT_HENRY identified by password;

 

Using the sample schema HR, login and grant SELECT on the employees table to these users:

  SQL> connect HR/password
  SQL> GRANT SELECT ON EMPLOYEES TO HR_ADMIN_HARVEY, HR_SUPPORT_HENRY;
 

Login as LBACSYS user and create the Oracle Label Security policy:

  SQL> CONNECT LBACSYS/password
  SQL> BEGIN
SA_SYSDBA.CREATE_POLICY(
POLICY_NAME => 'OLS_SECURITY_CLEARANCES');

SA_COMPONENTS.CREATE_LEVEL(
POLICY_NAME => 'OLS_SECURITY_CLEARANCES'
,LEVEL_NUM => '1000'
,SHORT_NAME => 'PUBLIC'
,LONG_NAME => 'PUBLIC');

SA_COMPONENTS.CREATE_LEVEL(
POLICY_NAME => 'OLS_SECURITY_CLEARANCES'
,LEVEL_NUM => '2000'
,SHORT_NAME => 'CONFIDENTIAL'
,LONG_NAME => 'CONFIDENTIAL');

SA_COMPONENTS.CREATE_LEVEL(
POLICY_NAME => 'OLS_SECURITY_CLEARANCES'
,LEVEL_NUM => '3000'
,SHORT_NAME => 'SENSITIVE'
,LONG_NAME => 'SENSITIVE');
END;
/
 

Then assign security clearances to users:

  SQL> BEGIN
SA_USER_ADMIN.SET_USER_LABELS(
POLICY_NAME => 'OLS_SECURITY_CLEARANCES'
,USER_NAME => 'HR_ADMIN_HARVEY'
,MAX_READ_LABEL => 'SENSITIVE');

SA_USER_ADMIN.SET_USER_LABELS(
POLICY_NAME => 'OLS_SECURITY_CLEARANCES'
,USER_NAME => 'HR_SUPPORT_HENRY'
,MAX_READ_LABEL => 'CONFIDENTIAL');
END;
/  

User HR_ADMIN_HARVEY has been assigned a security clearance of "SENSITIVE".  This is higher than the HR_SUPPORT_HENRY security clearance which is "CONFIDENTIAL".

Let's create the Data Redaction policy that would redact data based on user's security clearance.  Login as a user with EXECUTE privilege on the DBMS_REDACT package:

  SQL> connect DBA_DEBRA/password
  SQL> BEGIN
DBMS_REDACT.ADD_POLICY(
OBJECT_SCHEMA => 'HR'
,OBJECT_NAME => 'EMPLOYEES'
,COLUMN_NAME => 'SALARY'
,POLICY_NAME => 'HR_REDACTION_POLICY'
,FUNCTION_TYPE => DBMS_REDACT.FULL
,EXPRESSION => 'DOMINATES(sa_utl.numeric_label(''OLS_SECURITY_CLEARANCES''), char_to_label(''OLS_SECURITY_CLEARANCES'',''SENSITIVE'')) !=1');
END;
/
Now, when the user HR_ADMIN_HARVEY logs in, he will be able to see salary information:
 
  SQL> CONNECT HR_ADMIN_HARVEY/password
  SQL> SELECT LBACSYS.SA_SESSION.LABEL('OLS_SECURITY_CLEARANCES') USER_SECURITY_CLEARANCE
FROM DUAL; USER_SECURITY_CLEARANCE
--------------------------
SENSITIVE
SQL> select last_name, salary from hr.employees; LAST_NAME SALARY
-------------------- ----------
Rajs 3500
Russell 14000
Zlotkey 10500
Olsen 8000
McEwen 9000
Vishney 10500
Ande 6400
Fox 9600
Abel 11000
Grant 7000

10 rows selected.
On the other hand, when the user HR_SUPPORT_HENRY logs in he will see only redacted salary information: 

  SQL> CONNECT HR_SUPPORT_HENRY/password
  SQL> SELECT LBACSYS.SA_SESSION.LABEL('OLS_SECURITY_CLEARANCES') USER_SECURITY_CLEARANCE
FROM DUAL;

 


USER_SECURITY_CLEARANCE
--------------------------
CONFIDENTIAL
SQL> select last_name, salary from hr.employees;

LAST_NAME SALARY
-------------------- ----------
Rajs 0
Russell 0
Zlotkey 0
Olsen 0
McEwen 0
Vishney 0
Ande 0
Fox 0
Abel 0
Grant 0

10 rows selected.  


Hands-On


 Using OLS user authorizations to create powerful Command Rules in Oracle Database Vault
 OLS user authorizations in VPD policies: Determine access to application table columns based on user authorizations
 Six steps towards a successful multi level security implementation

Security Features


Strong Authentication
Network Encryption
Real Application Security
Unified Auditing
Secure External Password Store
Virtual Private Database
Traditional Database Auditing
Proxy Authentication
Enterprise User Security
Secure Application Roles
Fine Grained Auditing

Discussion Forums


 Security
 Oracle Audit Vault and Database Firewall
 Database

Technical Information


 Datasheet
 Overview White Paper
 Government and Defense Environments White Paper
 Technical White Paper
 Best Practices
 Frequently Asked Questions
 Oracle Label Security with Oracle E-Business Suite: Best Practices

Security Solutions


 Oracle Database Vault
 Oracle Advanced Security
 Oracle Label Security
Oracle Audit Vault and Database Firewall
Data Masking

In-Memory Replay Banner