3 Introduction to Oracle Data Redaction

Oracle Data Redaction is the ability to redact data, typically sensitive data in real time.

This chapter contains the following topics:

What Is Oracle Data Redaction?
Who Can Create Oracle Data Redaction Policies?
When to Use Oracle Data Redaction
Benefits of Using Oracle Data Redaction
How Oracle Data Redaction Affects the SYS and SYSTEM Schemas
How Data Redaction Works with Nested Functions, Inline Views, and the WHERE Clause
Using SQL Expressions to Build Reports That Contain Redacted Values
Target Use Cases for Oracle Data Redaction
Oracle Data Redaction Features and Capabilities
Using Oracle Data Redaction with Other Oracle Database Features

3.1 What Is Oracle Data Redaction?

Oracle Data Redaction enables you to mask (redact) data that is returned from queries issued by low-privileged users or applications. You can redact column data by using one of the following methods:

Full redaction. You redact all of the contents of the column data. The redacted value returned to the querying application user depends on the data type of the column. For example, columns of the NUMBER data type are redacted with a zero (0), and character data types are redacted with a single space.
Partial redaction. You redact a portion of the column data. For example, you can redact most of a Social Security number with asterisks (*), except for the last 4 digits.
Regular expressions. You can use regular expressions to look for patterns of data to redact. For example, you can use regular expressions to redact email addresses, which can have varying character lengths. It is designed for use with character data only.
Random redaction. The redacted data presented to the querying application user appears as randomly generated values each time it is displayed, depending on the data type of the column.
No redaction. The None redaction type option enables you to test the internal operation of your redaction policies, with no effect on the results of queries against tables with policies defined on them. You can use this option to test the redaction policy definitions before applying them to a production environment.

Oracle Database applies the redaction at runtime, at the moment application users attempt to access the data (that is, at query-execution time). This solution works well in a dynamic production system in which data is constantly changing. During the time that the data is being redacted, all of the data processing is performed normally, and the back-end referential integrity constraints are preserved.

Data redaction can help you to comply with industry regulations such as Payment Card Industry Data Security Standard (PCI DSS) and the Sarbanes-Oxley Act.

3.2 Who Can Create Oracle Data Redaction Policies?

To create redaction policies, you must have the EXECUTE privilege on the DBMS_REDACT PL/SQL package. You do not need any privileges to access the underlying tables or views that will be protected by the policy.

3.3 When to Use Oracle Data Redaction

Use Oracle Data Redaction when you must disguise sensitive data that your applications and application users must access. Data Redaction enables you to easily disguise the data using several different redaction styles.

Oracle Data Redaction is ideal for situations in which you must redact specific characters out of the result set of queries of Personally Identifiable Information (PII) returned to certain application users. For example, you may want to present a U.S. Social Security number that ends with the numbers 4320 as ***-**-4320.

3.4 Benefits of Using Oracle Data Redaction

The benefits of using Oracle Data Redaction to protect your data are as follows:

You have different styles of redaction from which to choose.
Because the data is redacted at runtime, Data Redaction is well suited to environments in which data is constantly changing.
You can create the Data Redaction policies in one central location and easily manage them from there.
The Data Redaction policies enable you to create a wide variety of function conditions based on SYS_CONTEXT values, which can be used at runtime to decide when the Data Redaction policies will apply to the results of the application user's query.

3.5 How Oracle Data Redaction Affects the SYS and SYSTEM Schemas

Both users SYS and SYSTEM automatically have the EXEMPT REDACTION POLICY system privilege. (SYSTEM has the EXP_FULL_DATABASE role, which includes the EXEMPT REDACTION POLICY system privilege.) This means that the SYS and SYSTEM users can always bypass any existing Oracle Data Redaction policies, and will always be able to view data from tables (or views) that have Data Redaction policies defined on them. You cannot create Data Redaction policies on objects in the SYS and SYSTEM schemas, which for the SYS schema, includes almost all of the dictionary tables and views.

See Also:

Chapter 6, "Guidelines for Using Oracle Data Redaction" for additional guidelines about roles that bypass Data Redaction policies

3.6 How Data Redaction Works with Nested Functions, Inline Views, and the WHERE Clause

The redaction that Oracle Data Redaction policies perform work as follows:

Nested functions are redacted innermost. For example, in SELECT SUM(AVG(TO_NUMBER(((X))) FROM HR.EMPLOYEES WHERE ..., the TO_NUMBER function is redacted first, followed by AVG, and then last the SUM function.
Inline views are redacted outermost. For example, in SELECT XYZ … AS SELECT A… AS SELECT B… AS SELECT C…, SELECT XYZ is redacted first, followed by AS SELECT A, then AS SELECT B, and so on. AS SELECT C is redacted last.
The WHERE clause is never redacted. Data Redaction redacts only data in the column SELECT list.

3.7 Using SQL Expressions to Build Reports That Contain Redacted Values

You can use SQL expressions to build reports that are based on columns that have Oracle Data Redaction policies defined on them. The values used in the SQL expression will be redacted. This redaction occurs in such a way that the redaction takes place before the SQL expression is evaluated: the result value that is displayed in the report is the end result of the evaluated SQL expression over the redacted values, rather than the redacted result of the SQL expression as a whole.

For example, suppose you create the following Data Redaction policy for the HR.EMPLOYEES table, which will replace the first 4 digits of the value from the SALARY column with the number 9 and the first digit of the value from the COMMISSION_PCT column with a 9.

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema          => 'HR', 
   object_name            => 'EMPLOYEES', 
   column_name            => 'SALARY',
   column_description     => 'emp_sal_comm shows employee salary and commission',
   policy_name            => 'redact_emp_sal_comm', 
   policy_description     => 'Partially redacts the emp_sal_comm column',
   function_type          => DBMS_REDACT.PARTIAL,
   function_parameters    => '9,1,4',
   expression             => '1=1');
END;
/
BEGIN
 DBMS_REDACT.ALTER_POLICY(
   object_schema          => 'HR',
   object_name            => 'EMPLOYEES',
   policy_name            => 'redact_emp_sal_comm',
   action                 => DBMS_REDACT.ADD_COLUMN,
   column_name            => 'COMMISSION_PCT',
   function_type          => DBMS_REDACT.PARTIAL,
   function_parameters    => '9,1,1',
   expression             => '1=1');
END;
/

Log in to the HR schema and then run the following report, which uses the SQL expression (SALARY + COMMISSION_PCT) to combine the employees' salaries and commissions:

SELECT (SALARY + COMMISSION_PCT) total_emp_compensation
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;

TOTAL_EMP_COMPENSATION
----------------------
                9999.9
               9999.95
              99990.95
...

You can use a variety of SQL expressions for the report, including concatenation. For example:

SELECT 'Employee ID '          || EMPLOYEE_ID ||
       ' has a salary of '     || SALARY || 
       ' and a commission of ' || COMMISSION_PCT || '.' detailed_emp_compensation
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
ORDER BY EMPLOYEE_ID;

DETAILED_EMP_COMPENSATION
-------------------------------------------------------------
Employee ID 150 has a salary of 99990 and a commission of .9.
Employee ID 151 has a salary of 9999 and a commission of .95.
Employee ID 152 has a salary of 9999 and a commission of .95.
...

3.8 Target Use Cases for Oracle Data Redaction

Oracle Data Redaction fulfils common use case scenarios.

This section contains:

Using Oracle Data Redaction with Database Applications
Considerations When Using Oracle Data Redaction with Ad Hoc Database Queries

3.8.1 Using Oracle Data Redaction with Database Applications

Oracle Data Redaction shields sensitive data that is displayed in database applications. Data Redaction is highly transparent to application users because it preserves the original data type and (optionally) the formatting. It is highly transparent to the database because the data remains the same in buffers, caches, and storage—only being changed at the last minute just before SQL query results are returned to the caller. The redaction is enforced consistently across all of the applications that use the same underlying database. You can specify which application users should see only redacted data by checking application user information that is passed into the database through the SYS_CONTEXT function; you can redact data based on attributes of the current database user; and you can implement multiple logical conditions within a given redaction policy. In addition, Data Redaction is implemented in a way that minimizes performance overhead. These characteristics make Oracle Data Redaction particularly well suited for usage by a range of applications, analytics tools, reporting tools, and monitoring tools that share common production databases. Although its primary target is redaction of production data for applications, Oracle Data Redaction also can be used in combination with Oracle Enterprise Manager Data Masking Pack for protecting sensitive data in testing and development environments.

See Also:

Oracle Database Real Application Testing User's Guide for more information about data masking
"Using Oracle Data Redaction with Oracle Enterprise Manager Data Masking Pack"

3.8.2 Considerations When Using Oracle Data Redaction with Ad Hoc Database Queries

You may encounter situations where it is necessary to redact sensitive data for ad hoc queries that are performed by database users. For example, in the course of supporting a production application, a user may need to run ad hoc database queries to troubleshoot and fix an urgent problem with the application. This is different from the application-based scenarios described in "Using Oracle Data Redaction with Database Applications", which typically use defined database accounts, have fixed privileges, and generate a bounded set of SQL queries.

Even though Oracle Data Redaction is not intended to protect against attacks by database users who run ad hoc queries directly against the database, it can hide sensitive data for these ad hoc query scenarios when you couple it with other preventive and detective controls. Because users may have rights to change data, alter the database schema, and circumvent the SQL query interface entirely, it is possible for them to bypass Data Redaction policies in certain circumstances. You can address this problem by restricting database privileges and by coupling Data Redaction with other Oracle Database security tools, as follows:

Oracle Database Vault can prevent database administrators from performing harmful operations.
Oracle Audit Vault and Database Firewall can:
- Monitor and block malicious database activities.
- Prevent rows from appearing in query results of non-authorized users.
- Alert you about suspicious activity that was audited by the database.

Remember that the Oracle Database security tools are designed to be used together to improve overall security. By deploying one or more of these tools as a complement to Oracle Data Redaction, you can securely redact sensitive data even from users who are running ad hoc queries.

Also, note that Oracle Data Redaction hides sensitive information based on database columns. It works best in use case scenarios where the sensitivity of the data is determined mainly by the column in which it is stored. When an Oracle database displays query results, Data Redaction redacts all of the data in a given column or leaves the column as is, depending on whether the column is touched by a Data Redaction policy and whether all of the conditions in the policy are met.

See Also:

"General Usage Guidelines" for additional general usage guidelines

3.9 Oracle Data Redaction Features and Capabilities

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

This section contains the following topics:

Using Full Data Redaction to Redact All Data
Using Partial Data Redaction to Redact Sections of Data
Using Regular Expressions to Redact Patterns of Data
Using Random Data Redaction to Generate Random Values
Comparison of Full, Partial, and Random Redaction Based on Data Types
Using No Redaction for Testing Purposes

3.9.1 Using Full Data Redaction to Redact All Data

When an Oracle Data Redaction policy that specifies full data redaction is applied to a table or view, the entire contents of the column are redacted. 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-01.

Full redaction is the default and is used whenever a Data Redaction policy specifies the column but omits the function_type parameter setting. 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

You can use the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure to change the full redaction output to different values if you want.

See Also:

3.9.2 Using Partial Data Redaction to Redact Sections of Data

In partial data redaction, you redact portions of the displayed output. You can set the position (also known as the string offset) 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, as described in "Using Regular Expressions to Redact Patterns of Data".

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:

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 start position at which to begin the redaction, the character to use for the redaction, and how many characters to redact. The first example uses a predefined 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.
```
XXX-XX-4320    function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,

***-**-4320    function_parameters => 'VVVFVVFVVVV,VVV-VV-VVVV,*,1,5',
```
Number data types: The partially redacted NUMBER data type Social Security number 987654328 could appear as follows. Both redact the first five digits. The first example uses a predefined shortcut that is designed for Social Security numbers in the NUMBER data type, and the second replaces the first five numbers with the number 9, starting from the first digit.
```
XXXXX4328    function_parameters => DBMS_REDACT.REDACT_NUM_US_SSN_F5,

999994328    function_parameters => '9,1,5',
```
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 to 02 (using the setting d02) and in the second example, the month is redacted to DEC (using m12). The uppercase values show the actual month (M), year (Y), hour (H), minute (M), and second (S).
```
02-AUG-11 10.20.50.000000 AM   function_parameters  =>  'Md02YHMS',

29-DEC-11 10.20.50.000000 AM   function_parameters  =>  'm12DYHMS',
```

See Also:

3.9.3 Using Regular Expressions to Redact Patterns of Data

You can use regular expressions to redact very 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, in addition to setting the DBMS_REDACT.ADD_POLICY procedure function_type parameter to DBMS_REDACT.REGEXP, you must 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, which 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 the regexp_pattern setting) with the text [redacted]. The \2 setting refers to the second match group, which is (.+\.[A-Za-z]{2,4}) from the regexp_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
```
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
```
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 shortcuts that enable you to use commonly used regular expressions for telephone numbers, email addresses, and credit card numbers.

