Exploring Data Redaction Enhancements in Oracle Database 23ai

Data redaction, a feature introduced in Oracle Database 12c as part of the Advanced Security Option (ASO), continues to evolve in Oracle Database 23ai (23.6). Oracle has significantly enhanced the DBMS_REDACT package in this release, offering improved flexibility and SQL compatibility for redacted columns. These enhancements enable redaction policies to integrate more smoothly with modern SQL constructs, removing the errors and limitations that previously constrained their use.

This article provides an in-depth walkthrough of these new capabilities using practical SQL examples. It is essential to understand that redaction under the DBMS_REDACT package is a chargeable option unless you are on Oracle Autonomous Database, where it’s available at no extra cost.

Setting Up the Environment

To demonstrate and compare behavior between Oracle 19c and 23ai, we begin by setting up a user schema and creating a sample table. The examples work identically in both versions, highlighting the enhancements in 23ai during execution.

-- Connect as SYS
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

-- Drop and recreate user
drop user testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource, create view to testuser1;

-- Connect as the test user
conn testuser1/testuser1@//localhost:1521/freepdb1

-- Create and populate the transactions table
create table transactions (
  id      number generated always as identity,
  payment number
);

insert into transactions (payment) values (100.10);
insert into transactions (payment) values (100.00);
insert into transactions (payment) values (300.30);
insert into transactions (payment) values (400.40);
insert into transactions (payment) values (1000.10);
commit;

Validate the table and data:

select * from transactions;

Now, we add a redaction policy to redact the PAYMENT column completely.

-- Switch to SYS to add redaction policy
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

begin
  dbms_redact.add_policy(
    object_schema => 'testuser1',
    object_name   => 'transactions',
    column_name   => 'payment',
    policy_name   => 'redact_payment',
    function_type => dbms_redact.nullify,
    expression    => '1=1'
  );
end;
/

As the application user, we confirm the redaction is active:

conn testuser1/testuser1@//localhost:1521/freepdb1
select * from transactions;

-- Output
-- ID   PAYMENT
-- --   -------
-- 1    
-- 2    
-- 3    
-- 4    
-- 5    

Redacted Columns in SQL Expressions and Views

In Oracle 23ai, SQL expressions involving redacted columns—whether in views or inline views—are now valid. For instance, we create a view that uses an aggregate function:

create or replace view transactions_v as
select sum(payment) as sum_payment
from transactions;

-- Query the view
select * from transactions_v;

-- Output in 23ai
-- SUM_PAYMENT
-- -----------
--     0

In 19c, querying such a view results in the error ORA-28094: SQL construct not supported by data redaction. Oracle 23ai suppresses the value while allowing the statement to execute successfully.

GROUP BY on Expressions With Redacted Columns

You can now group by expressions derived from redacted columns without encountering errors. Here’s an example using ROUND():

select round(payment) as round_payment, count(*) as amount
from transactions
group by round(payment);

-- Output in 23ai
-- ROUND_PAYMENT   AMOUNT
-- -------------   ------
--       0           5

Even though the redacted values are concealed, the grouping logic functions, allowing developers to use aggregate analysis while respecting security.

In 19c, this would throw ORA-00979: not a GROUP BY expression.

Using DISTINCT and ORDER BY on Redacted Columns

Oracle 23ai permits the use of DISTINCT and ORDER BY clauses on redacted columns. The queries run successfully, and results are fully redacted:

select distinct payment
from transactions
order by payment;

-- Output in 23ai
-- PAYMENT
-- -------
--        
--        
--        
--        
--        

Using a transformation on the column:

select distinct trunc(payment)
from transactions
order by trunc(payment);

-- Output
-- TRUNC(PAYMENT)
-- --------------
--       0
--       0
--       0
--       0

Such operations in Oracle 19c would fail with ORA-01791: not a SELECTed expression.

Set Operators With Redacted Columns

Oracle 23ai enables the use of set operators like UNION and UNION ALL on queries that involve redacted columns. For example:

select payment from transactions
union all
select level from dual connect by level <= 5;

-- Output in 23ai
-- PAYMENT
-- -------
--     0
--     0
--     0
--     0
--     0
--     0
--     0
--     0
--     0
--     0

In contrast, Oracle 19c reveals the non-redacted part of the UNION ALL, exposing data from DUAL.

This also applies to inline views:

select * from (
  select payment from transactions
  union all
  select level from dual connect by level <= 5
);

-- Output in 23ai is fully redacted.

Redaction Behavior and Policy Optimization

Several important behaviors are consistent across these enhancements:

• Any SQL expression on a redacted column results in full redaction (null or zero, depending on the redaction method).
• When the result of an expression is a data type unsupported by redaction, it returns NULL.
• Set operators on redacted columns ensure full masking of output.

Moreover, policy expressions such as expression => '1=1' are now optimized. Since the condition is always true, the policy is evaluated only once, improving query performance slightly.

Support for Functional Indexes and Extended Statistics

A major limitation in prior versions was the inability to create function-based indexes or gather extended statistics on redacted columns. Oracle 23ai addresses this:

-- Example: Create a functional index on a redacted column
create index idx_trx_round_payment on transactions(round(payment));

-- Gather extended statistics
exec 
dbms_stats.gather_table_stats('TESTUSER1', 'TRANSACTIONS', method_opt=>'for all columns size auto');

This supports more performant queries without compromising redaction.

Final Considerations

Data redaction remains a powerful feature for protecting sensitive information in Oracle databases. The enhancements in Oracle 23ai remove many of the practical limitations that hindered its adoption in legacy applications. SQL statements that previously failed, such as those with GROUP BY, DISTINCT, or set operations now execute gracefully, returning fully redacted results without exposing sensitive data.

However, organizations must ensure they are appropriately licensed for the Advanced Security Option unless they're using Oracle Autonomous Database, where redaction is included by default.

These advancements in Oracle 23ai enable a more seamless integration of redaction policies into existing applications without rewriting logic or experiencing runtime errors, significantly improving the developer and DBA experience.