Table of Contents

Oracle Label Security

Initial setup for LBAC admin

Assuming that OLS is enabled in the database, first step is to unlock the LBACSYS account, which holds OLS administrative privileges. After unlocking, the new query result will show:

SELECT * FROM dba_users;
username user_id account_status default_tablespace created authentication_type
LBACSYS 100 OPEN SYSTEM 14/08/20 PASSWORD
1_setup.sql
----------------------------------
-- SUMMARY OF OLS INITIAL ADMIN --
----------------------------------
 
---- AS SYSTEM
-- Initialize project tablespace
-- Revert: DROP TABLESPACE PROJECT2 INCLUDING CONTENTS;
CREATE TABLESPACE PROJECT2
  DATAFILE 'ay2122_cs5322_project2'
  SIZE 10M REUSE
  AUTOEXTEND ON;
 
-- See created tablespace
SELECT
  TABLESPACE_NAME, 
  FILE_NAME, 
  BYTES/1024/1024 MB
FROM DBA_DATA_FILES;
 
-- Check OLS enabled status
SELECT * FROM DBA_OLS_STATUS;
 
-- Unlock LBACSYS account
-- i.e. Label-Based Access Control System
-- Revert: ALTER USER LBACSYS ACCOUNT LOCK;
ALTER USER LBACSYS IDENTIFIED BY myNewPassword ACCOUNT UNLOCK;
 
-- Create user account for performing LBAC admin duties
-- Revert: DROP USER PROJECT2_DBA CASCADE;
CREATE USER PROJECT2_DBA IDENTIFIED BY myNewPassword DEFAULT TABLESPACE PROJECT2;
GRANT CONNECT TO PROJECT2_DBA; -- i.e. GRANT CREATE SESSION TO PROJECT2_DBA;
SELECT * FROM DBA_USERS WHERE USERNAME='PROJECT2_DBA';
 
---- AS LBACSYS
-- Grant LBAC_DBA role and EXECUTE privilege on SA_SYSDBA
-- Revert: REVOKE LBAC_DBA FROM PROJECT2_DBA;
GRANT LBAC_DBA TO PROJECT2_DBA;
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='PROJECT2_DBA';
 
 
-- Grant corresponding privileges, namely:
GRANT EXECUTE ON SA_SYSDBA TO PROJECT2_DBA; -- to create policies
GRANT EXECUTE ON SA_COMPONENTS TO PROJECT2_DBA; -- to create level, comp, group
GRANT EXECUTE ON SA_LABEL_ADMIN TO PROJECT2_DBA; -- to create labels
GRANT EXECUTE ON SA_USER_ADMIN TO PROJECT2_DBA; -- to authorize users for OLS
GRANT SELECT ON DBA_SA_USER_LEVELS TO PROJECT2_DBA; -- to see current OLS users
GRANT SELECT ON DBA_SA_USER_COMPARTMENTS TO PROJECT2_DBA; -- to see current OLS user compartments
GRANT SELECT ON DBA_SA_USER_GROUPS TO PROJECT2_DBA; -- to see current OLS user groups
GRANT SELECT ON DBA_SA_USER_PRIVS TO PROJECT2_DBA; -- to see current OLS user privileges
GRANT EXECUTE ON SA_POLICY_ADMIN TO PROJECT2_DBA; -- to apply policies to tables/schemas
 
-- Possibly related:
-- Check DBA system privileges: SELECT * FROM DBA_SYS_PRIVS;
-- Check assignment of user roles: SELECT * FROM USER_ROLE_PRIVS;
-- Check all roles: SELECT * FROM DBA_ROLES;
-- Might be needed: GRANT INHERIT PRIVILEGES ON USER SYSTEM TO LBACSYS;
-- Might be useful: GRANT EXEMPT ACCESS POLICY TO PROJECT2_DBA;

Difference between SYS vs SYSTEM user?

CONNECT SYS as SYSDBA?

Create OLS policies

OLS functionality is implemented as PL/SQL packages (procedural SQL), which then require the grammar DECLARE...BEGIN...END;/ to execute.

