Oracle Data Redaction (DBMS_REDACT)

Oracle Data Redaction enables you to mask (redact) data that is returned from queries issued by applications. You can redact column data by using one of the following methods:

• Full redaction
• Random redaction
• Partial redaction
• Regular expressions

Database version:  11.2.0.4.0 or later

Prepare environment

--Create user boris: create user boris identified by pass_123; grant create session to boris; --Create user app_user: create user app_user identified by pass_123; grant create session to app_user; grant create table to app_user; alter user app_user quota UNLIMITED on USERS; --Create test table in app_user schema: conn app_user/pass_123 create table test ( name varchar2(10), sal  number, ssn  varchar2(32)); --Populate table with sample data: insert into test values ('SMITH',50000,'200304050'); insert into test values ('CLARK',50000,'200304051'); insert into test values ('ADAMS',50000,'200304052'); insert into test values ('SIMS',50000,'200304053'); insert into test values ('FORD',50000,'200304054'); insert into test values ('MILLER',50000,'200304055'); commit; --Connect as sysdba grant select on app_user.test to boris; grant EXEMPT REDACTION POLICY to app_user; --exempt app_user

Full redaction(DBMS_REDACT.FULL)

--Connect as sysdba --Drop REDACT_SSN_POL policy BEGIN DBMS_REDACT.DROP_POLICY (   object_schema        => 'APP_USER',   object_name          => 'TEST',   policy_name          => 'REDACT_SSN_POL'); END; / --Create REDACT_SSN_POL policy BEGIN DBMS_REDACT.ADD_POLICY(   object_schema    => 'APP_USER',   object_name      => 'TEST',   column_name      => 'SSN',   policy_name      => 'REDACT_SSN_POL',   function_type    =>  DBMS_REDACT.FULL,   expression       => '1=1'); END; / --Test redaction policy as boris: conn boris/pass_123 select * from app_user.test; NAME              SAL         SSN -------------     -------    ------------- SMITH             50000 CLARK             50000 ADAMS             50000 SIMS              50000 FORD              50000 MILLER            50000 6 rows selected. --Test redaction policy as app_user: conn app_user/pass_123 select * from test; NAME              SAL         SSN ------------    ------     ------------ SMITH             50000    200304050 CLARK             50000    200304051 ADAMS             50000    200304052 SIMS              50000    200304053 FORD              50000    200304054 MILLER            50000    200304055 6 rows selected.

Random redaction(DBMS_REDACT.RANDOM)

--Connect as sysdba --Drop REDACT_SSN_POL policy BEGIN DBMS_REDACT.DROP_POLICY (   object_schema        => 'APP_USER',   object_name          => 'TEST',   policy_name          => 'REDACT_SSN_POL'); END; / --Create REDACT_SSN_POL policy BEGIN DBMS_REDACT.ADD_POLICY(   object_schema    => 'APP_USER',   object_name      => 'TEST',   column_name      => 'SSN',   policy_name      => 'REDACT_SSN_POL',   function_type    =>  DBMS_REDACT.RANDOM,   expression       => '1=1'); END; / --Test redaction policy as boris: conn boris/pass_123 select * from app_user.test; NAME              SAL      SSN -------------      -------  ------------- SMITH             50000    Z\cM2XO)b CLARK             50000    *4!K<%="2 ADAMS             50000    QhB:'HcNY SIMS              50000    5~(L#9f\= FORD              50000    5~(L#9f\= MILLER            50000    *RbD1t%[2 6 rows selected. --Test redaction policy as app_user: conn app_user/pass_123 select * from test; NAME              SAL         SSN ------------       ------     ------------ SMITH             50000      200304050 CLARK             50000      200304051 ADAMS             50000      200304052 SIMS              50000      200304053 FORD              50000      200304054 MILLER            50000      200304055 6 rows selected.

Partial redaction(DBMS_REDACT.PARTIAL)

