DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

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

#oracle #database #postgres #sql

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.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Vahid Yousefzadeh
Oracle Database Administrator
✉ vahidusefzadeh@gmail.com
Telegram channel :https://t.me/oracledb

Top comments (0)