2_create_ols_policies.sql
-------------------------
-- CREATE OLS POLICIES --
-------------------------
 
---- AS PROJECT2_DBA
-- Create OLS policy container that specifies how policies are added
-- Default column: {{ policy_name }}_COL
-- Policy enforcement: https://docs.oracle.com/en/database/oracle/oracle-database/19/olsag/implementing-policy-enforcement-options-and-labeling-functions.html
-- Note that policies are automatically enabled upon creation.
-- Revoke: SA_SYSDBA.DROP_POLICY('INVENTORY_OLS_POL', TRUE);
BEGIN
  SA_SYSDBA.CREATE_POLICY (
    policy_name => 'INVENTORY_OLS_POL',
    default_options => 'ALL_CONTROL');
END;
/
 
-- Verify policy created
SELECT * FROM ALL_SA_POLICIES;
 
-- EXTREMELY IMPORTANT WARNING - will save you a couple hours:
-- When policy is created, the definer will be automatically assigned a role to
-- administrate the policy, i.e. {{ policy_name }}_DBA. You *must* disconnect
-- from the database and connect again to enable this role, otherwise
-- 'ORA-12407: unauthorized operation for policy %s' will always be raised.
-- N.B. Reconnect in SQLDeveloper does *not* work. Do the full disconnect + connect.
 
 
---- AS LBACSYS/SYSTEM
-- Verify policy-specific role assigned
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='PROJECT2_DBA';
 
 
---- AS PROJECT2_DBA
-- Create levels
BEGIN
  SA_COMPONENTS.CREATE_LEVEL (
    policy_name => 'INVENTORY_OLS_POL',
    level_num => 10,
    short_name => 'C',
    long_name => 'CONFIDENTIAL');
  SA_COMPONENTS.CREATE_LEVEL (
    policy_name => 'INVENTORY_OLS_POL',
    level_num => 20,
    short_name => 'S',
    long_name => 'SECRET');
  SA_COMPONENTS.CREATE_LEVEL (
    policy_name => 'INVENTORY_OLS_POL',
    level_num => 30,
    short_name => 'TS',
    long_name => 'TOP_SECRET');
END;
/
SELECT * FROM ALL_SA_LEVELS;
 
-- Create compartments
BEGIN
  SA_COMPONENTS.CREATE_COMPARTMENT (
    policy_name => 'INVENTORY_OLS_POL',
    comp_num => '1',
    short_name => 'C',
    long_name => 'COMBAT');
  SA_COMPONENTS.CREATE_COMPARTMENT (
    policy_name => 'INVENTORY_OLS_POL',
    comp_num => '2',
    short_name => 'T',
    long_name => 'TRANSPORT');
  SA_COMPONENTS.CREATE_COMPARTMENT (
    policy_name => 'INVENTORY_OLS_POL',
    comp_num => '3',
    short_name => 'M',
    long_name => 'MEDICAL');
END;
/
SELECT * FROM ALL_SA_COMPARTMENTS;
 
-- Create groups
BEGIN
  SA_COMPONENTS.CREATE_GROUP (
    policy_name => 'INVENTORY_OLS_POL',
    group_num => '0',
    short_name => 'JOINT',
    long_name => 'JOINT_COMMAND');
 
  SA_COMPONENTS.CREATE_GROUP (
    policy_name => 'INVENTORY_OLS_POL',
    group_num => '10',
    short_name => 'AIRFORCE',
    long_name => 'AIR_FORCE_SERVICE',
    parent_name => 'JOINT');
  SA_COMPONENTS.CREATE_GROUP (
    policy_name => 'INVENTORY_OLS_POL',
    group_num => '11',
    short_name => 'ARMY',
    long_name => 'ARMY_SERVICE',
    parent_name => 'JOINT');
 
  SA_COMPONENTS.CREATE_GROUP (
    policy_name => 'INVENTORY_OLS_POL',
    group_num => '20',
    short_name => 'D1',
    long_name => 'AIR_FORCE_DIVISION_1',
    parent_name => 'AIRFORCE');
  SA_COMPONENTS.CREATE_GROUP (
    policy_name => 'INVENTORY_OLS_POL',
    group_num => '21',
    short_name => 'D2',
    long_name => 'AIR_FORCE_DIVISION_2',
    parent_name => 'AIRFORCE');
  SA_COMPONENTS.CREATE_GROUP (
    policy_name => 'INVENTORY_OLS_POL',
    group_num => '22',
    short_name => 'D3',
    long_name => 'ARMY_DIVISION_3',
    parent_name => 'ARMY');
  SA_COMPONENTS.CREATE_GROUP (
    policy_name => 'INVENTORY_OLS_POL',
    group_num => '23',
    short_name => 'D4',
    long_name => 'ARMY_DIVISION_4',
    parent_name => 'ARMY');
END;
/
SELECT * FROM ALL_SA_GROUPS;
 
---- Drop policy and restart...!
--BEGIN
--  SA_SYSDBA.DROP_POLICY('INVENTORY_OLS_POL', TRUE);
--END;
--/
 
 
-- Create data labels
-- Looping: https://stackoverflow.com/questions/2242024/for-each-string-execute-a-function-procedure
-- To enable DBMS_OUTPUT in SQLDeveloper for debugging,
--   enable 'View > Dbms Output' and enable DBMS output for the specified connection.
--   Then add the line 'DBMS_OUTPUT.PUT_LINE(COMPARTMENTS(IDX));'
DECLARE
  TYPE STRLIST IS TABLE OF VARCHAR2(100);
  SINGLE_COMPARTMENTS STRLIST;
  MULTI_COMPARTMENTS STRLIST;
  S_TS_LEVELS STRLIST;
  ALL_LEVELS STRLIST;
  G1_GROUPS STRLIST;
  G2_GROUPS STRLIST;
  ALL_GROUPS STRLIST;
  IDX_COUNTER BINARY_INTEGER;
BEGIN
  IDX_COUNTER := 0;
  SINGLE_COMPARTMENTS := STRLIST('C','T','M','');
  MULTI_COMPARTMENTS := STRLIST('C,T','C,M','T,M','C,T,M');
  S_TS_LEVELS := STRLIST('S','TS');
  ALL_LEVELS := STRLIST('C','S','TS');
  G1_GROUPS := STRLIST('AIRFORCE','ARMY');
  G2_GROUPS := STRLIST('D1','D2','D3','D4');
  ALL_GROUPS := STRLIST('JOINT','AIRFORCE','ARMY','D1','D2','D3','D4');
 
  ---------------------------------------------------------------------------
  -- Joint-level user labels, necessarily TS
  FOR IDX IN MULTI_COMPARTMENTS.FIRST..MULTI_COMPARTMENTS.LAST
  LOOP
    IDX_COUNTER := IDX_COUNTER + 1;
    SA_LABEL_ADMIN.CREATE_LABEL (
      policy_name => 'INVENTORY_OLS_POL',
      label_tag => IDX_COUNTER,
      label_value => 'TS:'||MULTI_COMPARTMENTS(IDX)||':JOINT',
      data_label => FALSE);
  END LOOP;
 
  -- Joint-level data labels, necessarily TS, only one of each compartment
  FOR IDX IN SINGLE_COMPARTMENTS.FIRST..SINGLE_COMPARTMENTS.LAST
  LOOP
    IDX_COUNTER := IDX_COUNTER + 1;
    SA_LABEL_ADMIN.CREATE_LABEL (
      policy_name => 'INVENTORY_OLS_POL',
      label_tag => IDX_COUNTER,
      label_value => 'TS:'||SINGLE_COMPARTMENTS(IDX)||':JOINT',
      data_label => TRUE);
  END LOOP;
 
  ---------------------------------------------------------------------------
  -- Service-level user labels, necessarily S and above
  FOR GIDX IN G1_GROUPS.FIRST..G1_GROUPS.LAST
  LOOP
    FOR CIDX IN MULTI_COMPARTMENTS.FIRST..MULTI_COMPARTMENTS.LAST
    LOOP
      FOR LIDX IN S_TS_LEVELS.FIRST..S_TS_LEVELS.LAST
      LOOP
        IDX_COUNTER := IDX_COUNTER + 1;
        SA_LABEL_ADMIN.CREATE_LABEL (
          policy_name => 'INVENTORY_OLS_POL',
          label_tag => IDX_COUNTER,
          label_value => S_TS_LEVELS(LIDX)||':'||MULTI_COMPARTMENTS(CIDX)||':'||G1_GROUPS(GIDX),
          data_label => FALSE);
      END LOOP;
    END LOOP;
  END LOOP;
 
  -- Service-level data labels, necessarily S and above, only one of each compartment
  FOR GIDX IN G1_GROUPS.FIRST..G1_GROUPS.LAST
  LOOP
    FOR CIDX IN SINGLE_COMPARTMENTS.FIRST..SINGLE_COMPARTMENTS.LAST
    LOOP
      FOR LIDX IN S_TS_LEVELS.FIRST..S_TS_LEVELS.LAST
      LOOP
        IDX_COUNTER := IDX_COUNTER + 1;
        SA_LABEL_ADMIN.CREATE_LABEL (
          policy_name => 'INVENTORY_OLS_POL',
          label_tag => IDX_COUNTER,
          label_value => S_TS_LEVELS(LIDX)||':'||SINGLE_COMPARTMENTS(CIDX)||':'||G1_GROUPS(GIDX),
          data_label => TRUE);
      END LOOP;
    END LOOP;
  END LOOP;
 
  ---------------------------------------------------------------------------
  -- Division-level user labels
  FOR GIDX IN G2_GROUPS.FIRST..G2_GROUPS.LAST
  LOOP
    FOR CIDX IN MULTI_COMPARTMENTS.FIRST..MULTI_COMPARTMENTS.LAST
    LOOP
      FOR LIDX IN ALL_LEVELS.FIRST..ALL_LEVELS.LAST
      LOOP
        IDX_COUNTER := IDX_COUNTER + 1;
        SA_LABEL_ADMIN.CREATE_LABEL (
          policy_name => 'INVENTORY_OLS_POL',
          label_tag => IDX_COUNTER,
          label_value => ALL_LEVELS(LIDX)||':'||MULTI_COMPARTMENTS(CIDX)||':'||G2_GROUPS(GIDX),
          data_label => FALSE);
      END LOOP;
    END LOOP;
  END LOOP;
 
  -- Division-level data labels, only one of each compartment
  FOR GIDX IN G2_GROUPS.FIRST..G2_GROUPS.LAST
  LOOP
    FOR CIDX IN SINGLE_COMPARTMENTS.FIRST..SINGLE_COMPARTMENTS.LAST
    LOOP
      FOR LIDX IN ALL_LEVELS.FIRST..ALL_LEVELS.LAST
      LOOP
        IDX_COUNTER := IDX_COUNTER + 1;
        SA_LABEL_ADMIN.CREATE_LABEL (
          policy_name => 'INVENTORY_OLS_POL',
          label_tag => IDX_COUNTER,
          label_value => ALL_LEVELS(LIDX)||':'||SINGLE_COMPARTMENTS(CIDX)||':'||G2_GROUPS(GIDX),
          data_label => TRUE);
      END LOOP;
    END LOOP;
  END LOOP;
