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

Data Redaction (DBMS_REDACT) Enhancements in Oracle Database 12c Release 2 (12.2)

This article demonstrates a number of the enhancements to Data Redaction in Oracle Database 12c Release 2 (12.2). It assumes you are familiar with the redaction functionality in Oracle 12.1 described here.

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

Setup
Use NULL as the Redacted Value
Named Policy Expressions
Miscellaneous

Setup

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

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

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
SET LINESIZE 100

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>

Use NULL as the Redacted Value

The value NULL can now be used as a redaction value by specifying DBMS_REDACT.NULLIFY in the FUNCTION_TYPE parameter of the ADD_POLICY procedure. The example below creates such a policy on the CARD_NO column, and uses partial redaction on the CARD_STRING column. Notice the values of the CARD_NO column are all shown as NULL now, while the values on the CARD_STRING column are partially redacted.

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.nullify,
    expression    => '1=1'
  );

  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 => DBMS_REDACT.REDACT_CCN16_F12
  );
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			 ****-****-****-1234 28-AUG-2018	123
	 2	  4001			 ****-****-****-2345 28-AUG-2018	234
	 3	  4002			 ****-****-****-3456 28-AUG-2018	345
	 4	  4003			 ****-****-****-4567 28-AUG-2018	456
	 5	  4004			 ****-****-****-5678 28-AUG-2018	567

SQL>

Named Policy Expressions

The policy expression is part of the policy definition and determines which rows should be redacted. A redaction policy can only have a single default expression, so all columns in the redaction policy have the same default expression. In addition to this default policy expression, in Oracle 12.2 you can define a named policy expression that can be applied to a column and overrides the default expression. This is useful for a number of reasons, including the following.

This allows you to define a collection of common named policy expressions to be used by all your policies, which in turn allows you to alter multiple policies simultaneously by altering the centralised named policy expression definition.
It allows each column in a redaction policy to be associated with a different expression, giving greater flexibility.

Check the current definition for the policy on the CARD_NO column using the REDACTION_POLICIES and REDACTION_COLUMNS views.

COLUMN policy_name FORMAT A30
COLUMN expression FORMAT A30

SELECT policy_name,
       expression
FROM   redaction_policies
WHERE  object_owner = 'TEST'
AND    object_name  = 'PAYMENT_DETAILS';

POLICY_NAME		       EXPRESSION
------------------------------ ------------------------------
redact_card_info	       1=1

SQL>

SET LINESIZE 120
COLUMN column_name FORMAT A30
COLUMN function_parameters FORMAT A50

SELECT column_name,
       function_type,
       function_parameters
FROM   redaction_columns
WHERE  object_owner = 'TEST'
AND    object_name  = 'PAYMENT_DETAILS';

COLUMN_NAME		       FUNCTION_TYPE		   FUNCTION_PARAMETERS
------------------------------ --------------------------- --------------------------------------------------
CARD_NO 		       NULLIFY REDACTION
CARD_STRING		       PARTIAL REDACTION	   VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12

SQL>

Create a named policy expression and associate it with the table column using the CREATE_POLICY_EXPRESSION and APPLY_POLICY_EXPR_TO_COL procedures respectively.

BEGIN
  DBMS_REDACT.create_policy_expression (
    policy_expression_name        => 'test_redact_policy',
    expression                    => '1=1',
    policy_expression_description => 'Always redact.');

  DBMS_REDACT.apply_policy_expr_to_col (
    object_schema           => 'test',
    object_name             => 'payment_details',
    column_name             => 'card_no',
    policy_expression_name  => 'test_redact_policy');
END;
/

We can see the policy has been applied using the following query against the REDACTION_EXPRESSIONS view.

SELECT column_name,
       expression
FROM   redaction_expressions
WHERE  object_owner = 'TEST'
AND    object_name  = 'PAYMENT_DETAILS';

COLUMN_NAME		       EXPRESSION
------------------------------ ------------------------------
CARD_NO 		       1=1

SQL>

The expression is still "1=1", so this hasn't affected the redaction. Notice the CARD_NO column is still redacted to NULL.

SELECT *
FROM   payment_details
ORDER BY id;

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

SQL>

Use the UPDATE_POLICY_EXPRESSION procedure to modify the named policy expression to turn off redaction and check the results.

BEGIN
  DBMS_REDACT.update_policy_expression(
    policy_expression_name => 'test_redact_policy',
    expression             => '1=0');
END;
/

SELECT *
FROM   payment_details
ORDER BY id;

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

SQL>

The redaction has now been disabled for the CARD_NO column, since the named policy expression overrides the default policy expression, but redaction is still in place for the CARD_STRING column, which was not associated with the named policy expression. Notice this doesn't affect the default expression in the policy definition, just the expression in the named expression.

COLUMN policy_name FORMAT A30
COLUMN expression FORMAT A30

SELECT policy_name,
       expression
FROM   redaction_policies
WHERE  object_owner = 'TEST'
AND    object_name  = 'PAYMENT_DETAILS';

POLICY_NAME		       EXPRESSION
------------------------------ ------------------------------
redact_card_info	       1=1

SQL>


SELECT column_name,
       expression
FROM   redaction_expressions
WHERE  object_owner = 'TEST'
AND    object_name  = 'PAYMENT_DETAILS';

COLUMN_NAME		       EXPRESSION
------------------------------ ------------------------------
CARD_NO 		       1=0

SQL>

Removing the named expression association returns the redaction to its original state.

BEGIN
  DBMS_REDACT.apply_policy_expr_to_col(
    object_schema          => 'test',
    object_name            => 'payment_details', 
    column_name            => 'card_no',
    policy_expression_name =>  null);
END;
/


SELECT *
FROM   payment_details
ORDER BY id;

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

SQL>

Provided the named policy expression is not associated with any columns, it can be removed using the DROP_POLICY_EXPRESSION procedure.

BEGIN
  DBMS_REDACT.drop_policy_expression(
    policy_expression_name  => 'test_redact_policy');
END;
/

Miscellaneous

Here are some additional enhancements.

The list of predefined Partial Fixed Character Redaction Formats has been extended in Oracle 12.2, as shown here.
In the previous release regular expression-based redaction policies, using DBMS_REDACT.REGEXP, were not possible on CLOB and NCLOB columns. That restriction has been lifted in Oracle 12.2.
Additional functions are supported in the policy expression and new named policy expression. The full list of supported operators and functions is listed here.
As with the previous release, Enterprise Manager allows you to create and manage custom partial redaction formats, allowing you to build a library of your favourites. This is not part of the core functionality. This functionality is described here.
A new view called REDACTION_EXPRESSIONS has been added. It's usage is demonstrated above.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.