Hello, i have implemented column level masking using oracle VPD by creating policies on the table. The policy works fine on select of data. The updates also work without issue but when there is a trigger created on the table the updates fail with ORA-42804 error. Please see below.
--create table
CREATE TABLE TEST_POLICY(TEST_ID NUMBER,TEST_SSN VARCHAR2(20),test_column VARCHAR2(20));
--insert data
INSERT INTO TEST_POLICY VALUES (1,'1234-5678','test1');
INSERT INTO TEST_POLICY VALUES (2,'5678-1234','test2');
--policy function
CREATE OR REPLACE FUNCTION FN_MASK_TEST_SSN (objschema IN VARCHAR2, objname IN VARCHAR2)
RETURN VARCHAR2 AS
con VARCHAR2(200) default '1=2';
BEGIN
con := con||' or TEST_SSN <> null';
RETURN (con);
END FN_MASK_TEST_SSN;
--create policy
BEGIN
DBMS_RLS.ADD_POLICY(object_schema=>'MARS',
object_name=>'TEST_POLICY',
policy_name=>'PL_TEST_POLICY_SSN',
function_schema=>'MARS',
policy_function=>'FN_MASK_TEST_SSN',
statement_Types=>'SELECT',
sec_relevant_cols=>'TEST_SSN',
sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
END;
--test policy
SELECT * FROM TEST_POLICY;
--test update
UPDATE test_policy SET test_column = 'test3' WHERE test_id = 1;
The update works fine but as soon as we create the below trigger it will not succeed.
[pre]
--create dummy trigger
CREATE OR REPLACE TRIGGER "TEST_POLICY_AUDT"
AFTER INSERT OR UPDATE OR DELETE ON TEST_POLICY
FOR EACH ROW
BEGIN
IF (1=1) THEN
RETURN;
END IF;
END;
--update fails with ora-42804
UPDATE test_policy SET test_column = 'test3' WHERE test_id = 1;