3.9.4 Using 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). The length of the redacted output depends on the data type of the column. For example, if the column is VARCHAR2(20), then a string of 20 random characters is provided in the redacted output of the query. The length of the redacted output does not depend on the length of the actual value within the row being queried.
Number data types: Numbers are replaced with a different set of numbers. For example, the number 987654321 can be redacted by replacing it with the random number 390871042. Random data redaction of values of the number data types is performed by replacing the value with a random value consisting of the same number of digits, so that the magnitude property of numeric data is preserved.

Be aware that random redaction of number types may not be an appropriate choice if the magnitude of the number is the sensitive attribute of the data (for example, salary data).
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,

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

Table 3-1 compares how the full, partial, and random redaction styles work for various data types. Each cell of this table indicates whether or not redaction is allowed. If redaction is allowed, it describes how the queried value will be redacted before it is displayed.

Table 3-1 Redaction Capabilities Based on the Data Type

Data Type	Notes	Full Redaction	Partial Redaction	Random Redaction
– Oracle Built-In Data Types
Character: `CHAR`, `VARCHAR2` (including long `VARCHAR2`, for example, `VARCHAR2(20000)`), `NCHAR`, `NVARCHAR2`	None	Default redacted value is a single blank space	Supported data type	Supported data type
Number: `NUMBER`, `FLOAT`, `BINARY_FLOAT`, `BINARY_DOUBLE`	None	Default redacted value is zero (`0`).	Supported data type	Supported data type

