Data Redaction and View Enhancements – Oracle 23ai(23.6)

Oracle 23.6 introduces new capabilities in Data Redaction, one of which is Data Redaction support at the view level. In previous versions, attempting to access a redacted column in a view resulted in the error ORA-28094: SQL construct not supported by data reduction. However, in version 23ai, such views can now be executed without errors. Below, we analyze this feature using a scenario in both versions 21c and 23ai. Creating the Table We first create a table: SQL> create table tb(id number, name varchar2(14), last_name varchar2(14), salary number); Table created. SQL> insert into tb values(1, 'Ali', 'Geraili', 9850000); 1 row inserted. SQL> commit; Commit complete. Creating the View We create a view based on this table: SQL> CREATE OR REPLACE VIEW vw_tb AS SELECT id, LOWER(name) AS first_name, LOWER(last_name) AS last_name, salary + 5000 AS "sal+5000" FROM tb; View created. Adding a Redaction Policy Next, we add a redaction policy to the table: SQL> BEGIN 2 DBMS_REDACT.ADD_POLICY( 3 object_schema => 'USEF', 4 object_name => 'TB', 5 policy_name => 'SALRY_REDACT', 6 expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''USEF''' 7 ); 8 END; 9 / PL/SQL procedure successfully completed. The policy hides the salary column from users other than USEF or those with the EXEMPT REDACTION POLICY privilege. Altering the Policy To completely redact the salary column, we alter the policy: SQL> BEGIN 2 DBMS_REDACT.ALTER_POLICY ( 3 object_schema => 'USEF', 4 object_name => 'TB', 5 policy_name => 'SALRY_REDACT', 6 column_name => 'SALARY', 7 action => DBMS_REDACT.ADD_COLUMN, 8 function_type => DBMS_REDACT.FULL); 9 END; 10 / PL/SQL procedure successfully completed. Testing with a New User We create a new user and grant them basic privileges: SQL> create user vahid identified by q; User created. SQL> grant create session, read any table to vahid; Grant succeeded. Connecting as the new user and executing queries: SQL> show user User is "VAHID" SQL> select * from usef.tb; ID NAME LAST_NAME SALARY ---- ----- -------------- ---------- 1 Ali Geraili 0 SQL> select * from usef.vw_tb; ID FIRST_NAME LAST_NAME sal+5000 ---- -------------- -------------- ---------- 1 ali geraili 0 Both queries execute successfully in Oracle 23.6. However, the same scenario in Oracle 21c results in an error for the view: SQLPlus: Release 21.0.0.0.0 - Production on Wed Jan 8 13:16:40 2025 SQL> select from usef.tb; ID NAME LAST_NAME SALARY ---------- -------------- -------------- ---------- 1 Ali Geraili 0 SQL> select * from usef.vw_tb; ORA-28094: SQL construct not supported by data redaction

Mar 13, 2025 - 20:09

Data Redaction and View Enhancements – Oracle 23ai(23.6)

Oracle 23.6 introduces new capabilities in Data Redaction, one of which is Data Redaction support at the view level. In previous versions, attempting to access a redacted column in a view resulted in the error ORA-28094: SQL construct not supported by data reduction. However, in version 23ai, such views can now be executed without errors. Below, we analyze this feature using a scenario in both versions 21c and 23ai.

Creating the Table
We first create a table:

SQL> create table tb(id number, name varchar2(14), last_name varchar2(14), salary number);
Table created.

SQL> insert into tb values(1, 'Ali', 'Geraili', 9850000);
1 row inserted.

SQL> commit;
Commit complete.

Creating the View

We create a view based on this table:

SQL> CREATE OR REPLACE VIEW vw_tb AS 
     SELECT id, LOWER(name) AS first_name, LOWER(last_name) AS last_name, salary + 5000 AS "sal+5000" 
     FROM tb;
View created.

Adding a Redaction Policy
Next, we add a redaction policy to the table:

SQL> BEGIN
  2   DBMS_REDACT.ADD_POLICY(
  3   object_schema => 'USEF',
  4   object_name => 'TB',
  5   policy_name => 'SALRY_REDACT',
  6   expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''USEF'''
  7   );
  8  END;
  9  /
PL/SQL procedure successfully completed.

The policy hides the salary column from users other than USEF or those with the EXEMPT REDACTION POLICY privilege.

Altering the Policy
To completely redact the salary column, we alter the policy:

SQL> BEGIN
  2   DBMS_REDACT.ALTER_POLICY (
  3   object_schema => 'USEF',
  4   object_name => 'TB',
  5   policy_name => 'SALRY_REDACT',
  6   column_name => 'SALARY',
  7   action => DBMS_REDACT.ADD_COLUMN,
  8   function_type => DBMS_REDACT.FULL);
  9  END;
 10  /
PL/SQL procedure successfully completed.

Testing with a New User
We create a new user and grant them basic privileges:

SQL> create user vahid identified by q;
User created.

SQL> grant create session, read any table to vahid;
Grant succeeded.

Connecting as the new user and executing queries:

SQL> show user
User is "VAHID"

SQL> select * from usef.tb;
  ID NAME   LAST_NAME          SALARY
---- -----  -------------- ----------
   1 Ali    Geraili                 0

SQL> select * from usef.vw_tb;
  ID FIRST_NAME     LAST_NAME        sal+5000
---- -------------- -------------- ----------
   1 ali            geraili                 0

Both queries execute successfully in Oracle 23.6. However, the same scenario in Oracle 21c results in an error for the view:

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Jan 8 13:16:40 2025

SQL> select * from usef.tb;
        ID NAME           LAST_NAME          SALARY
---------- -------------- -------------- ----------
         1 Ali            Geraili                 0

SQL> select * from usef.vw_tb;
ORA-28094: SQL construct not supported by data redaction