Oracle Database 12c: Data Redaction (Hiding sensitive data)

What is Data Redaction

Data Redaction is the process of masking (redacting) data (more particularly sensitive data) for an application. Oracle Data Redaction functionality provides methods to mask (redact) data that is returned from user SELECT queries

Data Redaction in Oracle Database

Prior to Oracle Database 12c, data masking or column masking was provided by means of VPD (Virtual Private Database) Policies.

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

DBMS_REDACT provides a greater level of data redaction and control over sensitive data in the database when compared to the existing VPD policies.

How it works?

Point to mention/remember: Data Redaction only works for SELECT queries performed against the redacted data. Data Redaction in Oracle Database 12c is defined at column level.

Data redaction in Oracle Database works in real time, which means data is not stored in redacted format rather it is presented in a redacted format on being queried based on the redaction policies defined on the data. We define data redaction policy using DBMS_REDACT package and when a user queries the data, the policy is applied against the querying user based on the user’s SYS_CONTEXT values.

Again to be particular, the entire data set (TABLE or VIEW) is not redacted. Only the rows which are queried by the user are redacted based on the user’s SYS_CONTEXT and redaction policies.

How to set up Data Redaction

To setup data redaction, following process can be followed.

1. Identify the Table or View and its Owner to which the data to be redacted belongs to.
2. Identify the columns in the table or view which needs to be redacted.
3. Add data redaction policies for the set of identified columns using DBMS_REDACT.ADD_POLICY or DBMS_REDACT.ALTER_POLICY procedure

Add a Policy using DBMS_REDACT

We define a data redact policy using the ADD_POLICY procedure from the DBMS_REDACT database package. The ADD_POLICY procedure provides different type of data redaction methods that can be applied to a data set.

While adding a data redact policy, we address the following components at the lowest level.

1. Object to be redacted: The OBJECT_SCHEMA, OBJECT_NAME and COLUMN_NAME defines the data to be redacted.
2. Policy Identity: We must have a way to identify the defined data redact policy. The POLICY_NAME parameter gives an identity to the policy
3. What type of Redaction: The FUNCTION_TYPE parameter defines the type of redaction Oracle should perform. Following are the values allowed for the FUNCTION_TYPE

DBMS_REDACT.NONE: Data is not to be redacted

DBMS_REDACT.FULL (default): Column Data is to be redacted to a fixed values.

DBMS_REDACT.PARTIAL: Column Data is to be partially redacted.

DBMS_REDACT.RANDOM: Random redaction, each query results in a different random value for the column data.

DBMS_REDACT.REGEXP: Regular expression based redaction

Based on the FUNCTION_TYPE, we may need to additionally specify the FUNCTION_PARAMETERS or different REGEXP parameters.

4. When to redact the data: The EXPRESSION parameter defines the event or time at which data redaction needs to be performed. An expression of “1=1” means the redaction will always take place. Alternatively, we can define situational expressions using SYS_CONTEXT function

The ADD_POLICY procedure has the following syntax.

   object_schema                IN    VARCHAR2 := NULL,
   object_name                  IN    VARCHAR2,
   policy_name                  IN    VARCHAR2,
   column_name                  IN    VARCHAR2 := NULL,
   function_type                IN    BINARY_INTEGER := DBMS_REDACT.FULL,
   function_parameters          IN    VARCHAR2 := NULL,
   expression                   IN    VARCHAR2,
   enable                       IN    BOOLEAN := TRUE,
   regexp_pattern               IN    VARCHAR2 := NULL,
   regexp_replace_string        IN    VARCHAR2 := NULL,
   regexp_position              IN    BINARY_INTEGER := 1,
   regexp_occurrence            IN    BINARY_INTEGER := 0,
   regexp_match_parameter       IN    VARCHAR2 := NULL,
   policy_description           IN    VARCHAR2 := NULL,
   column_description           IN    VARCHAR2 := NULL);

One more point to mention is that only a single data redact policy can be defined against a TABLE or VIEW using DBMS_REDACT package. Trying to add a new policy for a TABLE or VIEW which already has a data redact policy defined on it would result in to following error

ERROR at line 1:
ORA-28069: A data redaction policy already exists on this object

Keeping this in mind, if we need to redact multiple columns in a single table or view; we should create the first data redact policy using DBMS_REDACT.ADD_POLICY and then add the subsequent set of columns that needs to be redacted with DBMS_REDACT.ALTER_POLICY by specifying the action DBMS_REDACT.ADD_COLUMN, which would update the existing data redact policy with the redaction properties of the given column.


To be able to create a data redact policy, the user defining the policy must have the EXECUTE privilege on DBMS_REDACT package in order to utilize various data redaction procedures.


