8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Data Redaction (DBMS_REDACT) in Oracle Database 12c Release 1 (12.1)

Oracle 10g gave us to ability to perform column masking to prevent sensitive data from being displayed by applications. In Oracle 12c, and back-ported to 11.2.0.4, the data redaction feature uses the DBMS_REDACT package to define redaction policies that give a greater level of control and protection over sensitive data. The documentation for this feature is quite extensive, so this article will provide a simple overview of the functionality.

Redaction is part of Oracle Advanced Security, which is a separately licensed Enterprise Edition option.

Related articles.

Setup

We need to make sure the test user has access to the DBMS_REDACT package.

CONN sys@pdb1 AS SYSDBA
GRANT EXECUTE ON sys.dbms_redact TO test;

The example code in this article requires the following test table.

CONN test/test@pdb1

DROP TABLE payment_details PURGE;

CREATE TABLE payment_details (
  id          NUMBER       NOT NULL,
  customer_id NUMBER       NOT NULL,
  card_no     NUMBER       NOT NULL,
  card_string VARCHAR2(19) NOT NULL,
  expiry_date DATE         NOT NULL,
  sec_code    NUMBER       NOT NULL,
  valid_date  DATE,
  CONSTRAINT payment_details_pk PRIMARY KEY (id)
);

INSERT INTO payment_details VALUES (1, 4000, 1234123412341234, '1234-1234-1234-1234', TRUNC(ADD_MONTHS(SYSDATE,12)), 123, NULL);
INSERT INTO payment_details VALUES (2, 4001, 2345234523452345, '2345-2345-2345-2345', TRUNC(ADD_MONTHS(SYSDATE,12)), 234, NULL);
INSERT INTO payment_details VALUES (3, 4002, 3456345634563456, '3456-3456-3456-3456', TRUNC(ADD_MONTHS(SYSDATE,12)), 345, NULL);
INSERT INTO payment_details VALUES (4, 4003, 4567456745674567, '4567-4567-4567-4567', TRUNC(ADD_MONTHS(SYSDATE,12)), 456, NULL);
INSERT INTO payment_details VALUES (5, 4004, 5678567856785678, '5678-5678-5678-5678', TRUNC(ADD_MONTHS(SYSDATE,12)), 567, NULL);
COMMIT;

ALTER SESSION SET nls_date_format='DD-MON-YYYY';

COLUMN card_no FORMAT 9999999999999999

SELECT *
FROM   payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1234123412341234 1234-1234-1234-1234 28-OCT-2015        123
         2        4001  2345234523452345 2345-2345-2345-2345 28-OCT-2015        234
         3        4002  3456345634563456 3456-3456-3456-3456 28-OCT-2015        345
         4        4003  4567456745674567 4567-4567-4567-4567 28-OCT-2015        456
         5        4004  5678567856785678 5678-5678-5678-5678 28-OCT-2015        567

5 rows selected.

SQL>

Add a new Policy

Creating a new redaction policy is done using the ADD_POLICY procedure in the DBMS_REDACT package. A policy is made up of several distinct sections.

The following example is about as simple as it gets. A full redaction policy is placed on the CARD_NO column with an expression of "1=1".

CONN test/test@pdb1

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'test',
    object_name   => 'payment_details',
    column_name   => 'card_no',
    policy_name   => 'redact_card_info',
    function_type => DBMS_REDACT.full,
    expression    => '1=1'
  );
END;
/

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999

SELECT *
FROM   payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000                 0 1234-1234-1234-1234 28-OCT-2015        123
         2        4001                 0 2345-2345-2345-2345 28-OCT-2015        234
         3        4002                 0 3456-3456-3456-3456 28-OCT-2015        345
         4        4003                 0 4567-4567-4567-4567 28-OCT-2015        456
         5        4004                 0 5678-5678-5678-5678 28-OCT-2015        567

5 rows selected.

SQL>

We can see the CARD_NO column is now redacted to the number "0". The value displayed by full redaction is based on the data type defaults for the DBMS_REDACT.FULL function type. You can see the default values by querying the REDACTION_VALUES_FOR_TYPE_FULL view, shown by the following example that uses the redaction_columns.sql script.

CONN sys@pdb1 AS SYSDBA

@redaction_value_defaults.sql

