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