We observe that, If redaction policy applied to a column then the column can't be used for DDL/DML (irrespective to the whom the policy applied or whether the policy is disabled or not) ,For example, CREATE TABLE TEST AS SELECT redact_column FROM redact_table or UPDATE TEST SET non_redact_column = (SELECT SELECT redact_column FROM redact_table WHERE rownum =1 , both the statements will throw oracle error ORA-28081: Insufficient privileges - the command references a redaction object),unless the user has EXEMPT REDACTION POLICY privilege.
The challenge we are facing -- if we grant the privilege to the user our batches work fine ( as the above sqls get passed through), but, because of the privilege UI can view the clear data, but we want UI should see redacted data. So, wonder whether we can do anything at the session level, because both UI and batch connect to the database using the same user id.
BTW, I see another gap in data redaction - even if DDL./DML is not allowed on redact column unless having the exempt privilege, but we can bypass this by using CURSOR LOOP, without having the privilege. For example, the following works
FOR CUR__REC IN (SELECT redact_column FROM redact_table) LOOP
UPDATE non_redact_table
SET non_redact_column = cur_rec.redact_column
WHERE id = cur_rec.id;
END LOOP;