What You See Is What You Get Element

Tips and Tricks: Data Redaction in Oracle Database 12c

By Alex Zaballa, Oracle Ace

 

Introduction

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:

  • Virtual Private Database (VPD) - Allows control access on both row and column levels by dynamically adding a predicate to SQL statements issued against the database.
  • Oracle Label Security – Allows you to add user-defined values ​​to table records combining it with VPD to allow fine control of who sees what​​.
  • Database Vault – Data Redaction does not prevent privileged users (such as DBAs) from having access to the data being protected. To solve this, you can make use of Database Vault.

Licensing wise, Oracle Data Masking is available only with Enterprise Edition database and it requires licensing of Advanced Security.

How It Works


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.

  • DBMS_REDACT.ALTER_POLICY – allows changes to existing policies.
  • DBMS_REDACT.DISABLE_POLICY – disables an existing policy.
  • DBMS_REDACT.DROP_POLICY – drop an existing policy.
  • DBMS_REDACT.ENABLE_POLICY – enables an existing policy.
  • DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES – change the default return value for full redaction. You must restart the database to take effect.

data-red0



You can protect data at the column level using one of the following methods::

  • Full redaction – All content of the column is protected and the type of value returned depends on the data type of the column. For numeric columns, the value zero will be returned. For columns of type character, a space will be returned. This setting can be changed at the database level.
  • Partial redaction – Only part of the information is changed. For example, the first digits of the credit card number are replaced by asterisks.
  • Regular expressions - You can use regular expressions to search for patterns of data that must be protected.
  • Random redaction – Returned values ​​are random; each time a query is executed, the displayed data will be different.
  • No redaction - Allows testing the inner workings of redaction policies, with no effect on the results of current running queries. This is widely used during testing phase of redaction policies that will eventually find their way to production environments.

data-red9

 


data-red1


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.

Dictionary Views:


The following new database views can be used to obtain information regarding existing redaction policies:

  • REDACTION_POLICIES
  • REDACTION_COLUMNS
  • REDACTION_VALUES_FOR_TYPE_FULL


Data Redaction and Data Pump



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:

CONTENT=METADATA_ONLY

INCLUDE=RADM_FPTM,RADM_POLICY


Data Redaction and Create tables as select (CTAS):


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:


data-red2


Accessing Oracle Data Redaction:


data-rad8


Screen Management Policies:


data-red3


Creating a policy to redact the data from the salary column of EMPLOYEES table for all users apart from the user named SUPERVISOR:


data-red6


data-red4


Verifying if the policy works as desired:


[oracle@dibutu ~]$ sqlplus funcionario/senha@localhost:1521/testpdb
SQL*Plus: Release 12.1.0.1.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 12.1.0.1.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 


Querying the table again, this time connected as the user SUPERVISOR:


[oracle@dibutu ~]$ sqlplus supervisor/senha@localhost:1521/testpdb
SQL*Plus: Release 12.1.0.1.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 12.1.0.1.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 


Creating the same policy using SQL * Plus:

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:


 

data-rad5



data-red7

 

About the Author

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.
oraworldlogo