NUMBER_VALUE BINARY_FLOAT_VALUE BINARY_DOUBLE_VALUE CHAR_VALUE VARCHAR_VA NCHAR_VALU NVARCHAR_V DATE_VALUE           TIMESTAMP_VALUE             TIMESTAMP_WITH_TIME_ZONE_VALUE   BLOB_VALUE           CLOB_VALUE NCLOB_VALU
------------ ------------------ ------------------- ---------- ---------- ---------- ---------- -------------------- --------------------------- -------------------------------- -------------------- ---------- ----------
           0                  0                   0                                             01-JAN-2001 00:00:00 01-JAN-2001 01:00:00.000000 01-JAN-01 01.00.00.000000 +00:00 5B72656461637465645D [redacted] [redacted]

1 row selected.

SQL>

These default values can be altered using the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure, but you will need to start the instance for the updates to be visible.

Information about existing policies is displayed using the REDACTION_COLUMNS and REDACTION_POLICIES views, used in the redaction_policies.sql and redaction_columns.sql scripts below.

CONN sys@pdb1 AS SYSDBA

@redaction_policies

OBJECT_OWNER         OBJECT_NAME                    POLICY_NAME                    EXPRESSION                     ENABLE  POLICY_DESCRIPTION
-------------------- ------------------------------ ------------------------------ ------------------------------ ------- --------------------
TEST                 PAYMENT_DETAILS                redact_card_info               1=1                            YES

1 row selected.

SQL>


@redaction_columns

OBJECT_OWNER         OBJECT_NAME                    COLUMN_NAME                    FUNCTION_TYPE               FUNCTION_PARAMETERS            REGEXP_PATTERN                 REGEXP_REPLACE_STRING          REGEXP_POSITION REGEXP_OCCURRENCE REGEXP_MAT COLUMN_DESCRIPTION
-------------------- ------------------------------ ------------------------------ --------------------------- ------------------------------ ------------------------------ ------------------------------ --------------- ----------------- ---------- --------------------
TEST                 PAYMENT_DETAILS                CARD_NO                        FULL REDACTION                                                                                                                         0                 0

1 row selected.

SQL>

Other variations on redaction policies are described in the following section. These are equally applicable during redaction policy creation.

Alter an Existing Policy

The ALTER_POLICY procedure allows you to make changes to an existing policy. The type of change being made is controlled using the ACTION parameter. Depending on the action required, the relevant parameters must be specified.

The following example changes the previously created redaction policy so that it uses partial redaction. Notice the FUNCTION_PARAMETERS are now specified to give instructions how the partial redaction should take place. For a numeric data type we specify a comma separated list of three elements (value to redact to, start point, end point), so in this case we want the first 12 characters of the number to always display as "111111111111".

CONN test/test@pdb1

BEGIN
  DBMS_REDACT.alter_policy (
    object_schema       => 'test',
    object_name         => 'payment_details',
    policy_name         => 'redact_card_info',
    action              => DBMS_REDACT.modify_column,
    column_name         => 'card_no',
    function_type       => DBMS_REDACT.partial,
    function_parameters => '1,1,12'
  );
END;
/
   
ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999

SELECT *
FROM   payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1111111111111234 1234-1234-1234-1234 28-OCT-2015        123
         2        4001  1111111111112345 2345-2345-2345-2345 28-OCT-2015        234
         3        4002  1111111111113456 3456-3456-3456-3456 28-OCT-2015        345
         4        4003  1111111111114567 4567-4567-4567-4567 28-OCT-2015        456
         5        4004  1111111111115678 5678-5678-5678-5678 28-OCT-2015        567

5 rows selected.

SQL>

We can add another column to the redaction policy to protect the string representation of the card number.

BEGIN
  DBMS_REDACT.alter_policy (
    object_schema       => 'test',
    object_name         => 'payment_details',
    policy_name         => 'redact_card_info',
    action              => DBMS_REDACT.add_column,
    column_name         => 'card_string',
    function_type       => DBMS_REDACT.partial,
    function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,#,1,12'
  );
END;
/

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999

