/*
|| Listing 2: OLS Policy Setup.sql
||
|| Sets up security policy and all dependent information for
|| demonstration of Oracle Label Security (OLS) features 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 to Label Adminstrator account
CONNECT LBACSYS/LBACSYS;

-----
-- Listing 2.1: Create OLS security policies
-----
BEGIN
   SA_SYSDBA.CREATE_POLICY(
       policy_name => 'SADM'
      ,column_name => 'SADM_LBL'
      ,default_options => 'ALL_CONTROL,HIDE'
   );
END;
/

-----
-- Listing 2.2: Create security Levels
-----
BEGIN
   SA_COMPONENTS.CREATE_LEVEL(
       policy_name => 'SADM' 
      ,level_num =>   1000
      ,short_name => 'UN'
      ,long_name => 'Unsecured data'
   );
   SA_COMPONENTS.CREATE_LEVEL(
       policy_name => 'SADM' 
      ,level_num => 3000
      ,short_name => 'CW'
      ,long_name => 'Company Wide Information'
   );
   SA_COMPONENTS.CREATE_LEVEL(
       policy_name => 'SADM' 
      ,level_num => 5000
      ,short_name => 'CC'
      ,long_name => 'Company Confidential'
   );
   SA_COMPONENTS.CREATE_LEVEL(
       policy_name => 'SADM' 
      ,level_num => 7000
      ,short_name => 'TS'
      ,long_name => 'Trade Secret'
   );
END;
/

-----
-- Listing 2.3: Create security Compartments
-----
BEGIN
   SA_COMPONENTS.CREATE_COMPARTMENT(
       policy_name => 'SADM' 
      ,comp_num => 100
      ,short_name => 'AC'
      ,long_name => 'Accounting'
   );
   SA_COMPONENTS.CREATE_COMPARTMENT(
       policy_name => 'SADM' 
      ,comp_num => 200
      ,short_name => 'SA'
      ,long_name => 'Sales Administration'
   );
   SA_COMPONENTS.CREATE_COMPARTMENT(
       policy_name => 'SADM' 
      ,comp_num => 300
      ,short_name => 'HR'
      ,long_name => 'Human Resources'
   );
   SA_COMPONENTS.CREATE_COMPARTMENT(
       policy_name => 'SADM' 
      ,comp_num => 400
      ,short_name => 'OP'
      ,long_name => 'Operations'
   );
   SA_COMPONENTS.CREATE_COMPARTMENT(
       policy_name => 'SADM' 
      ,comp_num => 500
      ,short_name => 'OE'
      ,long_name => 'Order Entry'
   );
END;
/

-----
-- Listing 2.4: Create security Groups
-----
BEGIN
   SA_COMPONENTS.CREATE_GROUP(
       policy_name => 'SADM' 
      ,group_num =>   0
      ,short_name => 'T'
      ,long_name => 'Top of Sales Force Hierarchy'
      ,parent_name => NULL
   );
   SA_COMPONENTS.CREATE_GROUP(
       policy_name => 'SADM' 
      ,group_num =>   10
      ,short_name => 'NE'
      ,long_name => 'Northeastern Sales Region'
      ,parent_name => 'T'
   );
   SA_COMPONENTS.CREATE_GROUP(
       policy_name => 'SADM' 
      ,group_num =>   20
      ,short_name => 'SE'
      ,long_name => 'Southeastern Sales Region'
      ,parent_name => 'T'
   );
   SA_COMPONENTS.CREATE_GROUP(
       policy_name => 'SADM' 
      ,group_num =>   30
      ,short_name => 'CN'
      ,long_name => 'Central Sales Region'
      ,parent_name => 'T'
   );
   SA_COMPONENTS.CREATE_GROUP(
       policy_name => 'SADM' 
      ,group_num =>   40
      ,short_name => 'SW'
      ,long_name => 'Southwestern Sales Region'
      ,parent_name => 'T'
   );
   SA_COMPONENTS.CREATE_GROUP(
       policy_name => 'SADM' 
      ,group_num =>   50
      ,short_name => 'NW'
      ,long_name => 'Northwestern Sales Region'
      ,parent_name => 'T'
   );
END;
/

-----
-- Listing 2.5: Create data labels
-----
BEGIN
   -- Create labels for security Levels ONLY
   SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   10000
      ,label_value => 'UN'
      ,data_label => TRUE
   );
   SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   30000
      ,label_value => 'CW'
      ,data_label => TRUE
   );
   SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   50000
      ,label_value => 'CC'
      ,data_label => TRUE
   );
   SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   70000
      ,label_value => 'TS'
      ,data_label => TRUE
   );
   -- Create labels for security Levels within Compartments
   SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   10100
      ,label_value => 'UN:AC'
      ,data_label => TRUE
   );
      SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   10200
      ,label_value => 'UN:SA'
      ,data_label => TRUE
   );
      SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   10300
      ,label_value => 'UN:HR'
      ,data_label => TRUE
   );
      SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   10400
      ,label_value => 'UN:OP'
      ,data_label => TRUE
   );
      SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   10500
      ,label_value => 'UN:OE'
      ,data_label => TRUE
   );
   -- Create labels for Sales Force hierarchy (security Level plus Groups)
   SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   30100
      ,label_value => 'CW:SA:T'
      ,data_label => TRUE
   );
   SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   30110
      ,label_value => 'CW:SA:NE'
      ,data_label => TRUE
   );
   SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   30120
      ,label_value => 'CW:SA:SE'
      ,data_label => TRUE
   );
   SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   30130
      ,label_value => 'CW:SA:CN'
      ,data_label => TRUE
   );
   SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   30140
      ,label_value => 'CW:SA:SW'
      ,data_label => TRUE
   );
   SA_LABEL_ADMIN.CREATE_LABEL(
       policy_name => 'SADM' 
      ,label_tag =>   30150
      ,label_value => 'CW:SA:NW'
      ,data_label => TRUE
   );
END;
/
-----
-- Listing 2.6: Apply security policy labels to Users
-----
BEGIN
   SA_USER_ADMIN.SET_USER_LABELS(
       policy_name => 'SADM' 
      ,user_name =>   'SALESADM'
      ,max_read_label => 'CW:SA:T'
   );
   SA_USER_ADMIN.SET_USER_LABELS(
       policy_name => 'SADM' 
      ,user_name =>   'SLSMGR'
      ,max_read_label => 'CW:SA:T'
   );
   SA_USER_ADMIN.SET_USER_LABELS(
       policy_name => 'SADM' 
      ,user_name =>   'RGNMGR1'
      ,max_read_label => 'CW:SA:NE'
   );
   SA_USER_ADMIN.SET_USER_LABELS(
       policy_name => 'SADM' 
      ,user_name =>   'RGNMGR2'
      ,max_read_label => 'CW:SA:SE'
   );
   SA_USER_ADMIN.SET_USER_LABELS(
       policy_name => 'SADM' 
      ,user_name =>   'RGNMGR3'
      ,max_read_label => 'CW:SA:CN'
   );
   SA_USER_ADMIN.SET_USER_LABELS(
       policy_name => 'SADM' 
      ,user_name =>   'RGNMGR4'
      ,max_read_label => 'CW:SA:SW'
   );
   SA_USER_ADMIN.SET_USER_LABELS(
       policy_name => 'SADM' 
      ,user_name =>   'RGNMGR5'
      ,max_read_label => 'CW:SA:NW'
   );
END;
/

-----
-- Listing 2.7: Apply Sales Administration security policy to appropriate
--              tables within the SALESADM schema
-----
BEGIN
   SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
       policy_name => 'SADM' 
      ,schema_name => 'SALESADM' 
      ,table_name => 'sales_regions'
      ,table_options => NULL
      ,label_function => NULL
      ,predicate => NULL
   );
   SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
       policy_name => 'SADM' 
      ,schema_name => 'SALESADM' 
      ,table_name => 'sales_districts'
      ,table_options => NULL
      ,label_function => NULL
      ,predicate => NULL
   );
   SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
       policy_name => 'SADM' 
      ,schema_name => 'SALESADM' 
      ,table_name => 'sales_zones'
      ,table_options => NULL
      ,label_function => NULL
      ,predicate => NULL
   );
END;
/

-----
-- Listing 2.8: Authorize privileges for the SALESADM schema to maintain 
--              data contained within all its owned objects
-----
BEGIN
    SA_USER_ADMIN.SET_USER_PRIVS(
       policy_name => 'SADM' 
      ,user_name => 'SALESADM' 
      ,PRIVILEGES => 'FULL,PROFILE_ACCESS'
   );
END;
/

-----
-- Listing 2.9: Apply appropriate security labels to data stored within 
--              tables for which the SADM security policy has been applied
-----
BEGIN
   -- Apply changes to SALES_REGIONS
   UPDATE salesadm.sales_regions
      SET sadm_lbl = CHAR_TO_LABEL('SADM', 'CW:SA:CN')
    WHERE abbr = 'CN00';
   UPDATE salesadm.sales_regions
      SET sadm_lbl = CHAR_TO_LABEL('SADM', 'CW:SA:NE')
    WHERE abbr = 'NE00';
   UPDATE salesadm.sales_regions
      SET sadm_lbl = CHAR_TO_LABEL('SADM', 'CW:SA:NW')
    WHERE abbr = 'NW00';
   UPDATE salesadm.sales_regions
      SET sadm_lbl = CHAR_TO_LABEL('SADM', 'CW:SA:SE')
    WHERE abbr = 'SE00';
   UPDATE salesadm.sales_regions
      SET sadm_lbl = CHAR_TO_LABEL('SADM', 'CW:SA:SW')
    WHERE abbr = 'SW00';
   COMMIT;    
END;
/