Oracle Database Security Feature in 11g “REDACTION”

Oracle has introduced redaction feature in database 12c and added later to 11g in 11.2.0.4 patchset.

Redaction feature is available with dbms_redact.

Some examples:

a. If you want to redact credit card data in table:

Define a policy with functions of your choice. You can use FULL redaction or Partial or Regular Expression mode. This example is for partial. You should know the max length of the column for correct input/output parameters.

select max(length(COLNAME)) from <schema>.<table_name>;

Now, lets check the userenv for session_user.

SELECT SYS_CONTEXT (‘userenv’, ‘SESSION_USER’)    FROM DUAL;

So, go ahead and define a policy for a table which has max length of 19 char and column defined as varchar..

begin
dbms_redact.add_policy(
object_schema => ‘SCOTT’,
object_name => ‘TABLENAME’,
column_name => ‘COLNAME’,
policy_name => ‘CREDITCARD_MASKIT’,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => ‘VVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVV,*,1,10’,
expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”)
= ”SCOTT”’);
end;

 

Check it:

select colname from scott.tablename;

You will get out put as * for first 10 digits and after 10 digits the orig. values of data is displayed.

**********714894028

—————————————————————————————————————–

b. If you want to redact a number with length as 10.

I want to write a policy, the first 5 numbers will be replaced with digit 9 and rest all ignored in my select query.

begin
dbms_redact.add_policy(
object_schema => ‘TEST’,
object_name => ‘TABLEA’,
column_name => ‘NUMBERA’,
policy_name => ‘MASK_PARTIAL_A’,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => ‘9,1,5’,
expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”)
= ”TEST”’);
end;
/

You can query below dictionary views to see the redact column and function defined in a database.

select * from redaction_policies;

select * from redaction_columns;

——————————————————————————————-

You can alter, drop policies.

begin

DBMS_REDACT.DROP_POLICY (
object_schema => ‘TEST’,
object_name => ‘TABLEA’,
policy_name => ‘MASK_PARTIAL_A’);

end;

References:

Some examples of implementing Data Redaction using DBMS_REDACT (Doc ID 1588270.1)
sys_context: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm
dbms_redact: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_redact.htm#ARPLS73812