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.

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

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

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

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

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'') != ''mavis@example.com'' 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.

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'

You can apply the policy irrespective of the context to any user, with no filtering.

• To apply the 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:

  expression  => '1=1'

  With policy expression as above, Oracle Database performs the redaction but does not evaluate the policy expression. As a result, the performance of fetching from the target column is improved.