How to redact a Column in a Table Object in Oracle Database 19c

Important Considerations

1. Oracle Redaction use requires license for Oracle Advance Security for Oracle Database
2. Identify the PII data in your database
3. Review the roles & privileges assigned to the Users for unintentional redaction exemption
4. Carefully evaluate the data that needs to be redacted when called from other sources or applications

1. Create a TEST user called (checkuser) on whom the redaction will be applied

SQL> create user checkuser identified by checkuser;

SQL> grant create session to checkuser;

2. Create another User in the Database on whose Object the redaction policy will be implemented

SQL> create user IIS identified by IIS;

SQL> grant connect to IIS;

SQL> grant resource to IIS;

SQL> alter user IIS quota unlimited on users;

3. Create the an object Table

sqlplus IIS/IIS

SQL> create table user_info(
user_id number,
name varchar2(20),
sn varchar2(20),
corp_card varchar(20),
dt date); 2 3 4 5 6

Table created.

SQL> declare
type array_t is varray(20) of varchar2(10);
2 3 array array_t := array_t(‘James’,’Mary’,’Robert’,’Patricia’,’John’,’Jennifer’,’Michael’,’Linda’,’David’,’Elizabeth’,’William’,’Barbara’,’Richard’,’Susan’,’Joseph’,’Jessica’,’Thomas’,’Sarah’, ‘Alex’, ‘Thomas’);
4 begin
5 for i in 1..array.count loop
6 insert into user_info values(
7 trunc(dbms_random.value(low => 1, high => 900)),
array(i),
to_char(trunc(dbms_random.value(low => 100, high => 900)))||’-‘||to_char(trunc(dbms_random.value(low => 100, high => 900)))||’-‘||to_char(trunc(dbms_random.value(low => 100, high => 900))),
dbms_random.string(‘x’,20),
sysdate + dbms_random.value(0,366)
);
end loop;
commit;
end;
/ 8 9 10 11 12 13 14 15 16

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format=’DD/MM/YYYY HH24:MI:SS’;

SQL> select * from user_info;

USER_ID NAME SN CORP_CARD
———- ——————– ——————– ——————–
DT
——————-
25 James 850-529-461 MR1U168FQ7BO1N7Q6QAV
27/08/2026 08:53:03

186 Mary 758-773-424 3ACJE0OWO9B29V62E248
28/04/2026 18:52:17

405 Robert 729-857-414 HU5CA1VB7KL94MWLZ4W2
30/09/2026 19:39:58

USER_ID NAME SN CORP_CARD
———- ——————– ——————– ——————–
DT
——————-
229 Patricia 203-377-112 2P9MCHR6ORBQS0HVNIZS
25/10/2026 14:48:12

899 John 151-309-217 KD12QIWUMXED3D9DKUFW
26/11/2025 10:34:47

794 Jennifer 731-441-152 P84EA79MOF893JE8453V
26/12/2025 07:17:56

USER_ID NAME SN CORP_CARD
———- ——————– ——————– ——————–
DT
——————-
29 Michael 166-768-279 W2Y1KM2975S339LTMDHS
09/08/2026 06:22:50

716 Linda 873-563-113 X1TIWX47CBSWPYMD17NO
30/08/2026 00:03:11

842 David 717-478-376 YSVSSW0ZV3J0EEI3V0ZJ
15/04/2026 04:36:46

USER_ID NAME SN CORP_CARD
———- ——————– ——————– ——————–
DT
——————-
2 Elizabeth 432-648-452 FMWCHAZMEPQ17C7VFQC1
25/09/2026 06:20:50

142 William 173-625-623 DERW1E8IP5EAYHH8RFLU
22/02/2026 21:19:54

675 Barbara 466-678-819 HWKR33ZA4DBDVWEWFHBP
03/03/2026 02:55:18

USER_ID NAME SN CORP_CARD
———- ——————– ——————– ——————–
DT
——————-
616 Richard 507-735-617 QGU4D9VOGTHCHZARLCDD
26/06/2026 05:54:42

602 Susan 799-180-227 0358O4N1M7G96SU4V7EE
05/01/2026 07:45:15

190 Joseph 173-627-151 PC6OQWHX9LY0BIFPTR62
14/06/2026 14:10:01

USER_ID NAME SN CORP_CARD
———- ——————– ——————– ——————–
DT
——————-
648 Jessica 277-553-705 2U36T2SHEDVKJJH2864C
19/05/2026 09:12:30

520 Thomas 109-223-562 9TTVMGE0RXQKPQ0BWV1Z
04/01/2026 11:23:32

801 Sarah 896-389-264 Y9SKWM3W0PP8VF4TA397
07/12/2025 07:01:36

USER_ID NAME SN CORP_CARD
———- ——————– ——————– ——————–
DT
——————-
504 Alex 174-105-406 IRW5XGS2O7D04NXF9YLG
11/04/2026 22:04:21

654 Thomas 231-201-288 DZJG7CSJH2KKP2DXMWKS
08/08/2026 17:34:28

20 rows selected.

 

