Oracle Data Redaction

Available with the Oracle Advanced Security license, Oracle Data Redaction allows you to mask (redact) data returned from queries issued by applications.?

You can redact column data using one of the following methods:

1- Full redaction:

You extract all the contents of the column data.?The redaction value returned to the query application user depends on the data type of the column.?For example, columns of the NUMBER data type are rearranged with zero (0) and character data types with a single space.

Full redaction is the default and is used when it specifies a Data Redaction policy column but bypasses the function_type parameter setting.?When you run the DBMS_REDACT.ADD_POLICY procedure, you enter the following setting to set the function_type parameter for full redaction:

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.

2- Partial redaction:

You remove a part of the column data.?For example, you can remove an asterisk (*) for all but the last 4 digits of the TR ID number.

To specify partial redaction, you must set the DBMS_REDACT.ADD_POLICY procedure function_type to DBMS_REDACT.PARTIAL and use the function_parameters parameter to define the partial redaction behavior.

The output displayed for partial data redaction may be as follows:

Character Data Types :?For the representation as

XXX-XX-4320, function_parameters => DBMS_REDACT.REDACT_US_SSN_F5.?For display as

***-**-4320, function_parameters => 'VVVFVVFVVVV,VVV-VV-VVVV,*,1.5' should be.

Number Data Types :?To display as

XXXXX4328, function_parameters => DBMS_REDACT.REDACT_NUM_US_SSN_F5.

For display as 999994328,?function_parameters => should be '9,1.5'.

Date-time Data Types:

02-AUG-11 10.20.50.000000 For AM?display, function_parameters => 'Md02YHMS' should be.

29-DEC-11 For 10.20.50.000000 AM?display, function_parameters => 'm12DYHMS' must be in the form.

3- Regular expressions:

Can use regular expressions to search for data patterns to be corrected.?For example, you can use regular expressions to extract email addresses that can have varying character lengths.?It is intended for use with character data only.

You can use regular expressions to extract specific data within a column data value based on a pattern search.?For example, you can edit the username of the e-mail addresses so that only the domain is shown (for example, you can replace the support section in the [email protected] e-mail address with [redacted] so that it appears as [redacted] @databani.gen.tr ) .?To perform the redaction, set the function_type parameter in the DBMS_REDACT.ADD_POLICY procedure to DBMS_REDACT.REGEXP, and then you can use the following parameters to generate the regular expression:

• Search expression in data
• regexp_pattern => '(.+)@(.+\.[A-Za-z]{2,4})'
• Expression to replace value matching regexp_pattern setting
• regexp_replace_string => '[redacted]@\2'
• Starting position for string search string
• regexp_position => DBMS_REDACT.RE_BEGINNING (if first character)
• Type of search and replace operation to be performed
• regexp_occurrence => DBMS_REDACT.RE_ALL
• Default matching behavior for search and replace operation
• is regexp_match_parameter => (sets i expression to be case insensitive)

4- Random Redaction:

Each time the application user queries the edited data, randomly generated values are displayed depending on the type of data.

The output displayed for random values varies according to the data type of the redacted column, as follows:

