14 Oracle Data Redaction Features and Capabilities
Oracle Data Redaction provides a variety of ways to redact different types of data.
- Getting Started with Oracle Data Redaction
You can create and enable Oracle Data Redaction policies by using theDBMS_REDACT
package. You define redaction policies at several levels. - Full Data Redaction to Redact All Data
Full data redaction redacts the entire contents of a specified column of a table or view. - Partial Data Redaction to Redact Portions of Data
In partial data redaction, you redact portions of the displayed output. - Regular Expressions to Redact Patterns of Data
Regular expressions redact specific data within a column data value, based on a pattern search. You can use regular expressions to redact a column of strings of different lengths. - Redaction Using Null Values
You can create an Oracle Data Redaction policy that redacts column data by showing only null values in the query result. This is done by redacting the result of the database query just before presenting the results without affecting the data directly. - Random Data Redaction to Generate Random Values
In random data redaction, the entire value is redacted by replacing it with a random value. - Comparison of Full, Partial, Regexp, Nullify, and Random Redaction Based on Data Types
The full, partial, regular expression, nullify, and random data redaction styles affect the Oracle built-in, ANSI, user-defined, and Oracle supplied types in different ways. - No Redaction for Testing Purposes
You can create a Data Redaction policy that does not perform redaction. This enables you to include redaction policies in your applications during testing, and then apply those polices to your data as you move the application to production. - Central Management of Named Data Redaction Policy Expressions
You can create a library of named policy expressions that can be used in the columns of multiple tables and views.
Parent topic: Using Oracle Data Redaction
14.1 Getting Started with Oracle Data Redaction
You can create and enable Oracle Data Redaction policies by using the DBMS_REDACT
package. You define redaction policies at several levels.
-
The schema level specifies the exact schema where one or more columns of an object have to be redacted.
-
The object level includes tables, views, and materialized views where Orace Data Redaction policies are applied. You may apply a maximum of one policy per object.
-
The column level includes where redaction functions operate. You can define a redaction function on one column when you create a redaction policy. You then can edit the policy to redact additional columns in the object.
For example, you can create an Oracle Data Redaction policy on the SALARY
column of an HR.EMPLOYEES
table with the following procedure:
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'hr',
object_name => 'employees',
column_name => 'salary',
policy_name => 'hr_emp_redact_comp_pol',
function_type => DBMS_REDACT.FULL,
expression => '1=1');
END;
In this case, we use the ADD_POLICY
procedure in the DBMS_REDACT
package to define an Oracle Data Redaction policy called hr_emp_redact_comp_pol
. The function type DBMS_REDACT.FULL
specifies that full data redaction be performed in retrieved values in the salary column, which means that by default, number data types are replaced with zero (0) in the output text. The expression
parameter sets the policy to perform the redaction if it evaluates to TRUE
(1=1
).
Oracle Data Redaction provides a variety of ways to redact different types of data, which are described in this section.
Parent topic: Oracle Data Redaction Features and Capabilities
14.2 Full Data Redaction to Redact All Data
Full data redaction redacts the entire contents of a specified column of a table or view.
By default the output is displayed as follows:
-
Character data types: The output text is a single space.
-
Number data types: The output text is a zero (
0
). -
Date-time data types: The output text is set to the first day of January, 2001, which appears as
01-JAN-2001
.
When you run the DBMS_REDACT.ADD_POLICY
procedure, to set the function_type
parameter setting for full redaction, you enter the following setting:
function_type => DBMS_REDACT.FULL
Full redaction is the default and is used whenever a Data Redaction policy specifies the column but omits the function_type
parameter setting.
You can use the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES
procedure to change the full redaction output to different values. You can find the current values by querying the REDACTION_VALUES_FOR_TYPE_FULL
data dictionary view.
14.3 Partial Data Redaction to Redact Portions of Data
In partial data redaction, you redact portions of the displayed output.
You can set the position within the actual data at which to begin the redaction, the number of characters to redact starting from that position, and the redaction character to use. (Actual data is the data in a protected table or view. An example of actual data could be the number 123456789, and the redacted data version of this number could be 999996789.) This type of redaction is useful for situations where you want it to be obvious to the person viewing the data that it was redacted in some way. Typically, you use this type of redaction for credit card numbers or ID numbers.
Be aware that partial data redaction requires that your data width remain fixed. If you want to redact columns containing string values of variable length, then you must use regular expressions.
To specify partial redaction, you must set the DBMS_REDACT.ADD_POLICY
or DBMS_REDACT.ALTER_POLICY
procedure function_type
parameter to DBMS_REDACT.PARTIAL
and use the function_parameters
parameter to define the partial redaction behavior.
The displayed output for partial data redaction can be as follows:
-
Character data types: When partially redacted, a Social Security Number (represented as a hyphenated string within a character data type) with value
987-65-4320
could be redacted so that it is displayed as shown in the following examples. The code on the right specifies how to redact the character data: it specifies the expected input format of the actual data, the format to use for the display of the redacted output, the character to use for the redaction, the start position at which to begin the redaction, and how many characters to redact. The first example uses a predefined format (in previous releases called a shortcut) for character data type Social Security Numbers, and the second example replaces the first five numbers with an asterisk (*
) while preserving the hyphens (-
) in between the numbers.-
function_parameters => DBMS_REDACT.REDACT_US_SSN_F5
results inXXX-XX-4320
-
function_parameters => 'VVVFVVFVVVV,VVV-VV-VVVV,*,1,5
' result in***-**-4320
In these examples,
V
describes each character that potentially can be redacted, andF
describes each character that you want to format using a formatting character. -
-
Number data types: Partially redacted
NUMBER
data types appear with some numerals replaced with specified characters. For example, a Social Security number stored as987654321
could appear as follows. Both redact the first five digits. The first example uses a predefined format that is designed for Social Security numbers in theNUMBER
data type, and the second replaces the first five numbers with the number9
, starting from the first digit.-
function_parameters => DBMS_REDACT.REDACT_NUM_US_SSN_F5
results inXXXXX4321
-
function_parameters => '9,1,5
' results in999994321
-
-
Date-time data types: Partially redacted datetime values can appear simply as different dates. For example, the date
29-AUG-11 10.20.50.000000 AM
could appear as follows. In the first example, the day of the month is redacted to02
(using the settingd02
) and in the second example, the month is redacted toDEC
(usingm12
). The uppercase values show the actual day (D
), actual month (M
), actual year (Y
), actual hour (H
), actual minute (M
), and actual second (S
).-
function_parameters => 'Md02YHMS'
results in02-AUG-11 10.20.50.000000 AM
-
function_parameters => 'm12DYHMS'
results in29-DEC-11 10.20.50.000000 AM
-
14.4 Regular Expressions to Redact Patterns of Data
Regular expressions redact specific data within a column data value, based on a pattern search. You can use regular expressions to redact a column of strings of different lengths.
For example, you can redact the user name of email addresses, so that only the domain shows (for example, replacing hpreston
in the email address hpreston@example.com
with [redacted]
so that it appears as [redacted]@example.com
). To perform the redaction, set the DBMS_REDACT.ADD_POLICY
or DBMS_REDACT.ALTER_POLICY
procedure function_type
parameter to either DBMS_REDACT.REGEXP
or DBMS_REDACT.REGEXP_WIDTH
, and then use the following parameters to build the regular expression:
-
A string search pattern (that is, the values to search for), such as:
regexp_pattern => '(.+)@(.+\.[A-Za-z]{2,4})'
This setting looks for a pattern of the following form:
one_or_more_characters@one_or_more_characters.2-4_characters_in_range_A-Z_or_a-z
-
A replacement string that replaces the value matched by the
regexp_pattern
setting. The replacement string can include back references to sub-expressions of the main regular expression pattern. The following example replaces the data before the@
symbol (from theregexp_pattern
setting) with the text[redacted]
. The\2
setting refers to the second match group, which is(.+\.[A-Za-z]{2,4})
from theregexp_pattern
setting.regexp_replace_string => '[redacted]@\2'
-
The starting position for the string search string, such as the first character of the data, such as:
regexp_position => DBMS_REDACT.RE_BEGINNING
This value is the default if it is not specified.
-
The kind of search and replace operation to perform, such as the first occurrence, every fifth occurrence, or all of the occurrences, such as:
regexp_occurrence => DBMS_REDACT.RE_ALL
This value is the default if it is not specified.
-
The default matching behavior for the search and replace operation, such as whether the search is case-sensitive (
i
sets it to be not case-sensitive):regexp_match_parameter => 'i'
In addition to the default parameters, you can use a set of predefined formats that enable you to use commonly used regular expressions for telephone numbers, email addresses, and credit card numbers.
14.5 Redaction Using Null Values
You can create an Oracle Data Redaction policy that redacts column data by showing only null values in the query result. This is done by redacting the result of the database query just before presenting the results without affecting the data directly.
This feature enables you to use the DBMS_REDACT.NULLIFY
function to hide all of the sensitive data in a table or view column and replace it with null values. You can set this function by using the function_type
parameter of the DBMS_REDACT.ADD_POLICY
or DBMS_REDACT.ALTER_POLICY
procedure.
For example:
function_type => DBMS_REDACT.NULLIFY
Related Topics
Parent topic: Oracle Data Redaction Features and Capabilities
14.6 Random Data Redaction to Generate Random Values
In random data redaction, the entire value is redacted by replacing it with a random value.
The redacted values displayed in the result set of the query change randomly each time application users run the query.
This type of redaction is useful in cases where you do not want it to be obvious that the data was redacted. It works especially well for number and datetime data types, where it is difficult to distinguish between random and real data.
The displayed output for random values changes based on the data type of the redacted column, as follows:
-
Character data types: The random output is a mixture of characters (for example,
HTU[G{\pjkEWcK
). It behaves differently for theCHAR
andVARCHAR2
data types, as follows:-
CHAR data type: The redacted output is always in the same character set as the character set of the column. The byte length of the redacted output is always the same as the column definition length (that is, the column length that was provided at the time of table creation). For example, if the column is
CHAR(20)
, then a string of 20 random characters is provided in the redacted output of the user's query. -
VARCHAR2 data type: For random redaction of a
VARCHAR
data type, the redacted output is always in the same character set as the character set of the column. The length of the redacted output is limited based on the length of the actual data in the column. No characters in excess of the length of the actual data are displayed. For example, if the column isVARCHAR2(20)
and the row being redacted contains actual data with a length of 12, then a string of 12 random characters (not 20) is provided in the redacted output of the user's query for that row.
-
-
Number data types: Each actual number value is redacted by replacing it with a random, non-negative number. This redaction results in random numbers that do not exceed the precision of the actual data. For example, the number
987654321
can be redacted by replacing it with any of the numbers12345678
,13579
,0
, or987654320
, but not by replacing it with any of the numbers987654321
,99987654321
, or-1
. The number-123
could be redacted by replacing it with the numbers122
,0
, or83
, but not by replacing it with any of the numbers123
,1123
, or-2
.The only exception to the above is when the actual value is an integer between -1 and 9. In this case, the actual data is redacted by replacing it with a random, non-negative integer modulo ten (10).
-
Date-time data types: When values of the date data type are redacted using random Data Redaction, Oracle Database displays them with random dates that are always different from those of the actual data.
The setting for using random redaction is as follows:
function_type => DBMS_REDACT.RANDOM
Related Topics
Parent topic: Oracle Data Redaction Features and Capabilities
14.7 Comparison of Full, Partial, Regexp, Nullify, and Random Redaction Based on Data Types
The full, partial, regular expression, nullify, and random data redaction styles affect the Oracle built-in, ANSI, user-defined, and Oracle supplied types in different ways.
- Oracle Built-in Data Types Redaction Capabilities
Oracle Data Redaction handles the Oracle built-in data types depending on the type of Data Redaction policies that are used. - ANSI Data Types Redaction Capabilities
Oracle Data Redaction converts ANSI data types in specific ways, depending on the type of redaction that the Data Redaction policy has. - Built-in and ANSI Data Types Full Redaction Capabilities
For full redaction, the default redacted value depends on whether the data type is Oracle built-in or ANSI. - User-Defined Data Types or Oracle Supplied Types Redaction Capabilities
Several data types are not supported by Oracle Data Redaction.
Parent topic: Oracle Data Redaction Features and Capabilities
14.7.1 Oracle Built-in Data Types Redaction Capabilities
Oracle Data Redaction handles the Oracle built-in data types depending on the type of Data Redaction policies that are used.
Table 14-1 describes the Oracle Data Redaction support for Oracle built-in data types.
Table 14-1 Redaction Support for Oracle Built-in Data Types
Column Data Type | Full Redaction | Partial Redaction | Regexp Redaction | Random Redaction | Nullify Redaction |
---|---|---|---|---|---|
CharacterFoot 1 |
Yes |
Yes |
Yes |
Yes |
Yes |
NumberFoot 2 |
Yes |
Yes |
No |
Yes |
Yes |
Date-timeFoot 3 |
Yes |
Yes |
No |
Yes |
Yes |
|
Yes |
No |
No |
Yes |
Yes |
|
Yes |
No |
No |
No |
Yes |
|
Yes |
No |
Yes |
No |
Yes |
|
Yes |
No |
Yes |
No |
Yes |
|
No |
No |
No |
No |
No |
|
No |
No |
No |
No |
No |
RawFoot 4 |
No |
No |
No |
No |
No |
IntervalFoot 5 |
No |
No |
No |
No |
No |
|
No |
No |
No |
No |
No |
Footnote 1
Includes CHAR
, VARCHAR2
(including long VARCHAR2
, for example, VARCHAR2(20000)
), NCHAR
, NVARCHAR2
Footnote 2
Includes NUMBER
, FLOAT
, BINARY_FLOAT
, BINARY_DOUBLE
Footnote 3
Includes DATE
, TIMESTAMP
, TIMESTAMP WITH TIME ZONE
, TIMESTAMP WITH LOCAL TIME ZONE
Footnote 4
Includes LONG
RAW
, RAW
Footnote 5
Includes INTERVAL YEAR TO MONTH
,
INTERVAL DAY TO SECOND
14.7.2 ANSI Data Types Redaction Capabilities
Oracle Data Redaction converts ANSI data types in specific ways, depending on the type of redaction that the Data Redaction policy has.
Table 14-2 compares how the full, partial, regular expression, nullify, and random redaction styles work for ANSI data types, with regard to how they are converted and their support status.
Table 14-2 Redaction Support for the ANSI Data Types
Data Type | How Converted | Full Redaction | Partial Redaction | Regexp Redaction | Nullify Redaction | Random Redaction |
---|---|---|---|---|---|---|
|
Converted to |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Converted to |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Converted to |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Converted to |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Converted to |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Converted to |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Converted to |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Converted to |
Yes |
Yes |
Yes |
Yes |
Yes |
|
No conversion |
No |
No |
No |
No |
No |
14.7.3 Built-in and ANSI Data Types Full Redaction Capabilities
For full redaction, the default redacted value depends on whether the data type is Oracle built-in or ANSI.
ANSI Data Types Redaction Capabilities shows the default settings for both Oracle built-in and ANSI data type columns that use full redaction.
Table 14-3 Default Settings and Categories for Columns That Use Full Redaction
Data Type | Default Redacted Value | Data Type Category |
---|---|---|
|
Single space ( |
Oracle built-in |
|
Single space ( |
ANSI |
|
Single space ( |
ANSI |
|
Single space ( |
ANSI |
|
Single space ( |
ANSI |
|
Zero ( |
Oracle built-in |
|
Zero ( |
Oracle built-in |
|
Zero ( |
ANSI |
|
Zero ( |
ANSI |
|
Zero ( |
ANSI |
|
|
Oracle built-in |
|
Oracle’s raw representation of |
Oracle built-in |
|
|
Oracle built-in |
|
|
Oracle built-in |
Footnote 6
If you have changed the character set, then you may need to invoke the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES
procedure to set the value to the raw representation in the new character set, as follows:
DECLARE
new_red_blob BLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(new_red_blob, TRUE);
DBMS_LOB.WRITE(new_red_blob, 10, 1, UTL_RAW.CAST_TO_RAW('[redacted]'));
dbms_redact.update_full_redaction_values(
blob_val => new_red_blob);
DBMS_LOB.FREETEMPORARY(new_red_blob);
END;
/
After you run this procedure, restart the database.
See also Altering the Default Full Data Redaction Value for more information about using the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES
procedure.
14.7.4 User-Defined Data Types or Oracle Supplied Types Redaction Capabilities
Several data types are not supported by Oracle Data Redaction.
Table 14-4 compares how the full, partial, regular expression, nullify, and random redaction styles work for user-defined and Oracle-supplied types.
Table 14-4 Redaction Support for the User-Defined Data Types or Oracle-Supplied Types
Data Type or Type | Full Redaction | Partial Redaction | Regexp Redaction | Nullify Redaction | Random Redaction |
---|---|---|---|---|---|
User-defined data types |
No |
No |
No |
No |
No |
Oracle supplied types: Anytype types, XML types, Oracle Spatial types |
No |
No |
No |
No |
No |
14.8 No Redaction for Testing Purposes
You can create a Data Redaction policy that does not perform redaction. This enables you to include redaction policies in your applications during testing, and then apply those polices to your data as you move the application to production.
This is useful for cases in which you have a redacted base table, yet you want a
specific application user to have a view that always shows the actual data. By default,
a view created on a redacted table is redacted with the same policy used for the base
table. However, you can define a separate redaction policy for the view. You can create
a new view of the redacted table and then define a Data Redaction policy for this view.
When the application queries the view, the policy defined on the view is applied. The
policy still exists on the base table, but no redaction is performed when the
application queries using the view as long as the DBMS_REDACT.NONE
function_type
setting was used to create a policy on the view.
Parent topic: Oracle Data Redaction Features and Capabilities
14.9 Central Management of Named Data Redaction Policy Expressions
You can create a library of named policy expressions that can be used in the columns of multiple tables and views.
By having named policy expressions, you can centrally manage all of the policy expressions within a database.
When you modify the policy expression, the change is reflected in all table columns
that use the expression. The named policy expression takes precedence over the
expression
setting in the Data Redaction policy. To create the
named policy expression, you must use the
DBMS_REDACT.CREATE_POLICY_EXPRESSION
procedure, and to apply the
policy expression to a column, you use the
DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL
procedure. This feature
provides flexibility to redact different columns in a table or view, based on different
runtime conditions.
For example, consider a use case that involves a customer care application. A customer calls the customer care center to request a return on a recent purchase. A level 1 support representative of the call center must first verify the order ID, customer name, and customer address before initiating the return. During the process, there is no need for the level 1 support representative to view the customer’s credit card number. So, the credit card number column is redacted when the support representative queries the customer details in the call center application. When the return is initiated, a sales representative from the return department may need to view the credit card number to process the return. However, there is no need for the sales representative to view the expiration date of the credit card. So, when the sales representative queries the customer details in the same application, the credit card number is visible but the expiration date is redacted.
In this use case, different columns in the customer details table must be redacted in different ways, based on who the logged in user is. Oracle Data Redaction simplifies the implementation of this use case by using named Data Redaction policy expressions. This type of policy expression enables you to define and associate different policy expressions on different columns in the same table or view. Moreover, you can centrally manage named policy expressions within a database. Any updates that you make to a named policy expression are immediately propagated to all of the associated table or view columns.
Related Topics
Parent topic: Oracle Data Redaction Features and Capabilities