END;
/
SELECT * FROM ALL_SA_LABELS;
 
-- Drop all labels and restart...!
BEGIN
  FOR ROW IN (SELECT * FROM ALL_SA_LABELS)
  LOOP
    SA_LABEL_ADMIN.DROP_LABEL (
      policy_name => 'INVENTORY_OLS_POL',
      label_value => ROW.LABEL);
  END LOOP;
END;
/

Create OLS users

3_authorize_users.sql
-------------------------
-- AUTHORIZE OLS USERS --
-------------------------
 
---- AS PROJECT2_DBA
-- Set user levels, compartments, and groups
-- See: https://docs.oracle.com/en/database/oracle/oracle-database/19/olsag/creating-an-oracle-label-security-policy.html#GUID-180AE22F-C31A-48A9-AE4A-2CAB334746E8
-- Important note: Privileges are *bypasses*, not part of usual policy.
-- See: Slide 21 on https://www.comp.nus.edu.sg/~tankl/cs5322/slides/ols.pdf
--   - READ: Read all rows regardless of policy, e.g. data exporters
--   - FULL: READ privilege + ability to write to *all* data, e.g. data owner
--   - COMPACCESS: Allow access independent of user's groups (but with caveats)
--   - PROFILE_ACCESS: Allow proxy access as other users
--   - WRITEUP/WRITEDOWN: Change sensitivity (by default cannot change once set)
--   - WRITEACROSS: Change compartments/groups, while sensitivity remains the same
DECLARE
  TYPE STRLIST IS TABLE OF VARCHAR2(100);
  ASSIGNMENTS STRLIST;
  USERNAME VARCHAR(100);
  SENS VARCHAR(100);
  COMPS VARCHAR(100);
  GROUPS VARCHAR(100);
  PRIVL VARCHAR(100);
  N BINARY_INTEGER;
