Oracle Label Security SECDEMO Script

secdemo.sql

spool secdemolog
set echo off
set define on
set verify on

----------------------
--   DEFINITIONS    --
----------------------
define app=SECDEMO
define appdesc=SECDEMO
define syspwd=welcome1
define walletpwd=welcome1
define lbacsyspwd=welcome1
define dvacctmgr=dvacctmgr
define dvacctmgrpwd=welcome1
define default_pwd=welcome1

-- Directories
define directory_location=/oracle/product/db/oradata/orcl/
define file_dir=&appdesc._DIR
define file_dir_physical=/home/oracle/demos/&appdesc/docs

-- Security Types
define type_special=TopSecret
define type_special_not=Secret
define type_internal_comp1=Fraud
define type_internal_comp2=Narcotics
define type_internal_comp3=Terrorism

-- OLS
define ols_policy_name=&appdesc._ACCESS
define ols_column=OLS_COLUMN_&appdesc

-- Users
define appowner=APP_&app._OWNER
define usr_special=&appdesc._jcooper
define usr_internal=&appdesc._mtwain
define usr_internal_comp1=&appdesc._ltolstoy
define usr_internal_comp2=&appdesc._cdoyle
define usr_internal_comp3=&appdesc._jausten
define usr_resourcedba=&appdesc._sfitzgerald
define usr_acctmgr=&appdesc._wfaulk
define usr_secadmin=&appdesc._cdickens

-- Roles
define approle=&appdesc._USR

-- Table Data
define ts_encrypted=&appdesc._&type_special
define ts_unencrypted=&appdesc._&type_special_not
 
----------------------
--    CLEAN UP      --
----------------------

connect sys/&syspwd as sysdba

DECLARE
  wallet_status VARCHAR2(10);
BEGIN
  SELECT STATUS INTO wallet_status FROM v$encryption_wallet;
  IF wallet_status = 'CLOSED' THEN
    EXECUTE IMMEDIATE 'alter system set encryption wallet open identified by "&walletpwd"';
  END IF;
END;
/

connect lbacsys/&lbacsyspwd
exec sa_sysdba.drop_policy('&appdesc._ACCESS', true);

connect &dvacctmgr/&dvacctmgrpwd
begin
  execute immediate 'DROP USER &appowner CASCADE';
exception
  when others then
    if sqlcode != -1918 then
      raise;
    end if;
end;
/

connect sys/&syspwd as sysdba
drop role &approle;

-- Drop the table
drop tablespace &ts_encrypted including contents and datafiles;
drop tablespace &ts_unencrypted including contents and datafiles;

-- Create Tablespaces
create tablespace &ts_encrypted datafile '&directory_location/&ts_encrypted..dbf' size 50M reuse autoextend on encryption using 'AES256' default storage (encrypt maxsize unlimited);
create tablespace &ts_unencrypted datafile '&directory_location/&ts_unencrypted..dbf' size 50M reuse autoextend on maxsize unlimited;

connect &dvacctmgr/&dvacctmgrpwd;
begin
  execute immediate 'DROP USER &usr_special CASCADE';
exception
  when others then
    if sqlcode != -1918 then
      raise;
    end if;
end;
/
begin
  execute immediate 'DROP USER &usr_internal CASCADE';
exception
  when others then
    if sqlcode != -1918 then
      raise;
    end if;
end;
/
begin
  execute immediate 'DROP USER &usr_internal_comp1 CASCADE';
exception
  when others then
    if sqlcode != -1918 then
      raise;
    end if;
end;
/
begin
  execute immediate 'DROP USER &usr_internal_comp2 CASCADE';
exception
  when others then
    if sqlcode != -1918 then
      raise;
    end if;
end;
/
begin
  execute immediate 'DROP USER &usr_internal_comp3 CASCADE';
exception
  when others then
    if sqlcode != -1918 then
      raise;
    end if;
end;
/
begin
  execute immediate 'DROP USER &usr_resourcedba CASCADE';
exception
  when others then
    if sqlcode != -1918 then
      raise;
    end if;
end;
/
begin
  execute immediate 'DROP USER &usr_acctmgr CASCADE';
exception
  when others then
    if sqlcode != -1918 then
      raise;
    end if;
end;
/
begin
  execute immediate 'DROP USER &usr_secadmin CASCADE';
exception
  when others then
    if sqlcode != -1918 then
      raise;
    end if;
end;
/

-- Create Users
--- Create Account Manager and Security Administrator
connect &dvacctmgr/&dvacctmgrpwd;
create user &usr_acctmgr IDENTIFIED BY &default_pwd;
grant dv_acctmgr to &usr_acctmgr;
create user &usr_secadmin identified by &default_pwd;

--- Grant DV Owner to Sec Admin
connect dvowner/&default_pwd
grant dv_admin to &usr_secadmin;

--- Create User Accounts
connect &usr_acctmgr/&default_pwd

create user &usr_resourcedba identified by &default_pwd;
create user &appowner identified by &default_pwd default tablespace &ts_encrypted;
create user &usr_special identified by &default_pwd;
create user &usr_internal identified by &default_pwd;
create user &usr_internal_comp1 identified by &default_pwd;
create user &usr_internal_comp2 identified by &default_pwd;
create user &usr_internal_comp3 identified by &default_pwd;