--Connect as sysdba --Drop REDACT_SSN_POL policy BEGIN DBMS_REDACT.DROP_POLICY (   object_schema        => 'APP_USER',   object_name          => 'TEST',   policy_name          => 'REDACT_SSN_POL'); END; / --Create REDACT_SSN_POL policy to partially redact 1st 5 numbers in SSN BEGIN DBMS_REDACT.ADD_POLICY(   object_schema       => 'APP_USER',   object_name         => 'TEST',   column_name         => 'SSN',   policy_name         => 'REDACT_SSN_POL',   function_type       =>  DBMS_REDACT.PARTIAL,   function_parameters => 'VVVVVVVVV,VVVVVVVVV,X,1,5',   expression          => '1=1'); END; / --Test redaction policy as boris: conn boris/pass_123 select * from app_user.test; NAME              SAL         SSN -------------     -------   ------------- SMITH             50000    XXXXX4050 CLARK             50000    XXXXX4051 ADAMS             50000    XXXXX4052 SIMS              50000    XXXXX4053 FORD              50000    XXXXX4054 MILLER            50000    XXXXX4055 6 rows selected. --Test redaction policy as app_user: conn app_user/pass_123 select * from test; NAME              SAL         SSN ------------       ------   ------------ SMITH             50000    200304050 CLARK             50000    200304051 ADAMS             50000    200304052 SIMS              50000    200304053 FORD              50000    200304054 MILLER            50000    200304055 6 rows selected.

Regular expressions(DBMS_REDACT.REGEXP)

--Connect as sysdba --Drop REDACT_SSN_POL policy BEGIN DBMS_REDACT.DROP_POLICY (   object_schema        => 'APP_USER',   object_name          => 'TEST',   policy_name          => 'REDACT_SSN_POL'); END; / --Create REDACT_SSN_POL policy to show only 5 last digits of SSN BEGIN DBMS_REDACT.ADD_POLICY(   object_schema       => 'APP_USER',   object_name         => 'TEST',   column_name          =>  'SSN',   policy_name         => 'REDACT_SSN_POL',   function_type       =>  DBMS_REDACT.REGEXP,   regexp_pattern         =>  '\d+(\d{5})$',   regexp_replace_string => '****\1',   regexp_position       =>  DBMS_REDACT.RE_BEGINNING,   regexp_occurrence     =>  DBMS_REDACT.RE_ALL,   expression          =>  '1=1'); END; / --Test redaction policy as boris: conn boris/pass_123 select * from app_user.test; NAME              SAL         SSN -------------  -------    ------------- SMITH             50000    ****04050 CLARK             50000    ****04051 ADAMS             50000    ****04052 SIMS              50000    ****04053 FORD              50000    ****04054 MILLER            50000    ****04055 6 rows selected. --Test redaction policy as app_user: conn app_user/pass_123 select * from test; NAME              SAL         SSN ------------    ------     ------------ SMITH             50000    200304050 CLARK             50000    200304051 ADAMS             50000    200304052 SIMS              50000    200304053 FORD              50000    200304054 MILLER            50000    200304055 6 rows selected.

Applying the Redaction Policy Based on Database Role

For example, suppose you wanted only managers to be able to see the actual data.

expression  => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''MANAGER_ROLE'') = ''FALSE'''

Applying the Redaction Policy Based on User Environment

expression  => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''BORIS'''

The dbms_redact procedure introduces the following of ORA-error messages for data redaction.

ORA-28060 - A Data Redaction policy already exists on this column.

ORA-28061 - This object cannot have a Data Redaction policy defined on it.

ORA-28062 - The policy expression is too long.

ORA-28063 - The policy expression is empty.

ORA-28064 - The type of redaction function is not valid.

ORA-28066 - Invalid column column

ORA-28069 - A Data Redaction policy already exists on this object.

ORA-28073 - The column column_name has an unsupported datatype.

ORA-28074 - The field field_name of the masking parameters is not valid

ORA-28075 - The policy expression has unsupported functions

ORA-28076 - An attribute was not specified for SYS_SESSION_ROLES

ORA-28077 - The attribute specified (attribute) exceeds the maximum length

ORA-28078 - A regular expression parameter is missing or invalid

ORA-28082 - The parameter parameter is invalid (where the possible values are function_parameters, column_description , policy_name and policy_description )

ORA-28085 - The input and output lengths of the redaction do not match.

For more information see:

Oracle Database Advanced Security Guide 12c Release 1 (12.1)

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.