Oracle Redaction in the 12c Database: Advanced security for regulatory compliance

May 8, 2015 at 3:56 pm | Posted in Oracle, Technical Tips | Comments Off on Oracle Redaction in the 12c Database: Advanced security for regulatory compliance
Tags: ,

Oracle Redaction in the 12c database is part of the Oracle Advanced Security package. It can be used as a standalone feature, or together with other components of Advanced Security. Oracle redaction allows you to set up policies so that when column data is retrieved, it can be masked or hidden from the person performing the query. It can affect all or part of the data in a column and the data can be any of the more common datatypes that Oracle supports. Redaction allows you to comply with industry regulations such as the Payment Card Industry Data Security Standard as well as the Sarbanes-Oxley Act.

You’ve probably already seen this feature at work when you receive documents from the government or your bank with key data partially hidden, such as social security numbers and credit card numbers. Social security numbers are commonly masked to allow only the last 4 digits to be read, appearing as ***-**-1234. Salary is another column that is commonly redacted in reports.

The key to using redaction is the Oracle supplied package DBMS_REDACT and the procedures and functions contained within it. The procedure DBMS_REDACT.ADD_POLICY allows you to set up a policy on a column of a table or view. For example, view the following statement:

object_schema   =>  ‘bob’,
object_name     =>  ‘employee’,
column_name     =>  ‘salary’,
policy_name     =>  ‘protect salary in bobs employee table’,
enable          =>  TRUE

This statement will create a new policy with the given name on the salary column of the employee table in the schema called bob. Note that all the arguments of this procedure are IN, and that the arguments shown are all VARCHAR2 with the exception of enable, which is BOOLEAN. This policy will be enabled as soon as it’s created, and it will apply to all users except SYS and any other user who happens to have the system privilege called EXEMPT_REDACTION_POLICY. Further, it will redact the entire salary column and use a 0 as the default masking character, since the datatype of the salary column is number.

All of these defaults can be changed to create complex rules with the logic encapsulated in the policy. For example, you may want the policy to apply only to users who have a particular role or who are logged in at a particular time. You also might want the salary to be displayed as a series of $ signs instead of a zero, and if you were performing redaction on social security number, you may only want to redact part of the column so that the last 4 digits are still visible. This procedure has additional arguments that allow you to do all of these things and more. You can even redact a number column such as salary so that the column appears as a random number rather than the true value or a 0. I’m hard pressed to think of a business example where you might want to use this feature, so if you have a  good example, PLEASE reply to this blog and let me know in the comments!

Redaction should apply to read-only situations, since you don’t want a user to update a column in a row without being able to see the old value in the column, or even the new value for that matter.

As far as SQL statement processing is concerned, the redaction is the very last thing that occurs.  In the past you may have said to yourself that the last thing done in a SELECT statement is to sort the returned rows if the ORDER BY clause is part of the query.  Now we have one more potential step that might be performed on the returned rows after the sort: namely, the redaction.

This post just scrapes the surface of everything you can do with this tool.  My hope is that I’ve been able to stir your interest in this topic if you have applications where redaction  is appropriate.  More information about redaction can be found in Chapter 9 of the Oracle 12c Advanced Security Guide.  Click this link if you’d like to become more familiar with this tool:


Bob, the OrclTestGuy

Entries and comments feeds.

%d bloggers like this: