8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 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.
Related articles.
- Data Redaction (DBMS_REDACT) in Oracle Database 12c Release 1 (12.1)
- Oracle Data Redaction
- Column Masking
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:
- Configuring Oracle Data Redaction Policies
- Creating and Managing Multiple Named Policy Expressions
- DBMS_REDACT
- Data Redaction (DBMS_REDACT) in Oracle Database 12c Release 1 (12.1)
- Oracle Data Redaction
- Column Masking
Hope this helps. Regards Tim...