Oracle Data Redaction Features and Capabilities

Oracle Data Redaction provides a variety of ways to redact different types of data.

Full Data Redaction to Redact All Data

Full data redaction redacts the entire contents of the specified table or view column.

By default the output is displayed as follows:

Related Topics

Partial Data Redaction to Redact Sections 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. 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 cards 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 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:

Related Topics

Regular Expressions to Redact Patterns of Data

Regular expressions redact specific data within a column data value, based on a pattern search.

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 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:

Related Topics

Redaction Using Null Values

You can create an Oracle Data Redaction policy that redacts column data by replacing it with null values.

This feature enables you to use the DBMS_REDACT.NULLIFY function 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

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:

Related Topics

Comparison of Full, Partial, and Random Redaction Based on Data Types

The full, partial, 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. The table describes the Oracle Data Redaction support for Oracle built-in data types.

Column Data Type Full Partial Regexp Random
Character1 Yes Yes Yes Yes
Number2 Yes Yes No Yes
Raw3 No No No No
Date-time4 Yes Yes No Yes
Interval5 No No No No
BFILE No No No No
BLOB Yes No No No
CLOB Yes No Yes No
NCLOB Yes No Yes No
ROWID No No No No
UROWID No No No No

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. The table compares how the full, partial, and random redaction styles work for ANSI data types, with regard to how they are converted and their support status.

Data Type How Converted Full Redaction Partial Redaction Regexp NULL Redaction Random Redaction

CHARACTER(n),

CHAR(n)

 Converted to CHAR(n) Yes Yes Yes Yes Yes

CHARACTER VARYING(n),

CHAR VARYING(n)

 Converted to VARCHAR2(n) Yes Yes Yes Yes Yes

NATIONAL CHARACTER(n),

NATIONAL CHAR(n),

NCHAR(n)

 Converted to NCHAR(n) Yes Yes Yes Yes Yes

NATIONAL CHARACTER VARYING(n),

NATIONAL CHAR VARYING(n),

NCHAR VARYING(n)

 Converted to NVARCHAR2(n) Yes Yes Yes Yes Yes

NUMERIC[(p,s)]

DECIMAL[(p,s)]

Converted to NUMBER(p,s) Yes Yes Yes Yes Yes

INTEGER,

INT,

SMALLINT

 Converted to NUMBER(38) Yes Yes Yes Yes Yes

FLOAT,

DOUBLE PRECISION

Converted to FLOAT(126) Yes Yes Yes Yes Yes
REAL Converted to FLOAT(63) Yes Yes Yes Yes Yes

GRAPHIC,

LONG VARGRAPHIC,

VARGRAPHIC,

TIME

 No conversion No No No No No

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.

Data Type Default Redacted Value Data Type Category
CHARACTER Single space (" ") Oracle built-in

CHARACTER(n),

CHAR(n)

 Single space (" ") ANSI

CHARACTER VARYING(n),

CHAR VARYING(n)

 Single space (" ") ANSI

NATIONAL CHARACTER(n),

NATIONAL CHAR(n),

NCHAR(n)

 Single space (" ") ANSI

NATIONAL CHARACTER VARYING(n),

NATIONAL CHAR VARYING(n),

NCHAR VARYING(n)

 Single space (" ") ANSI
NUMBER Zero (0) Oracle built-in

NUMERIC[(p,s)]

DECIMAL[(p,s)]

Zero (0) Oracle built-in

INTEGER,

INT,

SMALLINT

 Zero (0) ANSI

FLOAT,

DOUBLE PRECISION

Zero (0) ANSI
REAL Zero (0) ANSI
DATE-TIME 01-01-01 or 01-01-01 01:00:00 Oracle built-in
BLOB Oracle’s raw representation of [redacted] 6 Oracle built-in
CLOB [redacted] Oracle built-in
NCLOB [redacted] Oracle built-in

User-Defined Data Types or Oracle Supplied Types Redaction Capabilities

Several data types or types are not supported by Oracle Data Redaction. The table compares how the full, partial, regular expression, and random redaction styles work for user-defined and Oracle-supplied types.

Data Type or Type Full Redaction Partial Redaction Regexp NULL Redaction Random Redaction
User-defined data types No No No No No
Oracle supplied types: Any types, XML types, Oracle Spatial types No No No No No

No Redaction for Testing Purposes

You can create a Data Redaction policy that does not perform redaction.

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. You can create a new view of the redacted table and then define a Data Redaction policy for this view. 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.

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 policy expression takes precedence over the expression setting in the Data Redaction policy. To create the policy expression, you must use the DBMS_REDACT.CREATE_POLICY_EXPRESSION procedure, and to apply the policy expression to a column, you use DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL. 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 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

  1. Includes CHAR, VARCHAR2 (including long VARCHAR2, for example, VARCHAR2(20000)), NCHAR, NVARCHAR2 ↩

  2. Includes NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE ↩

  3. Includes LONG RAW, RAW ↩

  4. Includes DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE ↩

  5. Includes INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND ↩

  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. ↩