4. GRANT SELECT Privileges on the Table to the CHECKUSER

SQL> grant select on user_info to checkuser;

Grant succeeded.

 

 

5. Creating a Redaction POLICY (SYS User)

SQL> BEGIN
DBMS_REDACT.ADD_POLICY (
2 3 object_schema => ‘IIS’
4 ,object_name => ‘USER_INFO’
5 ,policy_name => ‘AUDITING’
6 ,expression => ‘SYS_CONTEXT(”USERENV”, ”CLIENT_PROGRAM_NAME”) = ”sqlplus@test (TNS V1-V3)” AND SYS_CONTEXT(”USERENV”, ”CLIENT_PROGRAM_NAME”) = ”Toad.exe”’);
END;
/

PL/SQL procedure successfully completed.

SQL> show user
USER is “SYS”

SQL> BEGIN
DBMS_REDACT.ALTER_POLICY (
OBJECT_SCHEMA => ‘IIS’
,object_name => ‘USER_INFO’
,policy_name => ‘AUDITING’
,action => DBMS_REDACT.ADD_COLUMN
,column_name => ‘SN’
,function_type => DBMS_REDACT.PARTIAL
,function_parameters => ‘vvvfvvvfvvv,vvv-vvv-vvv,#,4,6’ );
END;
/
PL/SQL procedure successfully completed.

The above expression used implies that any user connecting through SQL Plus* or TOAD application will see this data redacted. For further information on expressions you may check the below link
https://docs.oracle.com/en/database/oracle/oracle-database/26/dbred/policy-expressions-that-use-sys_context-attributes.html

 

 

6. Check the REDACTION Policies created in the Database

Set lines 110
set pages 9999
col policy_name format a30
col expression format a40
col enable format a8
col object_owner format a19
col object_name format a20
col column_name format a15
col function_type format a25

prompt ===== Current Data Redaction policies
select policy_name, expression, enable from redaction_policies;

POLICY_NAME EXPRESSION ENABLE
—————————— —————————————- ——–
AUDITING SYS_CONTEXT(‘USERENV’, ‘CLIENT_PROGRAM_N YES
AME’) = ‘sqlplus@test’ AND SYS_CONTEXT
(”USERENV”, ”CLIENT_PROGRAM_NAME”)
= ‘Toad.exe’

select object_owner, object_name, column_name, function_type from redaction_columns;
===== Current Objects redacted by a Data Redaction policy

OBJECT_OWNER OBJECT_NAME COLUMN_NAME FUNCTION_TYPE
——————- ——————– ————— ————————-
IIS USER_INFO SN PARTIAL REDACTION

 

 

7. Validating if the Redaction is working on the defined object

SQL> conn checkuser/checkuser
Connected.
SQL> set lines 300 pages 100
SQL> select * from IIS.user_info;

USER_ID NAME SN CORP_CARD DT
———- ——————– ——————– ——————– ———
25 James 850-###-461 MR1U168FQ7BO1N7Q6QAV 27-AUG-26
186 Mary 758-###-424 3ACJE0OWO9B29V62E248 28-APR-26
405 Robert 729-###-414 HU5CA1VB7KL94MWLZ4W2 30-SEP-26
229 Patricia 203-###-112 2P9MCHR6ORBQS0HVNIZS 25-OCT-26
899 John 151-###-217 KD12QIWUMXED3D9DKUFW 26-NOV-25
794 Jennifer 731-###-152 P84EA79MOF893JE8453V 26-DEC-25
29 Michael 166-###-279 W2Y1KM2975S339LTMDHS 09-AUG-26
716 Linda 873-###-113 X1TIWX47CBSWPYMD17NO 30-AUG-26
842 David 717-###-376 YSVSSW0ZV3J0EEI3V0ZJ 15-APR-26
2 Elizabeth 432-###-452 FMWCHAZMEPQ17C7VFQC1 25-SEP-26
142 William 173-###-623 DERW1E8IP5EAYHH8RFLU 22-FEB-26
675 Barbara 466-###-819 HWKR33ZA4DBDVWEWFHBP 03-MAR-26
616 Richard 507-###-617 QGU4D9VOGTHCHZARLCDD 26-JUN-26
602 Susan 799-###-227 0358O4N1M7G96SU4V7EE 05-JAN-26
190 Joseph 173-###-151 PC6OQWHX9LY0BIFPTR62 14-JUN-26
648 Jessica 277-###-705 2U36T2SHEDVKJJH2864C 19-MAY-26
520 Thomas 109-###-562 9TTVMGE0RXQKPQ0BWV1Z 04-JAN-26
801 Sarah 896-###-264 Y9SKWM3W0PP8VF4TA397 07-DEC-25
504 Alex 174-###-406 IRW5XGS2O7D04NXF9YLG 11-APR-26
654 Thomas 231-###-288 DZJG7CSJH2KKP2DXMWKS 08-AUG-26

20 rows selected.

SQL> show user
USER is “CHECKUSER”

AS YOU CAN SEE THE REDACTION POLICY APPLIED WHEN CONNECTED THROUGH SQL Plus*