----------------------------
-- 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;
/