Oracle 11.2.0.4 has been released - Redaction has been backported!

Greetings!!

Oracle 11.2.0.4 had been released for 32-Bit and 64-Bit Linux and Solaris (SPARC and X86 versions). It's available for download on the Metalink Oracle Support (MOS) page at support.oracle.com. Of course you have to have an Oracle support contract to download the update.

One new feature in Oracle Database 12c was Oracle Data Redaction. This new feature has been backported to Oracle Database 11.2.0.4!

Oracle data redaction helps you to mask data that is returned by SQL statements. The data is not stored in the database in redacted form, but is only displayed in it's redacted form.

There are three different kinds of redaction:

• Full redaction - The complete column is redacted. For example, a number might be replaced with all x's characters, or the column might return as just a blank.
• Partial redaction - A regular expression is used to format the redaction such that only part of the column value is redacted. For example, a social security number of  000-00-0000 might be displayed as xxx-xx-0000.
• Random redaction - The database will just display the data in the column as randomly generated characters.

You enable redaction on a column by column basis using the dbms_redact.add_policy package. In this call you define the owner, table and column name to be redacted. You also define the name of the policy, the function type and then expression. In some cases, Oracle provides functions that you can use (such as the dbms_redact.redact_use_ssn_f5 function which redacts SSN's).

Here is a basic example of redaction:

We have this table in the scott schema:

sql> connect scott/password

SQL> desc emp
 Name                Type
 ------------------- ----------------------------
 EMPNO               NUMBER(4)
 ENAME               VARCHAR2(10)
 JOB                 VARCHAR2(9)
 MGR                 NUMBER(4)
 HIREDATE            DATE
 SAL                 NUMBER(7,2)
 COMM                NUMBER(7,2)
 DEPTNO              NUMBER(2)


select ename, sal from emp;

SQL> select ename, sal from emp;

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

14 rows selected.

We probably don't want to be showing the SAL column to just anyone. So, let's add a redact policy to redact the values in the SAL column:

First we are going to drop any pre-existing policy. Otherwise we would get an error if the policy already existed when we try to create it:

BEGIN 

 DBMS_REDACT.DROP_POLICY(
   object_schema    => 'SCOTT'
,  object_name      => 'EMP' 

,  policy_name      => 'mask_sal'

);

end;

/

Now let's add the policy:

begin 

DBMS_REDACT.ADD_POLICY(
   object_schema    => 'SCOTT'
,  object_name      => 'EMP'
,  column_name      => 'SAL'
,  policy_name      => 'mask_sal'
,  function_type    => DBMS_REDACT.FULL
,  expression       => '1=1'
); 

end; 

/

Note that in the ADD_POLICY call, we name the schema, object and column we want to redact. 

We then name the policy. The function_type parameter defines the type of redaction that is occurring here. In our case, we will redact the whole thing.

The expression parameter provides a lot of flexability with respect to which user 

accounts the the redaction policy policy applies to and which users it does not 

apply too. In this case, the 1=1 means that the policy will apply to all users 

and roles. Having added the policy, let's note the following:

Any user with DBA or SYSDBA privileges will not have the result set redacted. As such, 

this new feature does not replace Oracle Data Vault in any way. The user that owns the 

object that has the redaction policy defined on it, will not have the result set 

redacted. So, if we are logged in as SCOTT we would see the same results before. 

This is because SCOTT owns the table. Let's see what happens though when another 

user that has SELECT access queries the table:

connect testing/password

SQL> select ename, sal from scott.emp;

ENAME             SAL
---------- ----------
SMITH               0
ALLEN               0
WARD                0
JONES               0
MARTIN              0
BLAKE               0
CLARK               0
SCOTT               0
KING                0
TURNER              0
ADAMS               0
JAMES               0
FORD                0
MILLER              0

14 rows selected.

Let's assume that we have a table that looks like this:

SQL> select * from empssn;

     EMPNO SSN
---------- -----------
      7369 000-00-010
      7499 000-00-011
      7521 000-00-012
      7566 000-00-013
      7654 000-00-014
      7698 000-00-015
      7782 000-00-016
      7788 000-00-017
      7839 000-00-018
      7844 000-00-019
      7876 000-00-020
      7900 000-00-021
      7902 000-00-022
      7934 000-00-023

14 rows selected.

Let's redact the SSN. In this case we can use a pre-defined function, which makes the redaction process quite easy:

connect scott/password

BEGIN 

 DBMS_REDACT.DROP_POLICY(
   object_schema    => 'SCOTT'
,  object_name      => 'EMPSSN' 

,  policy_name      => 'MASK_SSN'

);

end;

/

begin 

DBMS_REDACT.ADD_POLICY(
   object_schema    => 'SCOTT'
,  object_name      => 'EMPSSN'
,  column_name      => 'SSN'
,  policy_name      => 'MASK_SSN'

,  function_type       => DBMS_REDACT.PARTIAL
,  function_parameters => DBMS_REDACT.REDACT_US_SSN_F5

, expression => '1=1' ); 

end; 

/

Note here that we have added a function_parameters parameter. The function_type is

dbms_redact.partial. The function_parameters parameter provides a way for us to send various

parameters to the function that indicate how the redaction should occur.

In this case, we used the REDACT_US_SSN_F5 shortcut that is provided with the 

DBMS_REDACT package. There are a number of different shortcuts available to use, 

so you don't need to constantly be customizing your redaction patterns.

If there is a need then you can customize the redaction pattern fairly easily. 

Much more detailed information on this feature can be found in the 

Oracle Database 12c Release 1 Advanced Security Administrators Guide 

and the  

Oracle Database 11g Release 2 Advanced Security Administrators Guide.

Concluding the last example, by connecting to the testing schema, we can now see that the SSN column is redacted:

connect testing/password

  1* select * from scott.empssn
SQL> /

     EMPNO SSN
---------- -----------
      7369 XXX-XX-010
      7499 XXX-XX-011
      7521 XXX-XX-012
      7566 XXX-XX-013
      7654 XXX-XX-014
      7698 XXX-XX-015
      7782 XXX-XX-016
      7788 XXX-XX-017
      7839 XXX-XX-018
      7844 XXX-XX-019
      7876 XXX-XX-020
      7900 XXX-XX-021
      7902 XXX-XX-022
      7934 XXX-XX-023

14 rows selected.

Pretty cool stuff.

Remember, this data isn't changed in the database. So when you export it or back it up, the data is backed up in it's original version. The redaction itself is only taking place as the rows are processed and returned to the user.

Edits: Content and format.