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 220.127.116.11. 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 18.104.22.168 – “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:
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|
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.
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.