Using Expressions to Define Conditions for Data Redaction Policies

The expression parameter in the DBMS_REDACT.ADD_POLICY or DBMS_REDACT.ALTER_POLICY procedures sets the conditions under which those policies apply.

• About Using Expressions in Data Redaction Policies

  The DBMS_REDACT.ADD_POLICY and DBMS_REDACT.ALTER_POLICY expression parameter define a boolean expression that must evaluate to TRUE to enable redaction.

• Supported Functions for Data Redaction Expressions

  You can create expressions that use functions to return specific types of data, such as SYS_CONTEXT namespaces.

• Applying the Redaction Policy Based on User Environment

  You can apply a Data Redaction policy based on the user’s environment, such as the session user name or a client identifier.

• Applying the Redaction Policy Based on Database Roles

  You can apply a Data Redaction policy based on a database role.

• Applying the Redaction Policy Based on Application Express Session States

  You can apply a Data Redaction policy based on an Oracle Application Express (APEX) session state.

• Applying the Redaction Policy Based on Oracle Label Security Label Dominance

  You can set a condition on which to apply a Data Redaction policy based on the dominance of Oracle Label Security labels.

• Managing the Application of Redaction Policies to All Users

  You can manage the application of policies irrespective of the context to any user, with no filtering.

About Using Expressions in Data Redaction Policies

The DBMS_REDACT.ADD_POLICY and DBMS_REDACT.ALTER_POLICY expression parameter define a boolean expression that must evaluate to TRUE to enable redaction.

The expression that is defined in the expression parameter is the default expression for the Oracle Data Redaction policy. If you apply a named policy expression for the columns that will be redacted by the Data Redaction policy, then the named policy expression takes precedence over the expression defined in the Data Redaction policy. If 1=1 is specified for the expression parameter which is a policy expression that evaluates to TRUE, it enables the redaction to be performed so long as the querying user is not exempt from the redaction policy. Using this condition saves CPU time during the fetch operation from a redacted column. By avoiding the need to evaluate any policy expression, the performance of fetching from any column that is protected by a Data Redaction policy is improved.

You can create expressions that make use of other Oracle Database features. For example, you can create expressions that are based on a user’s environment (using the SYS_CONTEXT and XS_SYS_CONTEXT functions), character string functions, the Oracle Label Security label dominance functions, or Oracle Application Express functions.

Follow these guidelines when you write the expression:

• Use only the following operators: AND, OR, IN, NOT IN, =, !=, <>, <, >, >=, <=

• Because the expression must evaluate to TRUE for redaction, be careful when making comparisons with NULL. Remember that in SQL the value NULL is undefined, so comparisons with NULL tend to return FALSE.

• Do not use user-created functions in the expression parameter; this is not permitted.

• Remember that for user SYS and users who have the EXEMPT REDACTION POLICY system or schema privilege, all of the Data Redaction policies are bypassed, so the results of their queries are not redacted. When you exempt a user from a Data Redaction policy, you should be aware of how the exemption is affected by users with specific privileges (such as the DBA role) and by Oracle Data Pump.

Related Topics

• Exemption of Users from Oracle Data Redaction Policies

• Oracle Data Pump Security Model for Oracle Data Redaction

Supported Functions for Data Redaction Expressions

You can create expressions that use functions to return specific types of data, such as SYS_CONTEXT namespaces.

• Expressions Using Namespace Functions

  You can use the SYS_CONTEXT and XS_SYS_CONTEXT namespace functions in Data Redaction expressions.

• Expressions Using the SUBSTR Function

  You can use the SUBSTR function, which returns portion (such as characters 1–3) of the character string specified, in Data Redaction expressions. The first parameter must be a constant string or a call to the SYS_CONTEXT function or the XS_SYS_CONTEXT function.

• Expressions Using Length of Character String Functions

  You can use functions that return the length of character strings, in Data Redaction expressions.

• Expressions Using Oracle Application Express Functions

  You can use Oracle Application Express functions in Data Redaction expressions.

• Expressions Using Oracle Label Security Functions

  You can use Oracle Label Security functions with Data Redaction expressions.

Expressions Using Namespace Functions

You can use the SYS_CONTEXT and XS_SYS_CONTEXT namespace functions in Data Redaction expressions.

Namespace Function	Description
SYS_CONTEXT	Returns the value associated with a namespace. The following namespace functions are valid: USERENV (default namespace), which includes values such as SESSION_USER and CLIENT_IDENTIFIER. SYS_SESSION_ROLES, which contains attributes for each role XS$SESSION, which contains attributes for the user session. User-defined namespaces, but these must exist in the DBA_CONTEXT catalog view before the policy expression is created.
XS_SYS_CONTEXT	Similar to SYS_CONTEXT but designed for an Oracle Real Application Security environment. XS_SYS_CONTEXT supports the same namespaces that SYS_CONTEXT supports.

