Setup
We need to make sure the test user has access to theDBMS_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 theADD_POLICY procedure in the DBMS_REDACT package. A policy is made up of several distinct sections.- Identify the object : The
OBJECT_SCHEMA,OBJECT_NAMEandCOLUMN_NAMEparameters identify the column to be redacted. - Give it a name : The
POLICY_NAMEparameter assigns a name to the policy. - What should happen? : The
FUNCTION_TYPEparameter determines the type of redaction that should take place. The allowable values are listed here. Depending on the type of redaction selected, you may be required to specify theFUNCTION_PARAMETERSor variousREGEXP_*parameters. - When should it happen? : The
EXPRESSIONparameter determines when the redaction should take place. For example, an expression of "1=1" means the redaction will always take place. Alternatively, situational expressions can be defined using theSYS_CONTEXTfunction.
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
TheALTER_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
TheDROP_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
- Redaction will not take place if the user has the
EXEMPT REDACTION POLICYsystem privilege. - If you try to
CREATE TABLE ... AS SELECT(CTAS) against a redacted table you get the following error message.ORA-28081: Insufficient privileges - the command references a redacted object.
- The
DATAPUMP_EXP_FULL_DATABASErole includes theEXEMPT REDACTION POLICYsystem privilege. As this role is granted to the DBA role, DBAs are excluded from redaction policies. - Redaction does not apply to the
WHEREclause, so inference of the value is still possible for those with SQL access. - 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.
- There have been several high profile bugs related to security holes in redaction. Make sure you are patched.