Raw: `LONG` `RAW`, `RAW`	None	Not a supported data type	Not a supported data type	Not a supported data type
Date-time: `DATE`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, `TIMESTAMP WITH LOCAL TIME ZONE`	None	Default redacted value is `01-01-01` or `01-01-01 01:00:00`.	Supported data type	Supported data type
Interval: `INTERVAL YEAR TO MONTH`, `INTERVAL DAY TO SECOND`	None	Not a supported data type	Not a supported data type	Not a supported data type
Large Object: `BFILE`	None	Not a supported data type	Not a supported data type	Not a supported data type
Large Object: `BLOB`	The No Redaction type (`DBMS_REDACT.NONE`) does not support LOB data types.	Oracle's raw representation of `[redacted]`	Not a supported data type	Not a supported data type
Large Object: `CLOB`, `NCLOB`	The No Redaction type (`DBMS_REDACT.NONE`) does not support LOB data types.	Default redacted value is `[redacted]`.	Not a supported data type	Not a supported data type
Rowid: `ROWID`, `UROWID`	None	Not a supported data type	Not a supported data type	Not a supported data type
–ANSI Data Types
`CHARACTER(n)`, `CHAR(n)`	Converted to `CHAR(n)`	Default redacted value is a single blank space.	Supported data type	Supported data type
`CHARACTER VARYING(n)`, `CHAR VARYING(n)`	Converted to `VARCHAR2(n)`	Default redacted value is a single blank space.	Supported data type	Supported data type
`NATIONAL CHARACTER(n)`, `NATIONAL CHAR(n)`, `NCHAR(n)`	Converted to `NCHAR(n)`	Default redacted value is a single blank space.	Supported data type	Supported data type
`NATIONAL CHARACTER VARYING(n)`, `NATIONAL CHAR VARYING(n)`, `NCHAR VARYING(n)`	Converted to `NVARCHAR2(n)`	Default redacted value is a single blank space.	Supported data type	Supported data type
`NUMERIC`[(`p,s`)] `DECIMAL`[(`p,s`)]	Converted to `NUMBER(p,s)`	Default redacted value is zero (`0`).	Supported data type	Supported data type
`INTEGER` `INT` `SMALLINT`	Converted to `NUMBER(38)`	Default redacted value is zero (`0`).	Supported data type	Supported data type
`FLOAT` `DOUBLE PRECISION`	Converted to `FLOAT(126)`	Default redacted value is zero (`0`)	Supported data type	Supported data type
`REAL`	Converted to `FLOAT(63)`	Default redacted value is zero (`0`).	Supported data type	Supported data type
`GRAPHIC` `LONG VARGRAPHIC` `VARGRAPHIC` `TIME`	None	Not a supported data type	Not a supported data type	Not a supported data type
–User-Defined Types	None	Not a supported data type	Not a supported data type	Not a supported data type
–Oracle Supplied Types
Any types, XML types, Oracle Spatial types, Oracle Media types	None	Not a supported data type	Not a supported data type	Not a supported data type

3.9.6 Using No Redaction for Testing Purposes

You can create a Data Redaction policy that does not performs 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.

3.10 Using Oracle Data Redaction with Other Oracle Database Features

If you are using Editions, aggregate functions, or object types, you should be aware of how Oracle Data Redaction works with these features.

This section contains:

Using Oracle Data Redaction with Editions
Using Oracle Data Redaction with Aggregate Functions
Oracle Data Redaction and Object Types

3.10.1 Using Oracle Data Redaction with Editions

You cannot redact editioned views. You also cannot use a redacted column in the definition of any editioned view.

3.10.2 Using Oracle Data Redaction with Aggregate Functions

Because Oracle Data Redaction dynamically modifies the value of each row in a column, certain SQL queries that use aggregate functions cannot take full advantage of database optimizations that presume the row values to be static. In the case of SQL queries that call aggregate functions, it may be possible to notice performance overhead due to redaction.

3.10.3 Oracle Data Redaction and Object Types

You cannot redact object types. This is because Database Redaction cannot handle all of the possible ways that you can configure object types.