Exadata and iPhone

I never thought one day I will find a similarity between Oracle Exadata and iPhone. But it was before Oracle announced X4-2C, the colorful Exadata:)

X4 2C


However, I don't think that X4-2C is cheaper as iPhone 5C is, since it's a limited edition...

Data Redaction in Oracle 12c and 11gR2

Data Redaction is one of the new features of 12c actually, and also it's become available in 11gR2 with 11.2.0.4. Data Redaction is in Advanced Security option of enterprise edition.

What Data Redaction does is basically masking the data on the fly based on the type and expression given. Let's do a demonstration:

First thing to do is to create a policy. Policies are created on tables and a table can have only one policy. If you try to add a policy to table with a policy, you get ORA-28069. Policy can be defined for only a single column at a time but you can add columns to the policy by altering it.

begin
    dbms_redact.add_policy (
        object_schema       => 'DEMO',
        object_name         => 'CUSTOMERS',
        column_name         => 'CNAME',        
        policy_name         => 'customers_pol',
        function_type       => DBMS_REDACT.REGEXP,
        regexp_pattern      => '(\S{3})(\S+)',
        regexp_replace_string   => '\1***',  
        expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS'''
    );
end;
/

Above command creates a policy on table CUSTOMERS in schema DEMO and it masks column CNAME by using regular expressions. Masked values will contain first 3 letters of the name (each name if customer has a middle name) and 3 "*"s.

There are 6 types of redaction; full, partial, regexp, random and none. You can find detail information in Oracle documentation. Please not the expression parameter. What we're saying here is apply this policy to users who are not sys. By default polices applied against users except sys and object owner. So if you also want table owner gets masked values or you want to redact data based on application, such an expression must be used. Expression is a mandatory parameter, you can set it as '1=1' if you have no rule to apply.

begin
    dbms_redact.alter_policy (
        object_schema       => 'DEMO',
        object_name         => 'CUSTOMERS',        
        policy_name         => 'customers_pol',
        action              => DBMS_REDACT.ADD_COLUMN,
        column_name         => 'DOB',
        function_type       => DBMS_REDACT.PARTIAL,
        function_parameters => DBMS_REDACT.REDACT_DATE_EPOCH,
        expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS'''        
    );
end;
/

By altering the policy we've created, we added another column of the table to be masked. Here we used built-in redaction function. This function set all date values to 01-Jan-1970. There are couple of more pre-defined functions such for SSN, e-mail zip code.

begin
    dbms_redact.alter_policy (
        object_schema       => 'DEMO',
        object_name         => 'CUSTOMERS',        
        policy_name         => 'customers_pol',
        action              => DBMS_REDACT.ADD_COLUMN,
        column_name         => 'CID',
        function_type       => DBMS_REDACT.FULL, 
        expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS'''        
    );
end;
/

This time we add CID column to the policy and redacted it fully. So masked values will be displayed as 0. To change a columns redaction type, alter policy again by setting altering action to MODIFY_COLUMN:

begin
    dbms_redact.alter_policy (
        object_schema       => 'DEMO',
        object_name         => 'CUSTOMERS',        
        policy_name         => 'customers_pol',
        action              => DBMS_REDACT.MODIFY_COLUMN,
        column_name         => 'CID',
        function_type       => DBMS_REDACT.RANDOM, 
        expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS'''        
    );
end;
/

Now, we set it to use random masking, random values will be generated for the column. Redacted query output is as below at the end:

SQL> select cid, cname, dob from demo.customers;

CID         CNAME DOB
-----------------------------------------
1311680984   ABD*** AYD***   01-JAN-70
102691765    AHM*** YIL*** 01-JAN-70
819107024  ARI*** DEM***   01-JAN-70
7285271581  AYD*** TUR***   01-JAN-70
22688323660  AZM*** SEV***   01-JAN-70
7508336149  HAC*** ELM***   01-JAN-70
46158355970  HAL*** HAT***   01-JAN-70

Finally, if you need to drop a policy:

begin
    dbms_redact.drop_policy(
        object_schema     => 'DEMO',
        object_name       => 'CUSTOMERS',
        policy_name       => 'customers_pol'
    );        
end;
/

Data redaction is not a complicated way of defining security policies however what I see as a downside is one-to-one relationship between columns and policies: It is not allowed to add another policy/expression for a column, you get ORA-28060 error. So what you need is to create a rule base which has different masking types for different roles on the very same table, you need another tool. Otherwise you have practical data masking tool.