Tag: oracle 12c DBMS_REDACT

Oracle 12c Database Data Redaction – DBMS_REDACT

arcsdegeo4 Comments

With Oracle Database 12c, Oracle has introduced a new PL/SQL package called DBMS_REDACT to support data masking or data redaction. This new feature is also back ported to Oracle version 11.2.0.4.

DBMS_REDACT Package has the following procedures that perform different actions:

dbms_redact-proc

I will now explore it with an example:

Querying the original table:

SQL> select * from HR.JOBS_TEMP ;

jobs_temp-table-data

Execute the following as SYS:

begin
       dbms_redact.add_policy(
       object_schema => ‘HR’,
       object_name   => ‘JOBS_TEMP’,
       column_name   => ‘MIN_SALARY’,
       policy_name   => ‘redect_jobs_min_salary’,
       function_type => DBMS_REDACT.FULL,
       expression    => ‘1=1’);
  end;
/ 

Re-executing the query  with an account that has “SELECT” permission on the table will illustrate “FULL REDACTION” of the column values:

jobs_temp-table-redact

To disable a policy:

BEGIN

  DBMS_REDACT.DISABLE_POLICY (

    object_schema  => ‘HR’,

    object_name    => ‘JOBS_TEMP’,

    policy_name    => ‘redect_jobs_min_salary’);

END;

/

The following query will list the current configured redaction policy:

SQL> select * from REDACTION_POLICIES;

query-to-list-redact-policies

The following query will list currently redacted columns:

SQL> select * from REDACTION_COLUMNS;

query-to-list-redact-columns

The following policy will mask the data for un-authorized users, so non-HR users won’t be able to see the maximum salary using expression statement:

BEGIN

 DBMS_REDACT.ADD_POLICY(

   object_schema   => ‘HR’,

   object_name     => ‘JOBS_TEMP’,

   column_name     => ‘MAX_SALARY’,

   policy_name     => ‘JOBS_TEMP_MAX_SAL’,

   function_type   => DBMS_REDACT.FULL,

   expression      => ‘SYS_CONTEXT(”USERENV”, ”SESSION_USER”)!= ”HR”’);

END;

/

Important remark: only 1 policy can be applied on a table or a view. Trying to create another policy on the same table will result with the following errors:

ORA-28069: A data redaction policy already exists on this object.

ORA-06512: at “SYS.DBMS_REDACT_INT”, line 3

ORA-06512: at “SYS.DBMS_REDACT”, line 42

ORA-06512: at line 2

To add other columns to the current policy, use DBMS_REDACT.ALTER_POLICY and add DBMS_REDACT.ADD_COLUMN.

of course redacting data in a column won’t provide protection against “SYS” account , or user granted “DBA” role from viewing the real data.