Redacting data from the Oracle Database

Redacting data from the Oracle Database

Published on: Category: IT development and operations

Oracle Data Redaction is a new feature in Oracle Database 12c. Redaction is the process of masking information from the database to applications. The new feature provides methods to mask sensitive data that is returned from queries.

Oracle Data Redaction uses the new package DBMS_REDACT that was introduced in Oracle database version 12c. This functionality was also backported to release 11.2.0.4. The package is used to define policies that control what data is shown to the application users.

For each policy you define:

  • what table or column needs to be redacted
  • under what conditions data needs to be redacted
  • in what way the data needs to be redacted

This can be completely configured with PL/SQL code.

Types of redaction

Here are some examples of the different types of redaction:

Type of redaction
Data in the database
Displayed redacted values
Full
02-FEB-26 / John Smith / 220.90
01-JAN-01 / xxxxxxxxxxxxxxx / 0.00
Partial
1233-1233-2344-3458 / 02-FEB-26
0000-0000-0000-3458 / 01-JAN-26
Regular expression
john.smith@gmail.com
xxxxxx@gmail.com
Random
1233-1233-2344-3458 / 02-FEB-26
8375-8723-8927-8723 / 17-DEC-11

Setup

To define a policy you need to have execute privileges on the DBMS_REDACT package. This package is used to add, alter or drop policies. These are the parameters for the DBMS_REDACT.ADD_POLICY procedure:

  1. PROCEDURE ADD_POLICY
  2. Argument Name TYPE IN/OUT DEFAULT?
  3. ------------------------------ ----------------------- ------ --------
  4. OBJECT_SCHEMA VARCHAR2 IN DEFAULT
  5. OBJECT_NAME VARCHAR2 IN
  6. POLICY_NAME VARCHAR2 IN
  7. POLICY_DESCRIPTION VARCHAR2 IN DEFAULT
  8. COLUMN_NAME VARCHAR2 IN DEFAULT
  9. COLUMN_DESCRIPTION VARCHAR2 IN DEFAULT
  10. FUNCTION_TYPE BINARY_INTEGER IN DEFAULT
  11. FUNCTION_PARAMETERS VARCHAR2 IN DEFAULT
  12. EXPRESSION VARCHAR2 IN
  13. ENABLE BOOLEAN IN DEFAULT
  14. REGEXP_PATTERN VARCHAR2 IN DEFAULT
  15. REGEXP_REPLACE_STRING VARCHAR2 IN DEFAULT
  16. REGEXP_POSITION BINARY_INTEGER IN DEFAULT
  17. REGEXP_OCCURRENCE BINARY_INTEGER IN DEFAULT
  18. REGEXP_MATCH_PARAMETER VARCHAR2 IN DEFAULT

Exceptions

If you have the EXEMPT REDACTION POLICY system privilege, data is never redacted.  Redaction is also never used in the following situations:

  • connections as SYS
  • database replication
  • backup and restore
  • export and import

Test setup

For testing we’ve created a table, and inserted some data:

  1. CREATE TABLE CUSTOMERS
  2. (cust_id NUMBER NOT NULL
  3. ,name varchar2(20)
  4. ,credit_card NUMBER(16)
  5. ,exp_date DATE
  6. ,creditlimit NUMBER(6,2)
  7. ,emailadr varchar2(20)
  8. )
  9. /
  10.  
  11. INSERT INTO CUSTOMERS
  12. VALUES (8714,'John Smith' ,6283723982745971,'01-FEB-2017',49.50,'john.smith@gmail.com');
  13. INSERT INTO CUSTOMERS
  14. VALUES (8743,'Paul McCartney',8995984092832971,'01-APR-2017',150.00,'paul@oracle.com');
  15. INSERT INTO CUSTOMERS
  16. VALUES (8345,'George Lennon' ,9345945634845567,'01-FEB-2017',19.99,'glennon@qualogy.com');
  17. INSERT INTO CUSTOMERS
  18. VALUES (9019,'Ricky Star' ,2383834982340341,'01-JUN-2017',100.00,'star@aol.com');

This is how the data is displayed if no redaction is used:

  1. ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
  2. COLUMN credit_card format 0000000000000000
  3. COLUMN EMAILADR format a20
  4.  
  5. SELECT * FROM CUSTOMERS
  6. /
  7.  
  8. CUST_ID NAME CREDIT_CARD EXP_DATE CREDITLIMIT EMAILADR
  9. ---------- -------------------- ----------------- ----------- ----------- --------------------
  10. 8714 John Smith 6283723982745971 01-FEB-2017 49.5 john.smith@gmail.com
  11. 8743 Paul McCartney 8995984092832971 01-APR-2017 150 paul@oracle.com
  12. 8345 George Lennon 9345945634845567 01-FEB-2017 19.99 glennon@qualogy.com
  13. 9019 Ricky Star 2383834982340341 01-JUN-2017 100 star@aol.com

Different types of redaction

Within the policy definition we set the FUNCTION_TYPE that defines what kind of redaction is used:

  • DBMS_REDACT.NONE
  • DBMS_REDACT.FULL
  • DBMS_REDACT.PARTIAL
  • DBMS_REDACT.RANDOM
  • DBMS_REDACT.REGEXP

Full redaction

The most basic form is the full redaction. Here we define a policy that hides the values from the credit card column.

The policy is defined by a user that has execute privileges on the DBMS_REDACT package, like the SYSTEM user. All queries are executed by the schema-owner TEST_REDACT.

In this example the creditcard data is always redacted, because the expression ‘1=1’ is always true.

  1. BEGIN
  2. DBMS_REDACT.ADD_POLICY (
  3. object_schema => 'TEST_REDACT',
  4. object_name => 'CUSTOMERS',
  5. column_name => 'CREDIT_CARD',
  6. policy_name => 'REDACT_CARD_POLICY',
  7. function_type => DBMS_REDACT.FULL,
  8. expression => '1=1'
  9. );
  10. END;
  11. /
  12.  
  13.  
  14. CUST_ID NAME CREDIT_CARD EXP_DATE CREDITLIMIT EMAILADR
  15. ---------- -------------------- ----------------- --------- ----------- --------------------
  16. 8714 John Smith 0000000000000000 01-FEB-17 49.5 john.smith@gmail.com
  17. 8743 Paul McCartney 0000000000000000 01-APR-17 150 paul@oracle.com
  18. 8345 George Lennon 0000000000000000 01-FEB-17 19.99 glennon@qualogy.com
  19. 9019 Ricky Star 0000000000000000 01-JUN-17 100 star@aol.com

Partial redaction

Here we mask position 1 to 12 of the credit card number with a zero:

  1. BEGIN
  2. DBMS_REDACT.ADD_POLICY (
  3. object_schema => 'TEST_REDACT',
  4. object_name => 'CUSTOMERS',
  5. column_name => 'CREDIT_CARD',
  6. policy_name => 'REDACT_CARD_POLICY',
  7. function_type => DBMS_REDACT.PARTIAL,
  8. function_parameters => '0,1,12',
  9. expression => '1=1'
  10. );
  11. END;
  12. /
  13.  
  14. CUST_ID NAME CREDIT_CARD EXP_DATE CREDITLIMIT EMAILADR
  15. ---------- -------------------- ----------------- --------- ----------- --------------------
  16. 8714 John Smith 0000000000005971 01-FEB-17 49.5 john.smith@gmail.com
  17. 8743 Paul McCartney 0000000000002971 01-APR-17 150 paul@oracle.com
  18. 8345 George Lennon 0000000000005567 01-FEB-17 19.99 glennon@qualogy.com
  19. 9019 Ricky Star 0000000000000341 01-JUN-17 100 star@aol.com

Partial redaction can also be done on a date. Here we keep the year, but hide the rest of the date:

  1. BEGIN
  2. DBMS_REDACT.ADD_POLICY (
  3. object_schema => 'TEST_REDACT',
  4. object_name => 'CUSTOMERS',
  5. column_name => 'EXP_DATE',
  6. policy_name => 'REDACT_CARD_POLICY',
  7. function_type => DBMS_REDACT.PARTIAL,
  8. function_parameters => 'm1d1Y',
  9. expression => '1=1'
  10. );
  11. END;
  12. /
  13.  
  14. CUST_ID NAME CREDIT_CARD EXP_DATE CREDITLIMIT EMAILADR
  15. ---------- -------------------- ----------------- --------- ----------- --------------------
  16. 8714 John Smith 6283723982745971 01-JAN-17 49.5 john.smith@gmail.com
  17. 8743 Paul McCartney 8995984092832971 01-JAN-17 150 paul@oracle.com
  18. 8345 George Lennon 9345945634845567 01-JAN-17 19.99 glennon@qualogy.com
  19. 9019 Ricky Star 2383834982340341 01-JAN-17 100 star@aol.com

Random redaction

With random redaction the information is replaced with other random data:

  1. BEGIN
  2. DBMS_REDACT.ADD_POLICY (
  3. object_schema => 'TEST_REDACT',
  4. object_name => 'CUSTOMERS',
  5. column_name => 'CREDIT_CARD',
  6. policy_name => 'REDACT_CARD_POLICY',
  7. function_type => DBMS_REDACT.RANDOM,
  8. expression => '1=1'
  9. );
  10. END;
  11. /
  12.  
  13. CUST_ID NAME CREDIT_CARD EXP_DATE CREDITLIMIT EMAILADR
  14. ---------- -------------------- ----------------- --------- ----------- --------------------
  15. 8714 John Smith 1860716071681409 01-FEB-17 49.5 john.smith@gmail.com
  16. 8743 Paul McCartney 8744124307922429 01-APR-17 150 paul@oracle.com
  17. 8345 George Lennon 5901451110955182 01-FEB-17 19.99 glennon@qualogy.com
  18. 9019 Ricky Star 1901737681759520 01-JUN-17 100 star@aol.com

Redaction using regular expressions

With regular expression we can redact the data based on pattern matching. There are predefined patterns for a number of situations, like email addresses and IP addresses. Here we hide the beginning of the email address:

  1. BEGIN
  2. DBMS_REDACT.ADD_POLICY (
  3. object_schema => 'TEST_REDACT',
  4. object_name => 'CUSTOMERS',
  5. column_name => 'EMAILADR',
  6. policy_name => 'REDACT_CARD_POLICY',
  7. function_type => DBMS_REDACT.REGEXP,
  8. regexp_pattern => DBMS_REDACT.RE_PATTERN_EMAIL_ADDRESS,
  9. regexp_replace_string => DBMS_REDACT.RE_REDACT_EMAIL_NAME,
  10. regexp_position => DBMS_REDACT.RE_BEGINNING,
  11. regexp_occurrence => DBMS_REDACT.RE_ALL,
  12. expression => '1=1'
  13. );
  14. END;
  15. /
  16.  
  17. CUST_ID NAME CREDIT_CARD EXP_DATE CREDITLIMIT EMAILADR
  18. ---------- -------------------- ----------------- --------- ----------- --------------------
  19. 8714 John Smith 6283723982745971 01-FEB-17 49.5 xxxx@gmail.com
  20. 8743 Paul McCartney 8995984092832971 01-APR-17 150 xxxx@oracle.com
  21. 8345 George Lennon 9345945634845567 01-FEB-17 19.99 xxxx@qualogy.com
  22. 9019 Ricky Star 2383834982340341 01-JUN-17 100 xxxx@aol.com

Expression

In the examples above the parameter value for expression was allways ‘1=1’, so the data is always redacted.

The packages gives you the option to redact the data based on the situation. A common way to do this is to use the SYSCONTEXT function, which gives you information about the current session. In this way you can redact your data based on the user, the current role or the used IP address.

To redact data only for user JOHN you can add this expression to the profile:

  1. expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''JOHN'''

Data dictionary views

There are some views that can be queried to display information about the defined policies:

  1. SELECT OBJECT_OWNER
  2. , OBJECT_NAME
  3. , POLICY_NAME
  4. , EXPRESSION
  5. FROM REDACTION_POLICIES
  6. /
  7.  
  8. OBJECT_OWNER OBJECT_NAME POLICY_NAME EXPRESSION
  9. ------------ ------------ -------------------- ------------
  10. TEST_REDACT CUSTOMERS REDACT_CARD_POLICY 1=1
  11.  
  12.  
  13. SELECT OBJECT_OWNER
  14. , OBJECT_NAME
  15. , COLUMN_NAME
  16. , FUNCTION_TYPE
  17. , FUNCTION_PARAMETERS
  18. FROM REDACTION_COLUMNS
  19. /
  20.  
  21.  
  22. OBJECT_OWNER OBJECT_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS
  23. ------------ ------------ ------------ --------------------------- --------------------
  24. TEST_REDACT CUSTOMERS CREDIT_CARD PARTIAL REDACTION 0,1,12

Conclusion

The Oracle Data Redaction feature makes it possible to hide sensitive information. The functionality only changes the way the data is displayed, there are no changes in the storing of the data.

With the DBMS_REDACT package the redaction policies can defined in a very flexible way.

More information

  • A full description of the possibilities of the profiles can be found here

  • Click here for a complete list of possible values for the USERENV parameters see the Oracle documentation

Bastiaan Bak
About the author Bastiaan Bak

DBA with over 15 years of experience. Experience in various branches, with several modules. Including: Oracle database, Oracle RAC, Oracle EBS and PL/SQL.

More posts by Bastiaan Bak
Comments (4)
  1. om 10:10

    Hi,
    How can we add multiple numbers of users in following:-
    expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') IN (SELECT USERNAME FROM DBA_USERS)'

    1. om 12:12

      Hi Shailesh

      You use "(SELECT USERNAME FROM DBA_USERS)" , so you want redaction for all users ?
      Then you could use expression => '1=1'

      A more realistic scenario would be to add redaction for users who don't have a specific role.
      For that you can use something like :
      expression => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''MGR'') = ''FALSE''');

  2. om 09:09

    Hi!
    Would it be possible to mask only part of the returned rows? I mean, for example, those for people in some departments are masked and some other are shown, depending on a condition…
    Thank you!

  3. om 03:03

    I need to redact the below users.
    is there any option to use the 'IN ' keyword in the expression parameter in dbms_redact package?
    list of users: ABC,XYZ,MNK
    Expression =>'SYS_CONTEXT(''USERENV","SESSION_USERS") IN ("ABC","XYZ","MNK");

Reply