8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Setup
- Add a new Policy
- Alter an Existing Policy
- Drop an Existing Policy
- Views
- Additional Information
Related articles.
- Data Redaction (DBMS_REDACT) Enhancements in Oracle Database 12c Release 2 (12.2)
- Oracle Data Redaction
- Column Masking
Setup
We create two test users.
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba --drop user testuser1 cascade; create user testuser1 identified by testuser1 quota unlimited on users; grant create session, create table to testuser1; --drop user testuser2 cascade; create user testuser2 identified by testuser2 quota unlimited on users; grant create session to testuser2;
We need to make sure the first test user has access to the DBMS_REDACT
package.
grant execute on sys.dbms_redact to testuser1;
The example code in this article requires the following test table.
conn testuser1/testuser1@//localhost:1521/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.
- Identify the object : The
OBJECT_SCHEMA
,OBJECT_NAME
andCOLUMN_NAME
parameters identify the column to be redacted. - Give it a name : The
POLICY_NAME
parameter assigns a name to the policy. - What should happen? : The
FUNCTION_TYPE
parameter 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_PARAMETERS
or variousREGEXP_*
parameters. - When should it happen? : The
EXPRESSION
parameter 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_CONTEXT
function.
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 testuser1/testuser1@//localhost:1521/pdb1 begin dbms_redact.add_policy( object_schema => user, 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/SysPassword1@//localhost:1521/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/SysPassword1@//localhost:1521/pdb1 as sysdba @redaction_policies OBJECT_OWNER OBJECT_NAME POLICY_NAME EXPRESSION ENABLE POLICY_DESCRIPTION -------------------- ------------------------------ ------------------------------ ------------------------------ ------- -------------------- TESTUSER1 PAYMENT_DETAILS redact_card_info 1=1 YES 1 row selected. SQL> @redaction_columns testuser1 payment_details OBJECT_OWNER OBJECT_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS REGEXP_PATTERN REGEXP_REPLACE_STRING REGEXP_POSITION REGEXP_OCCURRENCE REGEXP_MAT COLUMN_DESCRIPTION -------------------- ------------------------------ ------------------------------ --------------------------- ------------------------------ ------------------------------ ------------------------------ --------------- ----------------- ---------- -------------------- TESTUSER1 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 testuser1/testuser1@//localhost:1521/pdb1 begin dbms_redact.alter_policy ( object_schema => user, 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 => user, 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 => user, 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 testuser1/testuser1@//localhost:1521/pdb1 begin dbms_redact.alter_policy ( object_schema => user, object_name => 'payment_details', policy_name => 'redact_card_info', action => dbms_redact.modify_expression, column_name => 'card_no', expression => 'sys_context(''userenv'',''session_user'') != ''TESTUSER1''' ); 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 testuser1.payment_details TO testuser2; conn testuser2/testuser2@//localhost:1521/pdb1 ALTER SESSION SET nls_date_format='DD-MON-YYYY'; COLUMN card_no FORMAT 9999999999999999 SELECT * FROM testuser1.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 testuser1/testuser1@//localhost:1521/pdb1 begin dbms_redact.drop_policy ( object_schema => user, 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 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>
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 POLICY
system 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_DATABASE
role includes theEXEMPT REDACTION POLICY
system privilege. As this role is granted to the DBA role, DBAs are excluded from redaction policies. - Redaction does not apply to the
WHERE
clause, 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.
For more information see:
- Using Oracle Data Redaction
- DBMS_REDACT
- Data Redaction (DBMS_REDACT) Enhancements in Oracle Database 12c Release 2 (12.2)
- Oracle Data Redaction
- Column Masking
Hope this helps. Regards Tim...