SELECT *
FROM   payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1111111111111234 ####-####-####-1234 28-OCT-2015        123
         2        4001  1111111111112345 ####-####-####-2345 28-OCT-2015        234
         3        4002  1111111111113456 ####-####-####-3456 28-OCT-2015        345
         4        4003  1111111111114567 ####-####-####-4567 28-OCT-2015        456
         5        4004  1111111111115678 ####-####-####-5678 28-OCT-2015        567

5 rows selected.

SQL>

The following example redacts the expiry date using partial redaction, converting the day and month values to 1st of January.

BEGIN
  DBMS_REDACT.alter_policy (
    object_schema       => 'test',
    object_name         => 'payment_details',
    policy_name         => 'redact_card_info',
    action              => DBMS_REDACT.add_column,
    column_name         => 'expiry_Date',
    function_type       => DBMS_REDACT.partial,
    function_parameters => 'm1d1Y'
  );
END;
/

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999

SELECT *
FROM   payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1111111111111234 ####-####-####-1234 01-JAN-2015        123
         2        4001  1111111111112345 ####-####-####-2345 01-JAN-2015        234
         3        4002  1111111111113456 ####-####-####-3456 01-JAN-2015        345
         4        4003  1111111111114567 ####-####-####-4567 01-JAN-2015        456
         5        4004  1111111111115678 ####-####-####-5678 01-JAN-2015        567

5 rows selected.

SQL>

We can also amend the policy so it does not affect the schema owner. The following example uses the SYS_CONTEXT function in the EXPRESSION parameter to determine the current user, making the application of the redaction policy conditional.

CONN test/test@pdb1

BEGIN
  DBMS_REDACT.alter_policy (
    object_schema       => 'test',
    object_name         => 'payment_details',
    policy_name         => 'redact_card_info',
    action              => DBMS_REDACT.modify_expression,
    column_name         => 'card_no',
    expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''TEST'''
  );
END;
/

-- Test on current user.
ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999

SELECT *
FROM payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1234123412341234 1234-1234-1234-1234 28-OCT-2015        123
         2        4001  2345234523452345 2345-2345-2345-2345 28-OCT-2015        234
         3        4002  3456345634563456 3456-3456-3456-3456 28-OCT-2015        345
         4        4003  4567456745674567 4567-4567-4567-4567 28-OCT-2015        456
         5        4004  5678567856785678 5678-5678-5678-5678 28-OCT-2015        567

5 rows selected.

SQL>


-- Connect to another user and test.
GRANT SELECT ON test.payment_details TO test2;

CONN test2/test2@pdb1

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999

SELECT *
FROM   test.payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ----------- ---------- -----------
         1        4000  1111111111111234 ####-####-####-1234 01-JAN-2015        123
         2        4001  1111111111112345 ####-####-####-2345 01-JAN-2015        234
         3        4002  1111111111113456 ####-####-####-3456 01-JAN-2015        345
         4        4003  1111111111114567 ####-####-####-4567 01-JAN-2015        456
         5        4004  1111111111115678 ####-####-####-5678 01-JAN-2015        567

5 rows selected.

SQL>

As expected, the redaction policy no longer applies to the TEST user.

Details of the FUNCTION_PARAMETERS formats is available here. There are also predefined Partial Fixed Character Redaction Formats listed here.

Drop an Existing Policy

The DROP_POLICY procedure is used to remove an existing redaction policy. The following example drops the redaction policy and queries the data, showing the redaction is no longer taking place.

CONN test/test@pdb1

BEGIN
  DBMS_REDACT.drop_policy (
    object_schema => 'test',
    object_name   => 'payment_details',
    policy_name   => 'redact_card_info'
  );
END;
/

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999

SELECT *
FROM   payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO EXPIRY_DATE   SEC_CODE VALID_DATE
---------- ----------- ----------------- ----------- ---------- -----------
         1        4000  1234123412341234 27-OCT-2015        123
         2        4001  2345234523452345 27-OCT-2015        234
         3        4002  3456345634563456 27-OCT-2015        345
         4        4003  4567456745674567 27-OCT-2015        456
         5        4004  5678567856785678 27-OCT-2015        567

5 rows selected.

SQL>

Views

As mentioned previously, you can get information about redaction policies using the following views.

You can see examples scripts using these views here (redaction_policies.sql, redaction_columns.sql, redaction_value_defaults.sql).

Additional Information

For more information see:

Hope this helps. Regards Tim...

Back to the Top.