📋Steps in this guide1/4

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).

oracle 12cconfigurationintermediate

by OracleDba

12 views

Setup

We need to make sure the test user has access to the package. The example code in this article requires the following test table.

Code/Command (click line numbers to comment):

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

GRANT EXECUTE ON sys.dbms_redact TO test;

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 in the parameter of the procedure. The example below creates such a policy on the column, and uses partial redaction on the column. Notice the values of the column are all shown as NULL now, while the values on the column are partially redacted.

Code/Command (click line numbers to comment):

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 column using the and views. Create a named policy expression and associate it with the table column using the and procedures respectively. We can see the policy has been applied using the following query against the view. The expression is still "1=1", so this hasn't affected the redaction. Notice the column is still redacted to NULL. Use the procedure to modify the named policy expression to turn off redaction and check the results. The redaction has now been disabled for the column, since the named policy expression overrides the default policy expression, but redaction is still in place for the 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. Removing the named expression association returns the redaction to its original state. Provided the named policy expression is not associated with any columns, it can be removed using the procedure.

Code/Command (click line numbers to comment):

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>

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;
/

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

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

SQL>

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>

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>

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>

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>

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 , 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 has been added. It's usage is demonstrated above. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!