BEGIN
  N := 5; -- number of columns in 'ASSIGNMENTS'
  ASSIGNMENTS := STRLIST (
    'PROJECT2_TEST_TS','TS','C','JOINT','', -- GCHQ combat staff
    'PROJECT2_TEST_S','S','T','AIRFORCE','', -- senior commander in air force
    'PROJECT2_TEST_C','C','C,M,T','D1','', -- inventory manager for division 1
    'PROJECT2_TEST_COMM1','TS','C,M,T','D1','WRITEUP', -- secret independent unit mission commander
    'PROJECT2_TEST_COMM2','C','M,T','D2','', -- regular humanitarian mission planner
    'PROJECT2_TEST_COMM3','S','C,M,T','JOINT','WRITEDOWN,WRITEACROSS', -- inventory declassifier (up to S only, no deletion)
    'PROJECT2_TEST_COMM4','C','C,M,T','JOINT','READ', -- inventory auditor (read-only table access as well)
    'PROJECT2_TEST_OTHER1','TS','C,M,T','JOINT','FULL' -- defence minister
  );
 
  -- Drop everything first before writing, just to be safe
  FOR ROW IN (SELECT * FROM DBA_SA_USER_LEVELS)
  LOOP
    SA_USER_ADMIN.DROP_USER_ACCESS (
      policy_name => 'INVENTORY_OLS_POL',
      user_name => ROW.USER_NAME);
  END LOOP;
 
  -- Set levels
  FOR IDX IN ASSIGNMENTS.FIRST..(ASSIGNMENTS.LAST/N)
  LOOP
    USERNAME := ASSIGNMENTS(N*(IDX-1)+1);
    SENS := ASSIGNMENTS(N*(IDX-1)+2);
    COMPS := ASSIGNMENTS(N*(IDX-1)+3);
    GROUPS := ASSIGNMENTS(N*(IDX-1)+4);
    PRIVL := ASSIGNMENTS(N*(IDX-1)+5);
    SA_USER_ADMIN.SET_LEVELS (
      policy_name => 'INVENTORY_OLS_POL',
      user_name => USERNAME,
      max_level => SENS); -- defaults to highest as well
    SA_USER_ADMIN.SET_COMPARTMENTS (
      policy_name => 'INVENTORY_OLS_POL',
      user_name => USERNAME,
      read_comps => COMPS);
    SA_USER_ADMIN.SET_GROUPS (
      policy_name => 'INVENTORY_OLS_POL',
      user_name => USERNAME,
      read_groups => GROUPS);
    SA_USER_ADMIN.SET_USER_PRIVS (
      policy_name => 'INVENTORY_OLS_POL',
      user_name => USERNAME,
      privileges => PRIVL);
  END LOOP;
