-------------------
-- 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'));