Skip Headers
Oracle® Database Advanced Security Administrator's Guide
11g Release 2 (11.2)

E40393-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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:

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 DML and DDL Operations

Although you will use Oracle Data Redaction primarily for redacting the displayed results of application queries, you should understand of how it affects DML and DDL operations, especially if you have users who issue ad-hoc SQL against tables with redacted columns.

Note the following:

  • Oracle Data Redaction treats the RETURNING INTO clause of a DML statement as a query, even though the result is not displayed. The result that is sent to the buffer is what would have been displayed had the RETURNING INTO clause been run as an ordinary SQL query, rather than as part of a DML statement. If your application performs further processing on the buffer that contains the RETURNING INTO value, then consider changing the application because it may not expect to find a redacted value in the buffer.

  • If a redacted column appears as the source in a DML or DDL operation, then Oracle Data Redaction considers this as an attempt to circumvent the policy and prevents it with an ORA-28081: Insufficient privileges - the command references a redacted object error unless you have the EXEMPT REDACTION POLICY system privilege. Internally, Oracle Data Pump issues these kinds of operations, so you may also need to grant the EXEMPT REDACTION POLICY system privilege to a user if they need to perform schema-level exports of tables that have redacted columns.

3.6 How Oracle Data Redaction Affects the SYS and SYSTEM Schemas

Administrative users such as the SYS user and all users with the SYSDBA administrative privilege who have authenticated using the AS SYSDBA flag automatically have the EXEMPT REDACTION POLICY system privilege. This means that these administrative users will always be able to view actual data from tables (or views) that have Data Redaction policies defined on them. You cannot create Data Redaction policies on objects in the SYS schema, which includes almost all of the dictionary tables and views.

User SYSTEM does not automatically have the EXEMPT REDACTION POLICY system privilege. This means that when this user queries tables and views that have Data Redaction policies defined on them, the redacted values will appear, not the actual data.

See Also:

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

3.7 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.8 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.9 Target Use Cases for Oracle Data Redaction

Oracle Data Redaction fulfils common use case scenarios.

This section contains:

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

3.9.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 generate a bounded set of SQL queries, use defined database accounts, and have fixed privileges.

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 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 the rows of data queried from a given column if an enabled Data Redaction policy is defined for the column and the policy expression evaluates to TRUE; otherwise the column's actual data is displayed.

See Also:

"General Usage Guidelines" for additional general usage guidelines

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

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

3.10.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',
    

3.10.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.10.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). It behaves differently for the CHAR and VARCHAR2 data types, as follows:

    • CHAR data type: The redacted output is in the same character set with the byte length as of the column definition. 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: The redacted output is in the same character set of the same byte length as of the actual string, up to a maximum of the column width. Bytes beyond this maximum are zeroed out. For example, if the column is VARCHAR2(20) having an actual width of 12, then a string of 12 random characters (not 20) is provided in the redacted output of the user's query.

  • Number data types: Numbers are replaced with a different set of numbers, preserving the data type, whereas the number lengths are not preserved. For example, the number 987654321 can be redacted by replacing it with the number 90871042.

    Random data redaction guarantees that the resulting number does not exceed the precision of the original data (not the data type).

  • 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.10.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.10.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.11 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:

3.11.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.11.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.11.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.