/*
|| Listing 1: OLS Administration.sql
||
|| Supplies sample queries and examples of how to administer 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 lbacsys/lbacsys;
 
-----
-- Listing 1.1: Show all OLS attributes for users using SADM OLS policy
-----
COL user_name           FORMAT A12  HEADING 'User'
COL max_read_label      FORMAT A12  HEADING 'Max|Read|Label'
COL min_write_label     FORMAT A12  HEADING 'Min|Write|Label'
COL max_write_label     FORMAT A12  HEADING 'Max|Write|Label'
COL default_read_label  FORMAT A12  HEADING 'Default|Read|Label'
COL default_write_label FORMAT A12  HEADING 'Default|Write|Label'
SELECT 
    user_name
   ,max_read_label
   ,min_write_label 
   ,max_write_label 
   ,default_read_label
   ,default_write_label 
  FROM dba_sa_users
 WHERE policy_name = 'SADM';

 
-----
-- Listing 1.2: Show all OLS User Security Levels for SADM OLS policy
-----
COL user_name  FORMAT A12  HEADING 'User'
COL max_level  FORMAT A12  HEADING 'Max|Level'
COL min_level  FORMAT A12  HEADING 'Min|Level'
COL def_level  FORMAT A12  HEADING 'Default|Level'
COL row_level  FORMAT A12  HEADING 'Row|Level'
SELECT 
    user_name
   ,max_level
   ,min_level 
   ,def_level 
   ,row_level
  FROM DBA_SA_USER_LEVELS
 WHERE policy_name = 'SADM';

 
-----
-- Listing 1.3: Show all OLS User Compartments for SADM OLS policy
-----
COL user_name  FORMAT A12  HEADING 'User'
COL comp       FORMAT A12  HEADING 'Compartment'
COL rw_access  FORMAT A12  HEADING 'R/W|Access'
COL def_comp   FORMAT A12  HEADING 'Default|Compartment'
COL row_comp   FORMAT A12  HEADING 'Row|Compartment'
SELECT 
    user_name
   ,comp
   ,rw_access
   ,def_comp
   ,row_comp 
  FROM DBA_SA_USER_COMPARTMENTS
 WHERE policy_name = 'SADM';

 
-----
-- Listing 1.4: Show all OLS User Groups for SADM OLS policy
-----
COL user_name  FORMAT A12  HEADING 'User'
COL grp        FORMAT A12  HEADING 'Group'
COL rw_access  FORMAT A12  HEADING 'R/W|Access'
COL def_group  FORMAT A12  HEADING 'Default|Group'
COL row_group  FORMAT A12  HEADING 'Row|Group'
SELECT
    user_name
   ,grp
   ,rw_access
   ,def_group
   ,row_group 
  FROM DBA_SA_USER_GROUPS
 WHERE policy_name = 'SADM';

 
-----
-- Listing 1.5: Add new European group
-----

-- Add new security group for Europe
CONNECT lbacsys/lbacsys;
BEGIN
   SA_COMPONENTS.CREATE_GROUP(
       policy_name => 'SADM' 
      ,group_num =>   60
      ,short_name => 'EU'
      ,long_name => 'European Sales Region'
      ,parent_name => 'T'
   );
END;
/

-- Grant EXECUTE permissions to SALESADM and RGNMGR1 (experimentation only!)
CONNECT lbacsys/lbacsys;
GRANT execute ON to_data_label to SALESADM;
GRANT execute ON to_data_label to RGNMGR1;

 
-----
-- Listing 1.6: Using CHAR_TO_LABEL
-----
CONNECT salesadm/password;

DELETE FROM salesadm.sales_regions WHERE region_id = 6;
INSERT INTO salesadm.sales_regions (region_id, abbr, description, sadm_lbl)
VALUES(6, 'EUR', 'European', CHAR_TO_LABEL('SADM', 'CW:SA:EU')); 
COMMIT;

 
-----
-- Listing 1.7: Using numeric tag value
-----
DELETE FROM salesadm.sales_regions WHERE region_id = 6;
INSERT INTO salesadm.sales_regions (region_id, abbr, description, sadm_lbl)
VALUES(6, 'EUR', 'European', 30160); 
COMMIT;

 
-----
-- Listing 1.8: Using TO_DATA_LABEL (doesn't require valid data label - creates its own)
-----
DELETE FROM salesadm.sales_regions WHERE region_id = 6;
INSERT INTO salesadm.sales_regions (region_id, abbr, description, sadm_lbl)
VALUES(6, 'EUR', 'European', TO_DATA_LABEL('sadm', 'CW:SA:EU')); 
COMMIT;

 
-----
-- Listing 1.9: Changing a user's session label and row label temporarily
-----
BEGIN
   SA_SESSION.SET_LABEL('SADM', 'CW:SA:EU');
   SA_SESSION.SET_ROW_LABEL('SADM', 'CW:SA:EU');
END;
/

COL sa_user_name FORMAT A12 HEADING 'User Name'
COL label        FORMAT A20 HEADING 'Default Label'
COL row_label    FORMAT A12 HEADING 'Row Label'
SELECT
    sa_user_name
   ,label
   ,row_label
  FROM user_sa_session
;

DELETE FROM salesadm.sales_regions WHERE region_id = 6;
INSERT INTO salesadm.sales_regions (region_id, abbr, description)
VALUES(6, 'EUR', 'European');
COMMIT;

 
-----
-- Listing 1.10: Reset user session label and row label
-----
BEGIN
   SA_SESSION.RESTORE_DEFAULT_LABELS('SADM'); -- Restores defaults
   -- SA_SESSION.SAVE_DEFAULT_LABELS('SADM'); -- Saves new values as defaults
END;
/

COL sa_user_name FORMAT A12 HEADING 'User Name'
COL label        FORMAT A20 HEADING 'Default Label'
COL row_label    FORMAT A12 HEADING 'Row Label'
SELECT
    sa_user_name
   ,label
   ,row_label
  FROM user_sa_session
;