-Character data types: Random output is a mixture of characters (for example, XYZ[P{\pjkQWcM).?It behaves differently for CHAR and VARCHAR2 data types.

-Number data types: Each real number value is rearranged by replacing it with a random, non-negative number that is the absolute value of the real data.?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 numbers 12345678, 13579, 0, or 987654320, but not with any of the numbers 987654321, 99987654321, or -1.?The number -123 can be redacted by replacing 122 with 0 or 83, but not with any of the numbers 123, 1123 or -2.

-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 the actual data.

The setting for using random redaction is as follows:

function_type => DBMS_REDACT.RANDOM
        

5- No redaction:

Allows you to test the internal workings of your redaction policies against tables with defined policies without any impact on the results of queries.?You can use this option to test redaction policy definitions before applying them to a production environment.

You can create a non-redacting Data Redaction policy.?This is useful when you have a base table that has been adjusted, but you want a particular application user to always have a view that shows actual data.?You can create a new view of the redacted table and then define a Data Redaction policy for that view.?The policy still exists in the base table, but as long as the function_type is used to create a policy with the setting DBMS_REDACT.NONE, no fixes are made when the application queries using the view.

6- Redaction that returns NULL value (NULLIFY redaction):

You can create Oracle Data Redaction policies that return null values for the displayed value of a table or view column.?Strips all data in a column and replaces it with null values when the function_type parameter value is set to DBMS_REDACT.NULLIFY.?

The DBMS_REDACT.ADD_POLICY procedure can create a redaction policy that performs full redaction and displays null values for redacted columns.

You can use this function type with all supported column types that the DBMS_REDACT.FULL function type supports.?It also supports CLOB and NCLOB data types.?To use the DBMS_REDACT.NULLIFY function, the COMPATIBLE parameter must first be set to a minimum of 12.2.0.0.0.

A policy of function type DBMS_REDACT.NULLIFY can be created as follows:?

BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'hr',
object_name => 'employees',
column_name => 'commission_pct',
policy_name => 'nullify_com_pct',
function_type => DBMS_REDACT.NULLIFY,
expression => '1=1');
END;
/
        

When to Use Oracle Data Redaction

Use Oracle Data Redaction when you need to hide sensitive data that your applications and application users need access to.?Data Redaction allows you to easily hide data using several different redaction styles.

Oracle Data Redaction is ideal for situations where you need to extract certain characters from the resultant set of queries of Personally Identifiable Information returned to specific application users.?For example, you may want to present a TR ID number ending with the numbers 4321 as *******4321.

Oracle Data Redaction is especially suitable for call center applications and other read-only applications.?Be careful when using Oracle Data Redaction with applications that update back to the database, because redacted data may be written back to this database.

Benefits of Using Oracle Data Redaction

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

1. You get different editing styles to choose from.
2. ?Data Redaction is well suited to environments where data is constantly changing, as data is redacted at runtime.
3. You can create and easily manage Data Redaction policies in one central location.
4. Data Redaction policies allow you to create a wide variety of functional conditions based on SYS_CONTEXT values that can be used at runtime to decide when to apply Data Redaction policies to the results of the application user's query.

Considerations When Using Oracle Data Redaction with Ad Hoc Database Queries

You may encounter situations where it is appropriate to extract sensitive data for?ad hoc queries?performed by database users .?For example, in the process 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.

While Oracle Data Redaction is not designed to prevent data exposure to database users running ad-hoc queries directly against the database, it can provide an additional layer to reduce the chance of accidental data disclosure.?It is possible for a malicious user to bypass Data Redaction policies in certain circumstances, as such users may have rights to modify data, modify the database schema, and bypass the SQL query interface entirely.

Note that 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 safely increase your overall security posture.

Oracle Data Redaction Policies

The Oracle Data Redaction policy defines the conditions under which redaction must occur for a table or view.

A Data Redaction policy has the following features:

• The Data Redaction policy defines what kind of redaction should be done, how it should be redacted, and when it should take place.?Oracle Database performs the redaction at execution time, just before the data is returned to the application.
• A Data Redaction policy can omit values completely, partially omit values, or redact values randomly.?Also, when you want to test your policies in a test environment, you can define a Data Redaction policy to not extract any data.
• A Data Redaction policy can be defined by a policy statement that allows different application users to be presented with either corrected data or actual data, depending on whether the policy expression returns TRUE or FALSE.?Correction occurs when the logical result of evaluating the policy expression is TRUE.?For security reasons, the functions and operators available in the policy expression are limited to SYS_CONTEXT and others.?User-created functions are not allowed.?Policy statements can use the SYS_SESSION_ROLES namespace with the SYS_CONTEXT function to control active roles.

Policies can be managed with the DBMS_REDACT package.?The functions in this package are:

DBMS_REDACT.ADD_POLICY :?Adds policy to table or view.

DBMS_REDACT.ALTER_POLICY :?Edit a policy.

DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES :?Globally updates the full redaction value for a given data type.?The database instance must be restarted before the updated values can be used.

DBMS_REDACT.ENABLE_POLICY :?Enables a policy.

DBMS_REDACT.DISABLE_POLICY :?Disables a policy.

DBMS_REDACT.DROP_POLICY :?Deletes a policy.

DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL :?Applies a Data Redaction policy statement to a table or view column

DBMS_REDACT.CREATE_POLICY_EXPRESSION :Creates a Data Redaction policy statement

DBMS_REDACT.DROP_POLICY_EXPRESSION :?Deletes a Data Redaction policy statement

DBMS_REDACT.UPDATE_POLICY_EXPRESSION :?Updates a Data Redaction policy statement

?

Status of Oracle Data Redaction policies for SYS and SYSTEM users

Both SYS and SYSTEM users automatically have “EXEMPT REDACTION POLICY” system privilege.?The SYSTEM user also has the EXP_FULL_DATABASE role, which includes the EXEMPT REDACTION POLICY system privilege.?This means that SYS and SYSTEM users can always bypass existing Oracle Data Redaction policies and always view data from tables (or views) with Data Redaction policies defined on them.

Example :

In the example below, a policy named KISI_Redaction is created for the TCKIMLIKNO column in the KISI table in the ADURUOZ schema, if the connected user is not PROD.

– Adding a policy

BEGIN
DBMS_REDACT.ADD_POLICY (
OBJECT_SCHEMA => 'ADURUOZ',
object_name => 'KISI',
column_name => 'TCKIMLIKNO',
policy_name => 'KISI_Redaction',
function_type => DBMS_REDACT.FULL,
expression => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''PROD'' ');
END;
/
        

?– Changing the policy (Allows editing for SICILNO in addition to the TCKIMLIKNO field in the KISI_Redaction policy)

BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'ADURUOZ',
object_name => 'KISI',
policy_name => 'KISI_Redaction',
action => DBMS_REDACT.ADD_COLUMN,
column_name => 'SICILNO',
function_type => DBMS_REDACT.FULL);
END;
/
        

– Deleting the policy

BEGIN
DBMS_REDACT.DROP_POLICY (
OBJECT_SCHEMA => 'ADURUOZ',
object_name => 'KISI',
policy_name => 'KISI_Redaction');
END;
/
        

You can view the policies created with the following query.

SQL> SET LINESIZE 200
SQL> COLUMN object_owner FORMAT A20
SQL> COLUMN object_name FORMAT A30
SQL> COLUMN expression FORMAT A30
SQL> SELECT object_owner,object_name,policy_name,expression,enable,policy_description FROM redaction_policies ORDER BY 1, 2, 3;

OBJECT_OWNER OBJECT_NAME POLICY_NAME EXPRESSION ENABLE POLICY_DESCRIPTION
-------------------- ------------------------------ ------------------------------ ---------------------------------------------------- ------- --------------------
ADURUOZ KISI KISI_Redaction SYS_CONTEXT('USERENV', 'SESSION_USER') != 'PROD' YES