If the user querying the redacted data has EXEMPT REDACTION POLICY system privilege, redaction policies would not be enforced on that user’s CONTEXT.


Lets say, I have a table that holds credit card information and I want to redact the card number in that table. Based on the assumption, I am using the following database object model for my demonstration.

OBJECT_SCHEMA: test_redact
OBJECT_TABLE: credit_card_info
COLUMN_NAME: card_no

Lets create the table and load some data for our demonstration.

--- Creating Table for demonstration
SQL> create table credit_card_info
  2  (
  3  cust_id number(10) GENERATED ALWAYS AS IDENTITY START WITH 1000,
  4  enroll_date date NOT NULL,
  5  card_no number(16) NOT NULL,
  6  exp_date date NOT NULL
  7  );

Table created.

--- Performing sample data load for demonstration
SQL> insert into credit_card_info(enroll_date,card_no,exp_date) values (sysdate,1285145836589848,TRUNC(ADD_MONTHS(SYSDATE,36)));

1 row created.

SQL> insert into credit_card_info(enroll_date,card_no,exp_date) values (sysdate,7844896487984154,TRUNC(ADD_MONTHS(SYSDATE,36)));

1 row created.

SQL> insert into credit_card_info(enroll_date,card_no,exp_date) values (sysdate,8554884663181228,TRUNC(ADD_MONTHS(SYSDATE,36)));

1 row created.

SQL> insert into credit_card_info(enroll_date,card_no,exp_date) values (sysdate,9487545796548985,TRUNC(ADD_MONTHS(SYSDATE,36)));

1 row created.

SQL> select * from credit_card_info;

---------- ----------- ------------------ -----------
      1001 08-NOV-2014   1285145836589848 08-NOV-2017
      1002 08-NOV-2014   7844896487984154 08-NOV-2017
      1003 08-NOV-2014   8554884663181228 08-NOV-2017
      1004 08-NOV-2014   9487545796548985 08-NOV-2017

At this point we have the data that we want to redact. Lets implement data redaction policies.

Full Data Redaction

Lets start with the default data redact policy which is the FULL data redaction.

I would add a data redaction policy to redact the CARD_NO column of CREDIT_CARD_INFO table. I will make use of the FUNCTION_TYPE called DBMS_REDACT.FULL to redact CARD_NO values to a static value, every time it is being queried using the data redact event EXPRESSION ‘1=1’.

To add a data redact policy, we must have EXECUTE privilege on DBMS_REDACT package. Lets add the policy using SYS user as it has the required privileges.

IMPORTANT: I am not granting EXECUTE privilege to the table owner TEST_REDACT as it would enable the user to add data redact policy on other user’s tables provided it has SELECT privilege on that user’s table, which would be again a security concern.

Therefore, it is recommended to avoid granting EXECUTE privilege on DBMS_REDACT to schema owners and rather use the privileged user accounts (SYS) to ADD, ALTER, DROP, ENABLE or DISABLE data redact policies

SQL> show user
SQL> select * from user_sys_privs where privilege='EXECUTE ANY PROCEDURE';

USERNAME   PRIVILEGE                      ADM COM
---------- ------------------------------ --- ---

---- Creating the data redact policy	  
  2  DBMS_REDACT.add_policy(
  3  object_schema => 'test_redact',
  4  object_name   => 'credit_card_info',
  5  column_name   => 'card_no',
  6  policy_name   => 'redact_card_no',
  7  function_type => DBMS_REDACT.full, 
  --- FULL data redact specifies redaction to static value
  8  expression    => '1=1'  
  --- This expression leads to data redaction for every SELECT query on the column
  9  );
 10  END;
 11  /

PL/SQL procedure successfully completed.

Lets query the data and see if it is redacted or not.

SQL> show user
USER is "TEST_REDACT" --- Object Owner

SQL> select * from credit_card_info;

---------- --------- ---------- ---------
      1001 08-NOV-14          0 08-NOV-17
      1002 08-NOV-14          0 08-NOV-17
      1003 08-NOV-14          0 08-NOV-17
      1004 08-NOV-14          0 08-NOV-17

Lets see if the redact works when other database users apart from the table owner query the table.

SQL> show user

SQL> select * from test_redact.credit_card_info where CUST_ID=1002;

---------- --------- ----------------- ---------
      1002 08-NOV-14                 0 08-NOV-17

As we can see the CARD_NO is now redacted to a static value 0 (zero) while presenting it to the user.

In FULL data redact policy, the column data gets redacted to a static value (by default 0). We can view default static values for FULL redaction policy by querying REDACTION_VALUES_FOR_TYPE_FULL view. This view lists all the default static redact values for all type of input data types.