--- Grant roles
connect sys/&default_pwd as sysdba
grant dba to &appowner;
grant execute on ctx_ddl to &appowner;
grant dba to &usr_secadmin;
grant dba to &usr_resourcedba;
create role &approle;

connect &usr_acctmgr/&default_pwd;
grant connect to &approle;

-- Create Table

connect &appowner/&default_pwd;
CREATE TABLE documents (
  doc_no VARCHAR2(10), 
  author VARCHAR2(15), 
  date_created VARCHAR2(10),
  doc_category VARCHAR2(10),
  doc_source BLOB,
  sensitivity VARCHAR2(10),
  mime VARCHAR2(50),
   doc_name VARCHAR2(50)
) LOB (doc_source) STORE AS SECUREFILE (COMPRESS CACHE);

create or replace directory &file_dir as '&file_dir_physical';

 -- Configure Label Security
connect lbacsys/&default_pwd;
begin
  SA_SYSDBA.CREATE_POLICY(
    policy_name => '&ols_policy_name',
    column_name => '&ols_column',
    default_options => 'READ_CONTROL,LABEL_DEFAULT,HIDE'
  );
end;
/

-- Add OLS policy maintenance responsbilities to the Security Admin
grant &ols_policy_name._DBA to &usr_secadmin;
grant execute on sa_components to &usr_secadmin;
grant execute on sa_user_admin to &usr_secadmin;
grant execute on sa_label_admin to &usr_secadmin;
grant execute on sa_policy_admin to &usr_secadmin;
grant execute on sa_audit_admin to &usr_secadmin;
grant execute on to_lbac_data_label to &appowner WITH GRANT OPTION;

connect &appowner/&default_pwd;
create or replace function &appowner..generate_label (Classification VARCHAR2, Compartment VARCHAR2)
  return LBACSYS.LBAC_LABEL
as
begin
  return TO_LBAC_DATA_LABEL('&ols_policy_name', Classification||':'||Compartment);
end;
/

-- Create a labelling policy
grant execute on &appowner..generate_label to lbacsys;

connect &usr_secadmin/&default_pwd;
--- Create Levels
exec sa_components.create_level('&ols_policy_name', 1000, '&type_special_not', '&type_special_not');
exec sa_components.create_level('&ols_policy_name', 2000, '&type_special', '&type_special');
-- Create the compartments
exec sa_components.create_compartment('&ols_policy_name', 100, '&type_internal_comp1', '&type_internal_comp1');
exec sa_components.create_compartment('&ols_policy_name', 200, '&type_internal_comp2', '&type_internal_comp2');
exec sa_components.create_compartment('&ols_policy_name', 300, '&type_internal_comp3', '&type_internal_comp3');
-- Create the data labels
exec sa_label_admin.create_label('&ols_policy_name', 1100, '&type_special_not:&type_internal_comp1');
exec sa_label_admin.create_label('&ols_policy_name', 1200, '&type_special_not:&type_internal_comp2');
exec sa_label_admin.create_label('&ols_policy_name', 1300, '&type_special_not:&type_internal_comp3');
exec sa_label_admin.create_label('&ols_policy_name', 2100, '&type_special:&type_internal_comp1');
exec sa_label_admin.create_label('&ols_policy_name', 2200, '&type_special:&type_internal_comp2');
exec sa_label_admin.create_label('&ols_policy_name', 2300, '&type_special:&type_internal_comp3');

exec sa_user_admin.set_user_labels('&ols_policy_name', '&usr_special', '&type_special:&type_internal_comp1,&type_internal_comp2,&type_internal_comp3');
exec sa_user_admin.set_user_labels('&ols_policy_name', '&usr_internal', '&type_special:&type_internal_comp1,&type_internal_comp2');
exec sa_user_admin.set_user_labels('&ols_policy_name', '&usr_internal_comp1', '&type_special_not:&type_internal_comp1');
exec sa_user_admin.set_user_labels('&ols_policy_name', '&usr_internal_comp2', '&type_special_not:&type_internal_comp2');
exec sa_user_admin.set_user_labels('&ols_policy_name', '&usr_internal_comp3', '&type_special_not:&type_internal_comp3');

-- Apply OLS policy and label rows
--- Allow app owner to apply the labels (later)
execute SA_USER_ADMIN.SET_USER_PRIVS('&ols_policy_name', '&appowner', 'FULL');

--- Apply the policy to the table
begin
  SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
    policy_name => '&ols_policy_name', 
    schema_name => '&appowner', 
    table_name  => 'documents',
    table_options => NULL,
    label_function => '&appowner..generate_label(:new.sensitivity,:new.doc_category)');
end;
/

connect &appowner/&default_pwd
create or replace public synonym SECDEMO for &appowner..documents;

grant select,insert,update,delete on SECDEMO to &approle;
grant select,insert,update,delete on documents to &approle;
grant &approle to &usr_special;
grant &approle to &usr_internal;
grant &approle to &usr_internal_comp1;
grant &approle to &usr_internal_comp2;
grant &approle to &usr_internal_comp3;