My Oracle Support Banner

ORA-28094: SQL CONSTRUCT NOT SUPPORTED BY DATA REDACT (Doc ID 2457158.1)

Last updated on OCTOBER 16, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

 After the redaction policy is applied the reports which use the redacted
columns  are throwing the below error .Obiee report query forms with WITH
clause which is using CONCAT,TRIM,SUM functions for the redacted column which
is causing the issue

State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 28094,
message: ORA-28094: SQL construct not supported by data redaction at OCI call

OCIStmtExecute. (HY000)

Try Using redacted column(FULL redaction type) in below clause,

with SAWITH0 AS (
 select concat(v_d_cust_first_name,' ') from dim_customer )
 select *  from SAWITH0; -- This gives error where 'v_d_cust_first_name' is
redacted column

Select concat(v_d_cust_first_name,' ') from dim_customer ;  -- This works

 

Steps:-

 

1.Create User in sysdba of RDBMS 12.2.0.1.0,
    create user abc identified by abc default tablespace users temporary tablespace temp;
    grant connect, resource to abc;
    grant unlimited tablespace to abc;

2.Then grant user with below mentioned policies,
    grant select on sys.redaction_policies to abc;
    grant select on sys.redaction_columns to abc;
    grant execute on dbms_redact to abc;

3.Now Login to new Schema i.e abc and create new table as mentioned below,
    CREATE TABLE payment_details (
      id          NUMBER       NOT NULL,
      customer_id NUMBER       NOT NULL,
      card_no     NUMBER       NOT NULL,
      card_string VARCHAR2(19) NOT NULL,
      expiry_date DATE         NOT NULL,
      sec_code    NUMBER       NOT NULL,
      valid_date  DATE,
      CONSTRAINT payment_details_pk PRIMARY KEY (id)
    );

    INSERT INTO payment_details VALUES (1, 4000,1234123412341234, '1234-1234-1234-1234', TRUNC(ADD_MONTHS(SYSDATE,12)), 123, NULL);
    INSERT INTO payment_details VALUES (2, 4001,2345234523452345, '2345-2345-2345-2345', TRUNC(ADD_MONTHS(SYSDATE,12)), 234, NULL);
    INSERT INTO payment_details VALUES (3, 4002,3456345634563456, '3456-3456-3456-3456', TRUNC(ADD_MONTHS(SYSDATE,12)), 345, NULL);
    INSERT INTO payment_details VALUES (4, 4003,4567456745674567, '4567-4567-4567-4567', TRUNC(ADD_MONTHS(SYSDATE,12)), 456, NULL);
    INSERT INTO payment_details VALUES (5, 4004,5678567856785678, '5678-5678-5678-5678', TRUNC(ADD_MONTHS(SYSDATE,12)), 567, NULL);

SELECT * FROM  payment_details;

4.Apply Full Redaction Policy on column name 'card_no'

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'abc',
    object_name   => 'payment_details',
    column_name   => 'card_no',
    policy_name   => 'redact_card_info',
    function_type => DBMS_REDACT.full,
    expression    => '1=1'
  );
END;
/

/* SELECT object_owner, object_name,policy_name,expression,enable,policy_description FROM   redaction_policies where object_owner='ABC';
SELECT object_owner,object_name,column_name,function_type,function_parameters,regexp_pattern,regexp_replace_string,regexp_position,regexp_occurrence,regexp_match_parameter,column_description
FROM redaction_columns where object_owner='ABC'; */

5.Check if Redaction is applied,

select card_no from payment_details ORDER BY id; //Card_no will become 0 if the redaction is applied.

6.Perform below operations,

Select concat(card_no,' ') from payment_details ;//This works fine

with SAWITH0 AS (
 Select concat(card_no,' ') from payment_details )
 select *  from SAWITH0;//This is giving 'ORA-28094: SQL construct not supported by data redaction' error
 
 with SAWITH0 AS (
 Select sum(card_no) from payment_details )
 select *  from SAWITH0;//This is giving 'ORA-28094: SQL construct not supported by data redaction' error
 
 
 with SAWITH0 AS (
 Select trim(card_no) from payment_details )
 select *  from SAWITH0;//This is giving 'ORA-28094: SQL construct not supported by data redaction' error
 

Changes

 NA

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.