For example, in our case the CARD_NO is of NUMBER data type, which got redacted to 0 because of default redact value as show below.



We can also change the default static values for FULL redaction using UPDATE_FULL_REDACTION_VALUES procedure of the DBMS_REDACT package.

UPDATE_FULL_REDACTION_VALUES procedure has the following syntax

   number_val       IN NUMBER                    :=  NULL,
   binfloat_val     IN BINARY_FLOAT              :=  NULL,
   bindouble_val    IN BINARY_DOUBLE             :=  NULL,
   char_val         IN CHAR                      :=  NULL,
   varchar_val      IN VARCHAR2                  :=  NULL,
   nchar_val        IN NCHAR                     :=  NULL,
   nvarchar_val     IN NVARCHAR2                 :=  NULL,
   date_val         IN DATE                      :=  NULL,
   ts_val           IN TIMESTAMP                 :=  NULL,
   tswtz_val        IN TIMESTAMP WITH TIME ZONE  :=  NULL,
   blob_val         IN BLOB                      :=  NULL,
   clob_val         IN CLOB                      :=  NULL,
   nclob_val        IN NCLOB                     NULL);

Partial Data Redaction

Lets say I want to mask the first 14 characters of the 16 digit card number instead of completely making the data. We can opt for PARTIAL data redact policy for this purpose.

Lets alter the existing data redact policy for CARD_NO using ALTER_POLICY procedure of DBMS_REDACT package from FULL to PARTIAL.

  2  DBMS_REDACT.alter_policy(  --- meant to alter an existing policy
  3  object_schema => 'test_redact',
  4  object_name   => 'credit_card_info',
  5  column_name   => 'card_no',
  6  policy_name   => 'redact_card_no',
  7  action        => DBMS_REDACT.modify_column, 
  --- action DBMS_REDACT.modify_column specifies that the column redaction type needs to be modified
  8  function_type => DBMS_REDACT.partial, 
  --- Here I am applying partial data redaction
  9  function_parameters => '9,1,14' 
  --- In function_parameters, 9 is the mask number, 1 is the starting index of column data
  --- and 14 is the number of digits to be masked
 10  );
 11  END;
 12  /

PL/SQL procedure successfully completed.

The full list of FUNCTION_PARAMETERS for PARTIAL redaction can be found here with respect to different input data types.

Lets see if the PARTIAL data redaction is working or not

SQL> select * from credit_card_info;

---------- --------- ------------------ ---------
      1001 08-NOV-14   9999999999999948 08-NOV-17
      1002 08-NOV-14   9999999999999954 08-NOV-17
      1003 08-NOV-14   9999999999999928 08-NOV-17
      1004 08-NOV-14   9999999999999985 08-NOV-17

As expected, in this partial redaction the first 14 digits in the CARD_NO are masked with the number 9.

Random Data Redaction

In RANDOM data redact policy, the column data would be redacted to a random value each time it is being queried. Lets alter our existing data redact policy from PARTIAL to RANDOM using ALTER_POLICY procedure of DBMS_REDACT package.

Lets also change the event or time of the data redact from every time (‘1=1’) to user’s SYS_CONEXT, where the policy would get applied to the users other than the table owner (TEST_REDACT).

--- Using ALTER_POLICY policy to change redaction type from PARTIAL to RANDOM
  2  DBMS_REDACT.alter_policy(
  3  object_schema => 'test_redact',
  4  object_name   => 'credit_card_info',
  5  column_name   => 'card_no',
  6  policy_name   => 'redact_card_no',
  7  action        => DBMS_REDACT.modify_column, 
  --- action is to modify data redact type
  8  function_type => DBMS_REDACT.random 
  --- changing redact from partial to random
  9  );
 10  END;
 11  /

PL/SQL procedure successfully completed.

