Oracle Database 12c: Data Redaction

Hello everyone!

Oracle Data Redaction is new a feature that provides the security of data from application end users
modifying sensitive data contained in SQL query results before the results returned the applications.

The Oracle Data Redaction is completely different from Oracle Data Masking.

Many companies want to protect their data from the end-users by performing redaction in each application.
Oracle Data Redaction is transparent, flexible and a simple solution, the columns are redacted according to the flexible policies that provide
conditional redaction. This policies are managed directly into the database.

Oracle Data Redaction is useful for many different scenarios, applications like payment card (PCI), protected health (PHI) or personally identifiable (PII) information that is subject to regulation.

Operational activities that are not subject to redaction:

Backup and restore
Import and export
Patching and upgrades
Replication

Avalible Redaction Methods:

Type                           Description
None                          No redaction is performed.
Full                             Columns are redacted to constant values based on the column data type.
Partial                         User-specified positions are replaced by a user-specified character.
Random                      Data type is preserved and different values are output each time.
Regular                       Expression A “match and replace” is performed based on parameters

Redaction policy:
As a first step, you have to define a redaction policy that specifies what should be redacted.
– Identify a schema, table or view and column to be redacted.
– Specify the redaction method to use for the column.

For example: You can choose to redact the column by default and show actual data only for certain users or IP address. You can put many conditions like that together using local operations.

Use the procedure DBMS_REDACT package to manage redaction policies:

You have to give the grant EXECUTE on DBMS_REDACT package to users who need to perform data redaction.

Let’s go:

Defining a readaction policy for SALARY column in the schema

begin
  DBMS_REDACT.ADD_POLICY
 (policy_name =>    ‘EMPSAL_POLICY’,
 object_schema => ‘HR’,
 object_name =>    ‘EMPLOYEES’,
 column_name =>  ‘SALARY’,
  expression =>
  ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”)!=”HR-VP”’,
  function_type => DBMS_REDACT.FULL);
end;
/

The table redaction_columns and the table redaction_policies show all related information about the redaction columns and redaction policies

Now lets query the data with redact:

How you can see the column SALARY with redact data.

Modifiying the Redaction Policy

begin
   DBMS_REDACT.ALTER_POLICY(
   policy_name =>             ‘EMPSAL_POLICY’,
   object_schema =>          ‘HR’,
   object_name =>             ‘EMPLOYEES’,
   column_name =>           ‘SALARY’,
   action =>                       DBMS_REDACT.MODIFY_column,
  function_type =>             DBMS_REDACT.partial,
  function_parameters => ‘9,1,10’
);
end;
/

Now re-run the query again:

You can see the column salary with data redact

Drop a redact policy:

BEGIN
 DBMS_REDACT.DROP_POLICY(
 object_schema =>   ‘HR’,
 object_name =>     ‘EMPLOYEES’,
 policy_name =>     ‘EMPSAL_POLICY’
);
END;
/

I already use this feature in some production databases, it is very good for some sensitive data information
in some cases, this feature can be used with other Oracle Database security solutions.

I hope that you enjoy!

Thaks!
Felipe.