Related Topics

• Oracle AI Database SQL Language Reference

• Oracle AI Database Real Application Security Administrator’s and Developer’s Guide

Expressions Using the SUBSTR Function

You can use the SUBSTR function, which returns portion (such as characters 1-3) of the character string specified, in Data Redaction expressions. The first parameter must be a constant string or a call to the SYS_CONTEXT function or the XS_SYS_CONTEXT function.

SUBSTR String Function	Description
SUBSTR	Returns a portion of the input char value, beginning at character position, substring_length characters long. SUBSTR calculates length using characters as defined by the input character set.
SUBSTRB	Returns the specified portion of the input value in bytes
SUBSTRC	Returns the specified portion of the input value in Unicode complete characters
SUBSTR2	Returns the specified portion of the input value in UCS2 code points
SUBSTR4	Returns the specified portion of the input value in UCS4 code points

Related Topics

• Oracle AI Database SQL Language Reference

Expressions Using Length of Character String Functions

You can use functions that return the length of character strings, in Data Redaction expressions.

Oracle Database also checks that the arguments to each of these operators is either a constant string or a call to the SYS_CONTEXT or XS_SYS_CONTEXT function.

Character String Function	Description
LENGTH	Returns the length of the input char value. LENGTH calculates length using characters as defined by the input character set.
LENGTHB	Returns the length of the input value in bytes
LENGTHC	Returns the length of the input value in Unicode complete characters
LENGTH2	Returns the length of the input value in UCS2 code points
LENGTH4	Returns the length of the input value in UCS4 code points

Related Topics

• Oracle AI Database SQL Language Reference

Expressions Using Oracle Application Express Functions

You can use Oracle Application Express functions in Data Redaction expressions.

Oracle Application Express Function	Description
V	Returns the session state for an item. It is a wrapper for the APEX_UTIL.GET_SESSION_STATE function
NV	Returns the numeric value for a numeric item. It is a wrapper for the APEX_UTIL.GET_NUMERIC_SESSION_STATE function

Related Topics

• Oracle APEX API Reference

Expressions Using Oracle Label Security Functions

You can use Oracle Label Security functions with Data Redaction expressions.

Oracle Label Security Function	Description
LBACSYS.OLS_LABEL_DOMINATES	Checks if the session label of an Oracle Label Security policy dominates or is equal to another OLS label
OLS_DOMINATES, DOMINATES, and S_DOM*	Checks if one OLS label is dominant to a second OLS label. DOMINATES and S_DOM, which were used in releases earlier than Oracle Database 12.1, were deprecated in that release. Use the OLS_DOMINATES or OLS_DOM function instead.
OLS_DOM*	Checks if one OLS label is dominant to a second OLS label
DOM*	Checks if one OLS label is dominant to a second OLS label
OLS_STRICTLY_DOMINATES*	Checks if one OLS label is dominant to a second OLS label and is not equal to it
STRICTLY_DOMINATES*	Checks if one OLS label is dominant to a second OLS label and is not equal to it
SA_UTL.DOMINATES*	Checks if one OLS label dominates a second OLS label or if the session label for a given OLS policy dominates an OLS label
SA_UTL.CHECK_READ	Checks if a user can read a policy-protected row
SA_UTL.NUMERIC_LABEL	Returns the current session OLS label
CHAR_TO_LABEL	Converts a character string to an OLS label tag
SA_SESSION.LABEL	Returns the label that is associated with the specified OLS policy

• Oracle Data Redaction checks that their parameters are either constants or calls to only one of the SA_UTL.NUMERIC_LABEL, CHAR_TO_LABEL, and SA_SESSION.LABEL functions, and that the arguments to those functions are constant.

Related Topics

• Using Oracle Label Security Administrator’s Guide

Applying the Redaction Policy Based on User Environment

You can apply a Data Redaction policy based on the user’s environment, such as the session user name or a client identifier.

Use the USERENV namespace of the SYS_CONTEXT function in the DBMS_REDACT.ADD_POLICY expression parameter to apply the policy based on a user’s environment.

For example, to apply the policy only to the session user name psmith:

expression  => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''PSMITH'''

Related Topics

• Oracle AI Database SQL Language Reference

Applying the Redaction Policy Based on Database Roles

You can apply a Data Redaction policy based on a database role.

Use the SYS_SESSION_ROLES namespace in the SYS_CONTEXT function to apply the policy based on a user role.

This namespace contains attributes for each role. The value of the attribute is TRUE if the specified role is enabled for the querying application user; the value is FALSE if the role is not enabled. If the expression is TRUE, redacted data is returned to the user. If the expression is FALSE, actual data is returned to the user.

For example, suppose you wanted only supervisors to be allowed to see the actual data. The following example shows how to use the DBMS_REDACT.ADD_POLICY expression parameter to set the policy to show the actual data to any application user who has the supervisor role enabled, but redact the data for all of the other application users.

expression  => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''SUPERVISOR'') = ''FALSE'''

You can specify multiple roles with the expression parameter. The following example checks for the presence of the SUPERVISOR, CLERK, and TEMP_WORKER roles. Actual data will be displayed to a user with the SUPERVISOR role. Users who have the CLERK or TEMP_WORKER roles will see redacted data.

expression => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''SUPERVISOR'') = ''FALSE''
OR SYS_CONTEXT(''SYS_SESSION_ROLES'',''CLERK'') = ''TRUE''
OR SYS_CONTEXT(''SYS_SESSION_ROLES'',''TEMP_WORKER'') = ''TRUE'''

All roles are disabled in any named PL/SQL block that runs with definer’s rights. Procedures and functions are created with definer’s rights by default. For the policy expression with SYS_SESSION_ROLES to take effect, create the procedure or function to be invoker’s rights so that the enabled roles are effective. Alternatively, you can use code based access controls to grant a role to a program unit (function, package, or procedure), allowing the program unit to use the role in either definer’s or invoker’s rights. For more information, see Managing Security for Definer’s Rights and Invoker’s Rights in the Oracle AI Database Security Guide.

Related Topics

• Roles Used in Named Blocks with Definer’s Rights

• Roles Used in Named Blocks with Invoker’s Rights and Anonymous PL/SQL Blocks

• Oracle AI Database SQL Language Reference

Applying the Redaction Policy Based on Application Express Session States

You can apply a Data Redaction policy based on an Oracle Application Express (APEX) session state.

Use either of the following public Application Express APIs in the DBMS_REDACT.ADD_POLICY expression parameter to apply the policy on an Oracle Application Express session state:

• V, which is a synonym for the APEX_UTIL.GET_SESSION_STATE function

• NV, which is a synonym for the APEX_UTIL.GET_NUMERIC_SESSION_STATE function

If you want redaction to take place when the querying user is not within the context of an APEX application (when the query is issued from outside the APEX framework, for example directly through SQL*Plus), then use an IS NULL clause as shown in the following example.

For example, to set the DBMS_REDACT.ADD_POLICY expression parameter when the query is issued from outside the APEX framework, use an IS NULL clause, as follows:

expression => 'V(''APP_USER'') != ''[e-mail]'' or V(''APP_USER'') is null'

You can, for example, use these functions to redact data based on a job or a privilege role that is stored in a session state in an APEX application.

Related Topics

• Oracle APEX API Reference

Applying the Redaction Policy Based on Oracle Label Security Label Dominance

You can set a condition on which to apply a Data Redaction policy based on the dominance of Oracle Label Security labels.

Use the public standalone function OLS_LABEL_DOMINATES to check the dominance of a session label.

This function returns 1 (TRUE) if the session label of the specified policy_name value dominates or is equal to the label that is specified by the label parameter; otherwise, it returns 0 (FALSE).

For example, to apply a Data Redaction policy only in cases where the session label for the policy hr_ols_pol does not dominate nor is equal to label hs:

expression  => 'OLS_LABEL_DOMINATES (''hr_ols_pol'',''hs'') = 0'

Related Topics

• Oracle Label Security Administrator’s Guide

Managing the Application of Redaction Policies to All Users

You can manage the application of policies irrespective of the context to any user, with no filtering.

However, be aware that user SYS and users who have the EXEMPT REDACTION POLICY system or schema privilege are always exempt from Oracle Data Redaction policies.

• To apply a policy to users who are not SYS or have been granted the EXEMPT REDACTION POLICY privilege, write the DBMS_REDACT.ADD_POLICY expression parameter to evaluate to TRUE. For example, to '2=2'.

  To optimize performance, set the expression to '1=1' as this causes Oracle Database to not re-evaluate the policy expression for each row. As a result, the performance of fetching from the target column is improved.

  expression  => '1=1'

• To disable a policy to users, alter the expression parameter to evaluate to FALSE. For example, to '1=2'. Having the expression evaluate to FALSE prevents the policy from being applied and temporarily disables the policy. This can be useful for testing and auditing.

  To optimize performance, set the expression to '1=0' as this causes Oracle Database to not re-evaluate the policy expression for each row. As a result, the performance of fetching from the target column is improved.

  expression  => '1=0'

Related Topics

• Exemption of Users from Oracle Data Redaction Policies