--- Using ALTER_POLICY to change expression from every time to User's CONTEXT
  2   DBMS_REDACT.alter_policy(
  3   object_schema => 'test_redact',
  4   object_name   => 'credit_card_info',
  5   column_name   => 'card_no',
  6   policy_name   => 'redact_card_no',
  7   action        => DBMS_REDACT.modify_expression, 
  --- action is to change the expression (event or time of data redact)
  8   expression    => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''TEST_REDACT''' 
  --- changing data redact event to User's CONTEXT from '1=1'
  9   );
 10   END;
 11   /

PL/SQL procedure successfully completed.

At this point, we have a RANDOM data redact policy for the column CARD_NO in table CREDIT_CARD_INFO, which would be applied to all the users except the table owner (TEST_REDACT)

Now, lets query the data and see if the RANDOM data redaction is working.

---- Querying as the Table Owner
SQL> show user
SQL> select * from credit_card_info;

---------- --------- ------------------- ---------
      1001 08-NOV-14    1285145836589848 08-NOV-17
      1002 08-NOV-14    7844896487984154 08-NOV-17
      1003 08-NOV-14    8554884663181228 08-NOV-17
      1004 08-NOV-14    9487545796548985 08-NOV-17

---- connecting as a different user
SQL> conn test/test

SQL>  select  * from test_redact.credit_card_info;

---------- --------- ------------------- ---------
      1001 08-NOV-14    1231995313766388 08-NOV-17
      1002 08-NOV-14    1748178239644580 08-NOV-17
      1003 08-NOV-14    4752531294251194 08-NOV-17
      1004 08-NOV-14     384417886169193 08-NOV-17

SQL> /

---------- --------- ------------------- ---------
      1001 08-NOV-14     429610736468865 08-NOV-17
      1002 08-NOV-14    1053199418706825 08-NOV-17
      1003 08-NOV-14    1605352777706183 08-NOV-17
      1004 08-NOV-14    1234581368958941 08-NOV-17

As expected, each time the column values are redacted to a random value for the users except the table owner.

Data Redaction with REGEXP

Regular expression based data redaction works only for character data. In this type data redaction, we need to specify a regular expression REGEXP_PATTERN which will match the column data pattern and replace it with the REGEXP_REPLACE_STRING.

If the REGEXP_PATTERN doesn’t match with column data pattern, FULL data redaction DBMS_REDACT.FULL would be imposed by default on the data to avoid data exposure.

Lets add a column card_str of character type to our existing table credit_card_info. I am storing the string version of credit card number in this column.

SQL> alter table test_redact.credit_card_info add
  2  (
  3  card_str varchar2(20)
  4  );

Table altered.

Lets add a REGEXP data redact policy to redact credit card number string values. As mentioned earlier, when we are adding new columns in a existing data redaction policy; we need to use DBMS_REDACT.ALTER_POLICY with DBMS_REDACT.ADD_COLUMN action.

2 DBMS_REDACT.alter_policy(
3 object_schema          => 'test_redact', 
4 object_name            => 'credit_card_info', 
5 column_name            => 'card_str',
6 policy_name            => 'redact_card_no', 
7 action                 => DBMS_REDACT.add_column, 
-- Adding policy for a new column
8 function_type          => DBMS_REDACT.REGEXP, 
-- data redact policy is based on regular expression
9 regexp_pattern         => '[0-9]{4}[\-][0-9]{4}[\-][0-9]{4}[\-][0-9]{4}$', 
--- REGEXP match pattern
10 regexp_replace_string  => 'XXXX-XXXX-XXXX-XXXX', 
-- String to replace the match pattern
11 regexp_position        => 1, 
-- position in the column data where the REGEXP starts
12 regexp_occurrence      => 0, 
-- Number of occurrence of REGEXP in the column data 
13 regexp_match_parameter => 'i' 
-- Case Insensitive matching of data
14 );
15 END;
16 /
PL/SQL procedure successfully completed.

Lets see how the redacted data is displayed.

---------- --------- ------------------- --------- --------------------
      1001 08-NOV-14    1166980846368427 08-NOV-17 XXXX-XXXX-XXXX-XXXX
      1002 08-NOV-14    3627319026433572 08-NOV-17 XXXX-XXXX-XXXX-XXXX
      1003 08-NOV-14    5576643725215909 08-NOV-17 XXXX-XXXX-XXXX-XXXX
      1004 08-NOV-14     691916619577246 08-NOV-17 XXXX-XXXX-XXXX-XXXX

As expected CARD_STR column is redacted to the REGEXP_REPLACE_STRING as well as RANDOM policy is in place for CARD_NO as a part of existing policy.

We can also utilize predefined REGEXP_PATTERN and REGEXP_REPLACE_STRING for REGEXP based data redaction policy. For a detailed information please refer here

Vulnerability with DBMS_REDACT

Security expert “David Litchfield” has identified some major vulnerabilities with respect to data security while implementing data redaction using DBMS_REDACT package. He has published a complete analysis about these vulnerabilities in this article.


DBMS_REDACT is an excellent package to redact data in Oracle database. However, the vulnerabilities identified in the package by the security expert “David Litchfield” are major loopholes in the tool and needs to be addressed. If Oracle fix the identified loopholes in DBMS_REDACT package, it would definitely turned out to be an amazing tool for data redaction.


Creating a Regular Expression-Based Redaction Policy
Oracle Data Redaction is Broken

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed