Redacting data from the Oracle Database
Terug naar overzicht

Redacting data from the Oracle Database

Gepubliceerd: Categorie: Beheerdiensten

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
Over auteur Bastiaan Bak

DBA met ruim 15 jaar ervaring. Ervaring in verschillende branches, met verschillende modules. Onder meer: Oracle Database, Oracle RAC, Oracle EBS en PL/SQL.

Meer posts van Bastiaan Bak
Reacties (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)'

    Reageer
    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!

    Reageer
  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");

    Reageer
Reactie plaatsen