END;
/
SELECT * FROM DBA_SA_USER_LEVELS;
SELECT * FROM DBA_SA_USER_COMPARTMENTS;
SELECT * FROM DBA_SA_USER_GROUPS;
SELECT * FROM DBA_SA_USER_PRIVS;
 
-- EXTREMELY IMPORTANT WARNING (again):
-- Once OLS privileges have been assigned, commit, reset your connection again,
-- and save yourself another hour or so. If resetting doesn't change anything,
-- consider resetting all existing connections as well.

Assign policies to table

4_apply_policies.sql
----------------------------
-- ASSIGN POLICY TO TABLE --
----------------------------
 
---- AS SYSTEM
-- Create appropriate schemas
-- Unfortunately, users and schemas are strongly coupled in Oracle DB
-- Revert: DROP USER INVENTORY CASCADE;
CREATE USER INVENTORY DEFAULT TABLESPACE PROJECT2 QUOTA UNLIMITED ON PROJECT2;
 
-- Create appropriate tables
-- If OLS policy applied to schema, tables will automatically be under policy as well.
-- Sidenote: Can't figure out how to give tailored access to table creation...
-- No such thing as being able to grant access to an individual schema,
-- but need to grant CREATE ANY TABLE. Alternative for least privilege is
-- to grant proxy connection to user/schema: https://dba.stackexchange.com/a/13325
-- Revert: DROP TABLE INVENTORY.VEHICLE;
CREATE TABLE INVENTORY.VEHICLE (
  vehicle_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
  vehicle_model VARCHAR2(100) NOT NULL,
  PRIMARY KEY(vehicle_id));
 
 
---- AS PROJECT2_DBA
-- Apply policies to table/schema
-- For fine-grained table access control, use 'APPLY_TABLE_POLICY'.
-- See: https://docs.oracle.com/en/database/oracle/oracle-database/19/olsag/oracle-label-security-pl-sql-packages.html#GUID-5C1F501B-293A-436E-865D-51D34E3D0829
BEGIN
  SA_POLICY_ADMIN.APPLY_SCHEMA_POLICY (
    policy_name => 'INVENTORY_OLS_POL',
    schema_name => 'INVENTORY');
END;
/
SELECT * FROM ALL_SA_TABLE_POLICIES;
 
-- Drop policy
BEGIN
  SA_POLICY_ADMIN.REMOVE_SCHEMA_POLICY (
    policy_name => 'INVENTORY_OLS_POL',
    schema_name => 'INVENTORY',
    drop_column => TRUE);
END;
/

Apply labels to table rows

5_apply_labels.sql
-------------------
-- USER CREATION --
-------------------
 
---- AS SYSTEM
-- Create test users
-- Revert: DROP USER PROJECT2_TEST_TS CASCADE;
CREATE USER PROJECT2_TEST_TS IDENTIFIED BY myNewPassword DEFAULT TABLESPACE PROJECT2;
GRANT CONNECT TO PROJECT2_TEST_TS;
CREATE USER PROJECT2_TEST_S IDENTIFIED BY myNewPassword DEFAULT TABLESPACE PROJECT2;
GRANT CONNECT TO PROJECT2_TEST_S;
CREATE USER PROJECT2_TEST_C IDENTIFIED BY myNewPassword DEFAULT TABLESPACE PROJECT2;
GRANT CONNECT TO PROJECT2_TEST_C;
CREATE USER PROJECT2_TEST_COMM1 IDENTIFIED BY myNewPassword DEFAULT TABLESPACE PROJECT2;
GRANT CONNECT TO PROJECT2_TEST_COMM1;
CREATE USER PROJECT2_TEST_COMM2 IDENTIFIED BY myNewPassword DEFAULT TABLESPACE PROJECT2;
GRANT CONNECT TO PROJECT2_TEST_COMM2;
CREATE USER PROJECT2_TEST_COMM3 IDENTIFIED BY myNewPassword DEFAULT TABLESPACE PROJECT2;
GRANT CONNECT TO PROJECT2_TEST_COMM3;
CREATE USER PROJECT2_TEST_COMM4 IDENTIFIED BY myNewPassword DEFAULT TABLESPACE PROJECT2;
GRANT CONNECT TO PROJECT2_TEST_COMM4;
CREATE USER PROJECT2_TEST_OTHER1 IDENTIFIED BY myNewPassword DEFAULT TABLESPACE PROJECT2;
GRANT CONNECT TO PROJECT2_TEST_OTHER1;
 
