/*
|| Listing 3: OLS Policy Removal.sql
||
|| Supplies sample scripts for removal of an Oracle Label Security (OLS) 
|| security policy.
||
|| 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;

-----
-- Remove Policies applied to Tables
-----
BEGIN
   SA_POLICY_ADMIN.REMOVE_TABLE_POLICY(
       policy_name =>'SADM'
      ,schema_name => 'salesadm'
      ,table_name => 'sales_regions'
      ,drop_column => TRUE
   );
   SA_POLICY_ADMIN.REMOVE_TABLE_POLICY(
       policy_name =>'SADM' 
      ,schema_name => 'salesadm'
      ,table_name => 'sales_districts'
      ,drop_column => TRUE
   );
END;
/

-----
-- Remove Policies applied to Schemas
-----
BEGIN
   SA_POLICY_ADMIN.REMOVE_SCHEMA_POLICY(
       policy_name =>'SADM'
      ,schema_name => 'salesadm', 
   );
END;
/

-----
-- Remove Labels assigned to Users
-----
/*
SELECT 
    'SA_USER_ADMIN.DROP_LABELS(policy_name =>''' || policy_name || ''', user_name => ''' || user_name || ''');'
  FROM dba_sa_user_levels;
*/
BEGIN
   SA_USER_ADMIN.DROP_LABELS(policy_name =>'SADM', user_name => 'RGNMGR1');
   SA_USER_ADMIN.DROP_LABELS(policy_name =>'SADM', user_name => 'RGNMGR2');
   SA_USER_ADMIN.DROP_LABELS(policy_name =>'SADM', user_name => 'RGNMGR3');
   SA_USER_ADMIN.DROP_LABELS(policy_name =>'SADM', user_name => 'RGNMGR4');
   SA_USER_ADMIN.DROP_LABELS(policy_name =>'SADM', user_name => 'RGNMGR5');
   SA_USER_ADMIN.DROP_LABELS(policy_name =>'SADM', user_name => 'SALESADM');
   SA_USER_ADMIN.DROP_LABELS(policy_name =>'SADM', user_name => 'SLSMGR');
END;
/

-----
-- Remove security Labels
-----
/*
SELECT 
    'SA_LABEL_ADMIN.DROP_LABEL(policy_name =>''' || policy_name || ''', label_tag => ' || label_tag || ');'
  FROM dba_sa_labels;
*/
BEGIN
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 10000);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 30000);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 50000);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 70000);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 10100);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 10200);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 10300);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 10400);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 10500);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 30100);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 30110);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 30120);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 30130);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 30140);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 30150);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 1000000029);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 1000000209);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 1000000191);
   SA_LABEL_ADMIN.DROP_LABEL(policy_name =>'SADM', label_tag => 1000000194);
END;
/

-----
-- Remove security Levels
-----
/*
SELECT 
    'SA_COMPONENTS.DROP_LEVEL(policy_name =>''' || policy_name || ''', level_num => ' || level_num || ');'
  FROM dba_sa_levels
 ORDER BY level_num;
*/
BEGIN
   SA_COMPONENTS.DROP_LEVEL(policy_name =>'SADM', level_num => 1000);
   SA_COMPONENTS.DROP_LEVEL(policy_name =>'SADM', level_num => 3000);
   SA_COMPONENTS.DROP_LEVEL(policy_name =>'SADM', level_num => 5000);
   SA_COMPONENTS.DROP_LEVEL(policy_name =>'SADM', level_num => 7000);
END;
/

-----
-- Drop security Compartments
-----
/*
SELECT 
    'SA_COMPONENTS.DROP_COMPARTMENT(policy_name =>''' || policy_name || ''', comp_num => ' || comp_num || ');'
  FROM dba_sa_compartments
 ORDER BY comp_num;
*/
BEGIN
   SA_COMPONENTS.DROP_COMPARTMENT(policy_name =>'SADM', comp_num => 100);
   SA_COMPONENTS.DROP_COMPARTMENT(policy_name =>'SADM', comp_num => 200);
   SA_COMPONENTS.DROP_COMPARTMENT(policy_name =>'SADM', comp_num => 300);
   SA_COMPONENTS.DROP_COMPARTMENT(policy_name =>'SADM', comp_num => 400);
   SA_COMPONENTS.DROP_COMPARTMENT(policy_name =>'SADM', comp_num => 500);
END;
/

-----
-- Drop security Groups
-----
/*
SELECT 
    'SA_COMPONENTS.DROP_GROUP(policy_name =>''' || policy_name || ''', group_num => ' || group_num || ');'
  FROM dba_sa_groups
 ORDER BY group_num;
*/
BEGIN
   SA_COMPONENTS.DROP_GROUP(policy_name =>'SADM', group_num => 0);
   SA_COMPONENTS.DROP_GROUP(policy_name =>'SADM', group_num => 10);
   SA_COMPONENTS.DROP_GROUP(policy_name =>'SADM', group_num => 20);
   SA_COMPONENTS.DROP_GROUP(policy_name =>'SADM', group_num => 30);
   SA_COMPONENTS.DROP_GROUP(policy_name =>'SADM', group_num => 40);
   SA_COMPONENTS.DROP_GROUP(policy_name =>'SADM', group_num => 50);
   SA_COMPONENTS.DROP_GROUP(policy_name =>'SADM', group_num => 60);
END;
/

-----
-- Drop security policy. Note that specifying TRUE for the drop_column
-- argument causes Oracle to drop the policy column from tables to which
-- the policy has been applied previously
-----
BEGIN
   SA_SYSDBA.DROP_POLICY(policy_name => 'SADM', drop_column => TRUE);
END;
/