/*
 || Listing 1: OLS Advanced Features.sql
||
|| Supplies sample queries and examples of some of the more advanced features
|| of Oracle Label Security (OLS) for a sample Sales Administration application.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of 
|| Oracle Label Security (OLS) and should be carefully proofread
|| before executing it against any existing Oracle database to insure
|| that no potential damage can occur.
||
*/

CONNECT lbacsys/lbacsys;
 
-----
-- Listing 1.1: Create an OLS Label Function
-----
CREATE OR REPLACE FUNCTION salesadm.sf_sadm_security (a_region_id INTEGER)
   RETURN LBACSYS.LBAC_LABEL
/*
|| Function Specification: salesadm.pkg_sadm_security
||
|| Description: Example of Oracle Label Security (OLS) label function
||
|| Author: Jim Czuprynski, Zero Defect Computing, Inc.
|| Date: 11-03-2003
*/
IS
   l_vcLabel VARCHAR2(80);

BEGIN
   CASE a_region_id
      WHEN 1 THEN
         l_vcLabel := 'CW:SA:NE';
      WHEN 2 THEN
         l_vcLabel := 'CW:SA:SE';
      WHEN 3 THEN
         l_vcLabel := 'CW:SA:CN';
      WHEN 4 THEN
         l_vcLabel := 'CW:SA:NW';
      WHEN 5 THEN
         l_vcLabel := 'CW:SA:SW';
      WHEN 6 THEN
         l_vcLabel := 'CW:SA:EU';
      ELSE
         l_vcLabel := 'CW:SA:NE';
   END CASE;

   RETURN TO_LBAC_DATA_LABEL('sadm', l_vcLabel);

   EXCEPTION
      WHEN OTHERS THEN
         RETURN NULL;

END sf_sadm_security;
 
/-----
-- Listing 1.2: Update existing SALES_DISTRICTS rows
-----
BEGIN
   UPDATE salesadm.sales_districts
      SET sadm_lbl = CHAR_TO_LABEL('SADM', 'CW:SA:CN')
    WHERE abbr LIKE 'CN%';
   UPDATE salesadm.sales_districts
      SET sadm_lbl = CHAR_TO_LABEL('SADM', 'CW:SA:NE')
    WHERE abbr LIKE 'NE%';
   UPDATE salesadm.sales_districts
      SET sadm_lbl = CHAR_TO_LABEL('SADM', 'CW:SA:NW')
    WHERE abbr LIKE 'NW%';
   UPDATE salesadm.sales_districts
      SET sadm_lbl = CHAR_TO_LABEL('SADM', 'CW:SA:SE')
    WHERE abbr LIKE 'SE%';
   UPDATE salesadm.sales_districts
      SET sadm_lbl = CHAR_TO_LABEL('SADM', 'CW:SA:SW')
    WHERE abbr LIKE 'SW%';
   COMMIT;    
END;
/


-----
-- Listing 1.3: Apply the a label function as an OLS policy for a table
-----
BEGIN
   -- Remove the existing policy (if any)
   SA_POLICY_ADMIN.REMOVE_TABLE_POLICY(
       policy_name => 'SADM' 
      ,schema_name => 'SALESADM' 
      ,table_name => 'sales_districts'
    );	  
   -- Apply the new policy for the table
   SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
       policy_name => 'SADM' 
      ,schema_name => 'SALESADM' 
      ,table_name => 'sales_districts'
      ,label_function => 'salesadm.sf_sadm_security(:new.region_id)'
      ,predicate => NULL
   );
END;
/

 
-----
-- Listing 1.4: Enforcing a security policy at a lower level of granularity
-----
BEGIN
   -- Remove the existing policy (if any exists)
   SA_POLICY_ADMIN.REMOVE_TABLE_POLICY(
       policy_name => 'SADM'
      ,schema_name => 'SALESADM'
      ,table_name => 'sales_zones'
    );
   -- Apply the new policy for the table
   SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
       policy_name => 'SADM'
      ,schema_name => 'SALESADM'
      ,table_name => 'sales_zones'
      ,table_options => 'READ_CONTROL, WRITE_CONTROL, CHECK_CONTROL'
      ,label_function => NULL
      ,predicate => NULL
   );
END;
/

-- Show current security policy settings for all tables
COL schema_name         FORMAT A10  HEADING 'Schema'
COL table_name          FORMAT A20  HEADING 'Table Name'
COL status              FORMAT A10  HEADING 'Status'
COL table_options       FORMAT A32  HEADING 'Table Options'
COL function            FORMAT A32  HEADING 'Label Function Name'
SELECT 
    schema_name
   ,table_name    
   ,status 
   ,table_options
   ,function
  FROM dba_sa_table_policies
 WHERE policy_name = 'SADM';

 
-----
-- Listing 1.5: Activate OLS auditing for SADM policy for:
-- All users
-- All options 
-- By session 
-- Regardless of success or failure
-----
BEGIN
   SA_AUDIT_ADMIN.AUDIT(
       policy_name => 'SADM'
      ,users => NULL
      ,audit_option => 'APPLY, REMOVE, SET, PRIVILEGES'
      ,audit_type => 'BY SESSION'
      ,success => NULL
    );
END;
/

 
-----
-- Listing 1.6: Activate OLS auditing for SADM policy for:
-- Specific users
-- All options 
-- By session 
-- Regardless of success or failure
-----
BEGIN
   SA_AUDIT_ADMIN.AUDIT(
       policy_name => 'SADM'
      ,users => 'SALESADM,LBACSYS'
      ,audit_option => 'APPLY,REMOVE,SET,PRIVILEGES'
      ,audit_type => 'BY SESSION'
      ,success => NULL
    );
END;
/

 
-----
-- Listing 1.7: Activate storage of policy labels during auditing
-----
BEGIN
   SA_AUDIT_ADMIN.AUDIT_LABEL(
       policy_name => 'SADM'
    );
END;
/

 
-----
-- Listing 1.8: Create a DBA-level view for the security policy being audited
-----
BEGIN
   SA_AUDIT_ADMIN.CREATE_VIEW(
       policy_name => 'SADM'
      ,view_name => 'dba_sadm_auditing' 
    );
END;
/

 
-----
-- Listing 1.9: Query current OLS auditing options in effect
-----
COL policy_name   FORMAT A10  HEADING 'Policy|Name'
COL user_name     FORMAT A10  HEADING 'User|name'
COL apy           FORMAT A03  HEADING 'APY'
COL rem           FORMAT A03  HEADING 'REM'
COL set_          FORMAT A03  HEADING 'SET'
COL prv           FORMAT A03  HEADING 'PRV'
SELECT 
    policy_name
   ,user_name
   ,apy
   ,rem
   ,set_
   ,prv
  FROM dba_sa_audit_options
 WHERE policy_name = 'SADM';

 
-----
-- Listing 1.10: 
-- Deactivate OLS auditing for SADM policy for all users
-- and options specified previously
-- Deactivate auditing for all labels
-- Drop the auditing view
-----
BEGIN
   SA_AUDIT_ADMIN.NOAUDIT(
       policy_name => 'SADM'
      ,users => NULL
      ,audit_option => NULL
    );
   SA_AUDIT_ADMIN.NOAUDIT(
       policy_name => 'SADM'
      ,users => 'SALESADM,LBACSYS'
      ,audit_option => NULL
    );
   SA_AUDIT_ADMIN.NOAUDIT_LABEL(
       policy_name => 'SADM'
    );
END;
/