Oracle Data Redaction is one of the new features introduced in Oracle Database 12c. This new feature is part of the Advanced Security option and enables the protection of data shown to the user in real time, without requiring changes to the application.
Oracle Database 12c applies protection at query execution time. The stored data remain unchanged, while the data to be displayed is transformed on-the-fly before leaving the database.
This feature is not to be confused with Oracle Data Masking which has been available since version 11g. With Oracle Data Masking, the data is processed using masked shapes and this updated data is stored in new data blocks. For this reason, Data Masking is more suitable for non-production environments.
Below are some other features that already existed to help making the data more secure:
Licensing wise, Oracle Data Masking is available only with Enterprise Edition database and it requires licensing of Advanced Security.
We can create redaction policies which specify conditions that must be met before the data gets redacted and returned to the user. During the definition of such policies, the DBA can specify which columns and the type of protection that must be applied.
The package used to create protection rules is called DBMS_REDACT. The package includes five procedures to manage the rules and an additional procedure to change the default value for full redaction policy.
You can protect data at the column level using one of the following methods::
Data Redaction can help meet safety regulations, such as Payment Card Industry, Data Security Standard (PCI DSS) and Sarbanes-Oxley.
It can be used with the following column data types: NUMBER, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, BLOB, CLOB, and NCLOB.
The following new database views can be used to obtain information regarding existing redaction policies:
The role DATAPUMP_EXP_FULL_DATABASE includes the EXEMPT REDACTION POLICY system privilege. This way, tables can be exported using Data Pump without being redacted.
If an attempt to export a table is made by a user that has not been granted the EXEMPT REDACTION POLICY system privilege, the following error will be displayed:
ORA-28081: Insufficient privileges - the command references a redacted object.
To export the metadata related to the policies of the Oracle Data Redaction, you can use the following parameters of the expdp utility:
In order to issue CTAS from a table protected by an active redaction policy, the user must have privileges to see the actual data on the source table.
PDB used in the examples with EM 12c:
Accessing Oracle Data Redaction:
Screen Management Policies:
Creating a policy to redact the data from the salary column of EMPLOYEES table for all users apart from the user named SUPERVISOR:
[oracle@dibutu ~]$ sqlplus funcionario/senha@localhost:1521/testpdb SQL*Plus: Release 184.108.40.206.0 Production on Wed Apr 2 10:22:46 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 220.127.116.11.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> select EMPLOYEE_ID, FIRST_NAME, SALARY from hr.employees WHERE ROWNUM < 5; EMPLOYEE_ID FIRST_NAME SALARY ----------- -------------------- ---------- 100 Steven 0 101 Neena 0 102 Lex 0 103 Alexander 0
[oracle@dibutu ~]$ sqlplus supervisor/senha@localhost:1521/testpdb SQL*Plus: Release 18.104.22.168.0 Production on Wed Apr 2 10:26:04 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 22.214.171.124.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> select EMPLOYEE_ID, FIRST_NAME, SALARY from hr.employees WHERE ROWNUM < 5; EMPLOYEE_ID FIRST_NAME SALARY ----------- -------------------- ---------- 100 Steven 24000 101 Neena 17000 102 Lex 17000 103 Alexander 9000
SQL> BEGIN DBMS_REDACT.ADD_POLICY (OBJECT_SCHEMA => 'HR', object_name => 'EMPLOYEES', policy_name => 'POLITICA_TESTE', expression => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''SUPERVISOR'''); DBMS_REDACT.ALTER_POLICY (OBJECT_SCHEMA => 'HR', object_name => 'EMPLOYEES', policy_name => 'POLITICA_TESTE', action => DBMS_REDACT.ADD_COLUMN, column_name => '"SALARY"', function_type => DBMS_REDACT.FULL ); END; / PL/SQL procedure successfully completed.
Creating the same policy using SQLDeveloper:
Alex Zaballa is an Oracle Ace and a member of Oraworld. Alex is currently working as a Senior DBA in Luanda and provides DBA services for the Ministry of Finance of Angola. With over 14 years of experience with Oracle technologies, he began his career as an Oracle developer, participating in the development of an ERP software from definition to deployment. In 2007 he moved to Angola where he had the opportunity to work as a Senior Oracle DBA providing services for Jupiter. As a member of the select group of highly qualified professionals worldwide who have the credential OCM - Oracle Certified Master and, among others, his specialties include design and implementation of high scalability, high availability and performance tuning. He is a respected member of OraWorld, a group that is constantly working with the Oracle community creating articles, conferences, webinars and Oracle Database courses. The OraWorld members are "Oracle Certified Masters" and "Oracle ACEs". You can follow this group through the following links: https://www.facebook.com/oraworldteam https://twitter.com/oraworld_team www.oraworld-team.com.