DBA Hub

📋Steps in this guide1/9

Data Redaction (DBMS_REDACT) Enhancements in Oracle Database 23ai/26ai (23.6)

This article demonstrates a number of the enhancements to Data Redaction in Oracle Database 23ai/26ai (23.6).

oracle 23configurationintermediate
by OracleDba
20 views
1

Setup

This setup has purposely been written to work on both 23ai/26ai and 19c, so we can compare the results between them. We create a test user and connect to it to run the examples. We create and populate a test table. We create a redaction policy to redact the column. We can see the redaction is now working.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

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

conn testuser1/testuser1@//localhost:1521/freepdb1

--drop table transactions purge;
create table transactions (
  id      number generated always as identity,
  payment number 
);

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

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;
/

conn testuser1/testuser1@//localhost:1521/freepdb1

select * from transactions;

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

SQL>
2

Complex Expressions on Redacted Columns in Views

In 23ai/26ai SQL expressions involving a redacted column are allowed in the select list of a view or in an inline view. We create a view on the redacted column using a SQL function. At this point there are no differences between 19c and 23ai/26ai. The difference becomes apparent when we try to query the view, or use an inline view to do something similar. In 23ai/26ai this is the result of the queries of the view, or an inline view. Notice that the data is still redacted in 23ai/26ai, but the queries run without error. Doing the same queries on 19c would result in the following error.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
create or replace view transactions_v as
select sum(payment) sum_payment
from   transactions;

select * from transactions_v;

SUM_PAYMENT
-----------
          0

SQL>


select *
from   (select sum(payment) sum_payment
        from   transactions);

SUM_PAYMENT
-----------
          0

SQL>

ORA-28094: SQL construct not supported by data redaction
3

GROUP BY on Expressions On Columns With Redaction Policies

In Oracle database 23ai/26ai the select list and clause can reference expressions on redacted columns. In the following example we use the function on the column to remove the fractional component. We reference that rounded value in both the select list and the clause to get an idea of the numbers of each transaction size. We get the output we expect, with the transaction amount redacted. Clearly here is limited value to this output without the transaction value, but the redaction policy has been honoured and we've not generated an error. If we try to run the same query in 19c we get the following error.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select round(payment) as round_payment, count(*) as amount
from   transactions
group by round(payment);

ROUND_PAYMENT     AMOUNT
------------- ----------
            0          2
            0          1
            0          1
            0          1

SQL>

ORA-00979: not a GROUP BY expression
4

DISTINCT with ORDER BY on Redacted Columns

In Oracle database 23ai/26ai and can reference redacted columns. We have 5 distinct values in the column, but only four if we remove the fractional components. We can see this reflected in output of the following queries, even though the data remains redacted. In Oracle database 19c, both those queries would return the following error.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
select distinct payment
from   transactions
order by payment;

   PAYMENT
----------






5 rows selected.

SQL>


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

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

4 rows selected.

SQL>

ORA-01791: not a SELECTed expression
5

Set Operators with Redacted Columns

In Oracle database 23ai/26ai we can use set operators with queries referencing redacted columns. In the following example we use a to combine the results of two queries, only one of which references a redacted column. Notice the output is fully redacted. In Oracle 19c we would have got the following output. We can also use set operators in the subqueries of inline views. In this example we use the same query as part of an inline view. In Oracle 19c this would have resulted in the following error.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
select payment from transactions
union all
select level from dual connect by level <= 5;

   PAYMENT
----------
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0

10 rows selected.

SQL>

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

   PAYMENT
----------





         1
         2
         3
         4
         5

10 rows selected.

SQL>

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

   PAYMENT
----------
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0

10 rows selected.

SQL>

ORA-28094: SQL construct not supported by data redaction
6

Redaction Results

Many of the rules for how redaction results are displayed have been shown in the previous examples, but it's worth mentioning them again. - SQL expressions on redacted columns always result in full redaction. - If an SQL expressions results in a data type not supported by redacted, a null will be displayed. - Set operators used with redacted columns will always result in full redaction for the relevant columns.
7

Optimization of Policy Expressions

Redactions policies using a policy expression of "1=1" will always be true, so they are no longer evaluated, making these redaction policies more efficient.
8

Extended Statistics and Functional Indexes on Redacted Columns

In Oracle database 23ai/26ai we can use redacted columns with function-based indexes, and gather extended statistics on redacted columns.
9

Considerations

Here are some thoughts about these redaction enhancements. - Redaction is still part of the Advanced Security Option, which limits its usefulness. If you using Autonomous Database, redaction is available at no extra cost. - Many of these redaction enhancements allow redaction to be added to an application without breaking existing SQL statements, and causing them to throw errors. - The expressions and operators that result in fully redacted output may seem odd at first, but this helps prevent inference accidentally exposing redacted values. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!