Oracle Data Redaction in Oracle Database 12c

Data Redaction provides a way to define masking policies for an application. Oracle Data Redaction provides functionality to mask (redact) data that is returned from user SELECT queries. The masking takes place in real time. The difference between Oracle Data Masking and Data Redaction is that Data Redaction doesn’t alter underlying data in the database; it redacts the data only when it is being displayed. Data Redaction can be applied conditionally, based on different factors such as user, application identifiers, or client IP addresses. Data Redaction is available in Oracle Database 12c and now also in 11g Release 2, patch set 11.2.0.4. Data Redaction is licensed as part of Oracle Advanced Security.

EDIT: Be aware of Oracle’s data redaction “constraints”. Read David Litchfield’s white paper “Oracle Data Redaction is Broken” here (PDF). I checked all three described methods in my labs in September 2016 and using “RETURNING INTO” and “XMLQUERY()” methods appears to be fixed. However one gap still persist in a Database 12c version 12.1.0.2 – “an iterative inference attack”. It is still possible to be executed disclosing redacted data even to a regular test_user schema!

Below TEST 3 result based on David’s white paper example:

select cc from APEX_ZION.REDACTIONTEST;
CC 
-------
XXXXXXXXXXXXXXXX

--an iterative inference attack
exec p_undoredaction;
PL/SQL procedure successfully completed.
CC: 4111222233334444

Data redaction doesn’t prevent application logic, operations like inserting, updating or deleting data  are perfectly consistent with original data. If the application user creates a view on redacted table, the view will also contain the redacted data.

The following methods are available in data redaction:

Oracle Data Redaction in Oracle Enterprise Manager 12c:

In our demo we will be using sample table created in “APEX_ZION” schema running in Oracle Database 12c pluggable database. Customer data resides in CUSTOMERS table in APEX_ZION schema. We’ve got ~50k of records in the table. We generated our sample data at fakenamegenerator.com and imported it using Oracle SQL Developer.

Here is an sql script to create a demo table with several rows and test user account:

as APEX_ZION user
create table customers (
	customer_id		number,
	cust_first_name		varchar2(20),
	cust_last_name		varchar2(20),
	cust_phone_nr		varchar2(25),
	CUST_STREET_ADDRESS	varchar2(60),
	cust_postal_code	varchar2(10),
	cust_city		varchar2(30),
	cust_email		varchar2(60),
	cust_url		varchar2(100),
	cust_national_id	varchar2(20)
	
Insert into CUSTOMERS values ('124','George','Haynes','478-226-7323','4145 Oakridge Lane','30901','Augusta','[email protected]','closedloopcolor.com','670-09-4251');
Insert into CUSTOMERS values ('125','William','Bill','205-968-8067','3553 Wright Court','35243','Cahaba Heights','[email protected]','jswimshop.com','419-92-3203');
Insert into CUSTOMERS values ('126','Rhonda','Finley','217-615-9990','1405 University Hill Road','62701','Springfield','[email protected]','istanbulchekup.com','345-40-5460');
Insert into CUSTOMERS values ('127','Rosana','Solis','630-912-3504','747 Lewis Street','60606','Chicago','[email protected]','germanfacials.com','342-30-5422');
Insert into CUSTOMERS values ('128','Craig','Montz','407-296-6024','1326 Barnes Street','32808','Orlando','[email protected]','moocroot.com','768-32-5412');
Insert into CUSTOMERS values ('129','Todd','Barrios','325-282-5551','2445 Felosa Drive','79506','Blackwell','[email protected]','rekzai.com','636-84-3118');
Insert into CUSTOMERS values ('130','Ida','Cochran','334-566-0879','3410 Turkey Pen Lane','36081','Troy','[email protected]','onwebinsurance.com','417-04-4580');
Insert into CUSTOMERS values ('131','Lewis','Anderson','812-442-2562','709 Lucy Lane','47834','Brazil','[email protected]','oreauction.com','309-98-5662');
Insert into CUSTOMERS values ('132','Cheryl','Winters','559-501-6054','2155 Chicago Avenue','93721','Fresno','[email protected]','mcdube.com','573-54-1811');
Insert into CUSTOMERS values ('133','Leann','Sullivan','773-396-7992','1060 Oakmound Drive','60605','Chicago','[email protected]','localvim.com','356-78-9654');

commit;

connect sys / as sysdba
create user test_user identified by *******;
grant insert, select, delete, update on APEX_ZION.customers to test_user;
grant create session to test_user;

Our goal is to redact the following columns for all other users than apex_zion:

Column Name	Data Redaction method
cust_phone_nr	Partial
cust_postal_code	Random
cust_email	RegExp
cust_national_id	Partial

Login to the Oracle Enterprise Manager 12c, next login to a target database and create data redaction policy:

New policies are effective immediately, let’s test it in Oracle SQL Developer by selecting the same data from two different user accounts:

Data Redaction policies can also be managed in Oracle SQL Developer.

First we have to grant the following privileges to user APEX_ZION

--as sysdba user:
grant select on sys.redaction_policies to apex_zion;
grant select on sys.redaction_columns to apex_zion;
grant execute on dbms_redact to apex_zion;

Data Redaction in SQL Developer:

That’s all folks. For more information please check DBMS_REDACT at Oracle Docs.

Please leave your comments down below.