-- Drop
--DROP USER PROJECT2_TEST_TS CASCADE;
--DROP USER PROJECT2_TEST_S CASCADE;
--DROP USER PROJECT2_TEST_C CASCADE;
--DROP USER PROJECT2_TEST_COMM1 CASCADE;
--DROP USER PROJECT2_TEST_COMM2 CASCADE;
--DROP USER PROJECT2_TEST_COMM3 CASCADE;
--DROP USER PROJECT2_TEST_COMM4 CASCADE;
--DROP USER PROJECT2_TEST_OTHER1 CASCADE;
 
-- Grant DBA user access to table in schema
GRANT SELECT,INSERT,UPDATE,DELETE ON INVENTORY.VEHICLE TO PROJECT2_TEST_TS;
GRANT SELECT,INSERT,UPDATE,DELETE ON INVENTORY.VEHICLE TO PROJECT2_TEST_S;
GRANT SELECT,INSERT,UPDATE,DELETE ON INVENTORY.VEHICLE TO PROJECT2_TEST_C;
GRANT SELECT,INSERT,UPDATE,DELETE ON INVENTORY.VEHICLE TO PROJECT2_TEST_COMM1;
GRANT SELECT,INSERT,UPDATE,DELETE ON INVENTORY.VEHICLE TO PROJECT2_TEST_COMM2;
GRANT SELECT,INSERT,UPDATE ON INVENTORY.VEHICLE TO PROJECT2_TEST_COMM3;
GRANT SELECT ON INVENTORY.VEHICLE TO PROJECT2_TEST_COMM4;
GRANT SELECT,INSERT,UPDATE,DELETE ON INVENTORY.VEHICLE TO PROJECT2_TEST_OTHER1;
 
 
-------------------
-- OLS FUNCTIONS --
-------------------
 
---- AS OLS user
-- COMP_READ reads out compartments of current user session, GROUP_READ for groups,
-- both of which are reflected in LABEL.
SELECT SA_SESSION.LABEL('INVENTORY_OLS_POL') FROM DUAL; -- current user session label
SELECT SA_SESSION.ROW_LABEL('INVENTORY_OLS_POL') FROM DUAL; -- current data label to be applied
SELECT SA_SESSION.PRIVS('INVENTORY_OLS_POL') FROM DUAL; -- current list of privileges
 
-- Change current user session labels
BEGIN
  SA_SESSION.SET_LABEL('INVENTORY_OLS_POL','C:C:D1');
END;
/
 
-- Change data label to be applied by default
BEGIN
  SA_SESSION.SET_LABEL('INVENTORY_OLS_POL','TS:C:AIRFORCE');
END;
/
 
-- Restore to original user session label and data label
-- Alternatively, save them with 'SAVE_DEFAULT_LABELS'.
BEGIN
  SA_SESSION.RESTORE_DEFAULT_LABELS('INVENTORY_OLS_POL');
END;
/
 
 
-----------------------
-- USER INTERACTIONS --
-----------------------
 
-- AS OLS user
-- View rows
SELECT * FROM INVENTORY.VEHICLE;
 
-- Add labels if data does not have labels (i.e. policy added after table populated)
UPDATE INVENTORY.VEHICLE
  SET INVENTORY_OLS_POL_COL = CHAR_TO_LABEL('INVENTORY_OLS_POL','C:C:D1')
  WHERE VEHICLE_ID IN (2, 3);
 
-- Add new data + label
INSERT INTO INVENTORY.VEHICLE VALUES (4, 'CH-47 Chinook', CHAR_TO_LABEL('INVENTORY_OLS_POL','S:C:JOINT'));