Oracle data redaction in 12c – Part II
Introduction
As discussed in Data Redaction in Oracle Database 12c - Part 1, this feature conceals the data from being read by the unauthorized end users. Unlike the Data Masking operation in versions prior to Oracle Database 12c, data redaction does not modify the actual data in the database buffers or caches but only formats the output on the fly while displaying the result to the application.
Methods to redact different types of data:
Random Data Redaction:
This method redacts the complete value with a random value. The redacted data displayed to the user keeps changing randomly each time the query is run and this depends on the data type of the column being redacted. Set the parameter "function_type" of DBMS_REDACT.ADD_POLICY policy procedure to "DBMS_REDACT.RANDON".
This method holds good for Character Data Type, Number Data Type and Date-Time Data Type.
Random redaction for a character data type behaves differently for a CHAR and VARCHAR data type. For a CHAR data type, the redacted random value will be of the same length as that of the column definition. For a VARCHAR data type, the length of the redacted random value is limited to the length of the actual data in the column being redacted.
The below example performs a RANDOM redaction on column EMP_NAME of type character with random values.
Example to perform RANDOM redaction on a column of CHARACTER data type
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'HR',
object_name => 'emp',
column_name => 'emp_name',
policy_name => 'random_character_redact',
function_type => DBMS_REDACT.RANDOM,
expression => '1=1');
END;
/
SQL> select emp_name from emp;
EMP_NAME
-------------
8:g'U
B0XJ:
:^+QRC
Random redaction for a column of number data type produces random numbers that do not exceed the precision of the actual data.
Example to perform RANDOM redaction on a column of NUMBER data type
The example has the actual data for the EMPID column starting with 100 upto 102.
Actual data before redaction:
SQL> select * from emp;
EMPID EMP_NAME
---------- --------------------
100 SCOTT
101 JAMES
102 Narain
Now a policy is created for Random redaction on the column EMPID.
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'HR',
object_name => 'emp',
column_name => 'empid',
policy_name => 'random_num_redact',
function_type => DBMS_REDACT.RANDOM,
expression => '1=1');
END;
/
After redaction, the displayed result set shows the EMPID with random numbers.
SQL> select * from emp;
EMPID EMP_NAME
---------- --------------------
23 SCOTT
63 JAMES
12 Narain
Random redaction for a column of type date time works in a fashion that the displayed redacted result set is always with random dates that are different from that of the actual data.
Example to perform RANDOM redaction on a column of DATE data type
Before redaction, the DOJ column has the actual dates.
SQL> select * from emp;
EMPID EMP_NAME DOJ
---------- -------------------- ---------
100 SCOTT 23-MAR-15
101 JAMES 15-MAY-15
102 Narain 15-JAN-16
Random redaction policy is now created for the DOJ column:
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'HR',
object_name => 'emp',
column_name => 'doj',
policy_name => 'random_date_redact',
function_type => DBMS_REDACT.RANDOM,
expression => '1=1');
END;
/
After redaction, the column DOJ has in a different set of random dates as redacted values.
These values keep changing randomly each time the column is queried.
SQL> select * from emp;
EMPID EMP_NAME DOJ
---------- -------------------- ---------
100 SCOTT 22-APR-88
101 JAMES 28-APR-28
102 Narain 08-APR-58
SQL> select * from emp;
EMPID EMP_NAME DOJ
---------- -------------------- ---------
100 SCOTT 26-JUL-60
101 JAMES 20-OCT-61
102 Narain 08-JUN-32
Redaction using regular expression:
This method uses regular expressions to redact specific data in the column based on the pattern search. To use this mode, set the parameter "function_type" of the DBMS_REDACT.ADD_POLICY procedure to DBMS_REDACT.REGEXP followed by the following parameters:
- regexp_pattern: specifies the search pattern for the data to be redacted and replaces it as specified by the regexp_replace_string setting.
- regexp_replace_string: specifies how the data to be redacted needs to be replaced.
- regexp_position: The starting position for the string search.
- regexp_occurence: Specifies which occurrence of the searched string needs to be replaced. If specified with RE_ALL format, then it replaces all the occurrences of the string match. If specified as RE_FIRST, then it replaces only the first occurrence of the matched string. If a positive integer is specified, then it replaces that integer-th occurrence of the match.
No redaction:
This creates a policy on the table column but does not redact any data thereby no effect seen on the result set of the query. This method is used to test the redaction policy definitions before implementing them in the production environment. Set the "function_type" of the DBMS_REDACT.ADD_POLICY procedure to NONE. If not specified, then as said earlier, the default mode will be FULL Redaction.
Example :
Below is the result set of actual data without redaction for the table EMP.
SQL> select * from emp;
EMPID EMP_NAME DOJ
---------- -------------------- ---------
100 SCOTT 23-MAR-15
101 JAMES 15-MAY-15
102 Narain 15-JAN-16
A redaction policy is created on the column EMPID of table EMP with the function_type as "DBMS_REDACT.NONE".
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'HR',
object_name => 'emp',
column_name => 'empid',
policy_name => 'redact_empid',
function_type => DBMS_REDACT.NONE,
expression => '1=1');
END;
/
The result set of the query still remains the same after the policy creation.
SQL> select * from emp;
EMPID EMP_NAME DOJ
---------- -------------------- ---------
100 SCOTT 23-MAR-15
101 JAMES 15-MAY-15
102 Narain 15-JAN-16
Subtasks of DBMS_REDACT package:
**Altering an existing policy:**To alter an existing policy, use the DBMS_REDACT.ALTER_POLICY procedure. Altering a policy depends on the "action" value settings.
The values for the "Action" parameter can be any one of these:
- DBMS_REDACT.ADD_COLUMN - To add a new column for redaction to an existing policy.
- DBMS_REDACT.DROP_COLUMN - To remove redaction from a column.
- DBMS_REDACT.MODIFY_EXPRESSION - To change the expression value. At any time, a policy can have only one expression.
- DBMS_REDACT.SET_POLICY_DESCRIPTION - To change the description of the policy.
- DBMS_REDACT.SET_COLUMN_DESCRIPTION - TO change the description of the column.
Example:
A FULL Redaction is already set up on the ACCOUNT_NUMBER column by defining the policy "Redact Customer Table" thereby redacting the ACCOUNT_NUMBER.
BEGIN
DBMS_REDACT.ADD_POLICY (
object_schema => 'BNK',
object_name => 'customer',
column_name => 'ACCOUNT_NUMBER',
policy_name => 'Redact Customer Table',
function_type => DBMS_REDACT.FULL,
expression => '1=1');
END;
/
SQL> select * from customer;
ACCOUNT_NUMBER NAME CIF
-------------- ---------- -------
0 MICHAEL 3492
0 JOHNNY 38284
0 Smith 482910
Now, let's say to redact another column "NAME" of the same table "CUSTOMER", use the "action" parameter with the value "DBMS_REDACT.ADD_COLUMN". In addition to this, since we would like to perform a Partial Redaction on this NAME column, we also need to specify the function_type with DBMS_REDACT.PARTIAL and the function_parameters values.
BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => 'BNK',
object_name => 'customer',
action => DBMS_REDACT.ADD_COLUMN,
column_name => 'NAME',
policy_name => 'Redact Customer Table',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'VVVVVVVVVVVV,VVVVVVVVVVVV,#,2,4',
expression => '1=1');
END;
/
Now, the CUSTOMER table has the FULL redaction implemented on ACCOUNT_NUMBER and PARTIAL redaction implemented on the NAME column.
SQL> select * from customer;
ACCOUNT_NUMBER NAME CIF
-------------- -------------------- ----------
0 M###AEL 3492
0 J###NY 38284
0 S###h 482910
Dropping a Redaction Policy: Use the DBMS_REDACT.DROP_POLICY procedure to drop a redaction policy. Specify the object_name, object_schema and the policy name to drop the specific redaction policy.
Example:
The below example drops the policy "Full_num_redact".
BEGIN
DBMS_REDACT.DROP_POLICY(
object_schema => 'BNK',
object_name => 'customer',
policy_name => 'Full_num_redact');
END;
/
Disabling Redaction Policy : To disable a redaction policy, use the DBMS_REDACT.DISABLE_POLICY procedure. To get a list of the status of the redaction policies, query the REDACTION_POLICIES table. Once a redaction policy is disabled, there will be no effect of redaction on the result set of the query. But disabling a redaction policy does not allow to create a new policy on the same table. As said earlier, there can be only one redaction policy for a table. If a new policy needs to be created, then the existing ones need to be dropped.
Example:
The CUSTOMER table has a set of redaction on columns ACCOUNT_NUMBER and NAME.
SQL> select * from customer;
ACCOUNT_NUMBER NAME CIF
-------------- -------------------- ----------
0 M###AEL 3492
0 J###NY 38284
0 S###h 482910
We disable the redaction policy “Redact Customer Table” as below.
Begin
DBMS_REDACT.DISABLE_POLICY(
object_schema => 'BNK',
object_name => 'customer',
policy_name => 'Redact Customer Table');
END;
/
Now querying the result set displays the actual data.
SQL> select * from customer;
ACCOUNT_NUMBER NAME CIF
-------------- ---------- --------
8223496 MICHAEL 3492
30383903 JOHNNY 38284
94033892 Smith 482910
**Enabling Redaction Policy:**To enable a redaction policy, use the DBMS_REDACT.ENABLE_POLICY procedure. By default, when a new policy is created, the policy is always in a ENABLED state.
Begin
DBMS_REDACT.ENABLE_POLICY(
object_schema => 'BNK',
object_name => 'customer',
policy_name => 'Redact Customer Table');
END;
/
**Altering the default full redaction data values:**As mentioned previously, the default values of the redacted data for a full redaction on a column of number data type is 0, a single space for a character data type and the first day of January 2001 for a date type. The default values can be changed by using the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure. The procedure needs to be run as a SYS user and then bounce the database for the changes to take effect. Please note that this change of the default redacted values will be reflected on all the existing FULL Redaction Policies.
Example:
EXEC DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES (number_val => 10);
This changes the default full redaction value for a column of number data type from 0 to 10.
Considerations to be noted:
-
Data redaction is not support on columns of Large Data Types (BLOB, CLOB).
-
Data redaction has no effect on DML operations like INSERT, DELETE, UPDATE. Users can perform these operations regardless of whether the redaction policy exists or not. Data redaction affects only on the SELECT statements issued by the user.
-
Users cannot perform CREATE TABLE AS SELECT operations from the source tables which has its columns redacted. Only users granted with EXEMPT REDACTION POLICY system privilege can perform this operation.
About the Authors
Nassyam Basha Oracle DBA, OCM 11g, Oracle ACE Director, Author of Data Guard 11gR2 Book, Blogger, OTN Moderator and Super Hero, MOSC Guru, Writer with OTN, DELL and having around 9 years of hands on experience in High Availability technologies Like Oracle RAC, Data Guard, Exadata and much more. Currently working with The Pythian Group, In the past i have worked for AT&T(Bell Labs), dbaDirect, SLK software services.
Shivananda Rao is an Oracle ACE Associate and working as a Senior Oracle DBA. He has good knowledge on Oracle technologies specifically with High Availability, Disaster Recovery, Upgrades and RMAN. He has been actively participating in the OTN forum and maintains Oracle technical blog (www.shivanandarao-oracle.com) which has more than 50 articles published by him. He has an expertise of working on Dataguard and RMAN issues with much concentrated on High Availability topics.