Friday, September 14, 2018

ORA-28086: The data redaction policy expression has an error When Cursor Sharing is Force

Adding a policy using dbms_redact on fails when the cursor sharing is force. The latest PSU available for the DB version has already been applied to the DB. But this didn't resolve the issue. The current patch applied
SQL> select comments from dba_registry_history;

Cursor sharing mode
SQL> show parameter cursor_sharing

NAME            TYPE     VALUE
--------------- -------- ---------
cursor_sharing  string   FORCE
Adding a policy using dbms_redact fails with following error
  2    DBMS_REDACT.add_policy(
  3      object_schema => 'asanga',
  4      object_name   => 'cc_details',
  5      column_name   => 'cc_no',
  6      policy_name   => 'redact_card_info',
  7      function_type => DBMS_REDACT.full,
  8      expression  => 'SYS_CONTEXT(''TOP_SEC'',''APP_USER'') <> ''asanga'' or SYS_CONTEXT(''TOP_SEC'',''APP_USER'') IS NULL'
  9    );
 10  END;
 11  /
ERROR at line 1:
ORA-28086: The data redaction policy expression has an error.
ORA-28087: The policy expression has an unsupported (use of) operator 'SYS_CONTEXT'.
ORA-06512: at "SYS.DBMS_REDACT_INT", line 3
ORA-06512: at "SYS.DBMS_REDACT", line 42
ORA-06512: at line 2
As a workaround change the cursor sharing to "exact" and run the add policy.

If cursor sharing mode cannot be changed apply the patch 20693579 which allows DBMS_REDACT.add_policy work even when cursor sharing is set to force.

Related Metalink Notes
Bug 20693579 DBMS_REDACT.ADD_POLICY fails with ORA-28086 when CURSOR_SHARING is force [ID 20693579.8]
Data Redaction Package DBMS_REDACT not valid after upgrading to [ID 1945055.1]
Create Data Redaction Policy returns error ORA-06521: PL/SQL: Error mapping function, ORA-06512: at "SYS.DBMS_REDACT_INT", line 3 [ID 2222699.1]