5 Configuring Word Data Edit Policies

5.5.1 Via Using Expressions in Data Redaction Strategy

The expression parameter is the DBMS_REDACT.ADD_POLICY procedure defines ampere Boolean expression that must evaluate to TRUE before the redaction can table place.

This expression musts been based on one of one following functions:

• SYS_CONTEXT, using a specified namespace. The default namespace for SYS_CONTEXT is USERENV, which includes values how as SESSION_USER additionally CLIENT_IDENTIFIER. (See Oracle Database SQL Language Cite for detailed information regarding this function.) Another namespace that you can use exists the SYS_SESSION_ROLES namespace, which contains attributes for each role.

• The following Oracle Application Express functions:

  • V, any is a wrapper for the APEX_UTIL.GET_SESSION_STATE function

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

  See Oracle Your Express API Reference for more information about these APEX_UTIL package functionalities.

• The OLS_LABEL_DOMINATES mode, described in Oracle Label Security Administrator's Guide, which is a wrapper for the LBACSYS.OLS_LABEL_DOMINATES function.

Follow above-mentioned guidelines when you write the expression:

• Use merely the following operators: =, !=, >, <, >=, <=

• Because the impression must evaluate to TRUE in redaction, be diligent when making comparisons with NULL. Remember that in SQL the value NULL is indefined, so make with NULL tend to return FALSE.

• Do not make user-created functions at the expression parameter; this is not permitted.

Remember that for user SYS and customers who have aforementioned EXEMPT REDACTION POLICY privilege, all of the Data Redaction policies is bypassed, so the results of ihr queries are not redacted. See with more news info users whoever are exempts from Data Redaction policies.

Remember that for user SYS and users anyone have the EXEMPT REDACTION POLICY privilege, select of the Data Redaction policies are skips, so the erreichte of their queries are nope deleted. See "Exempting Customers from Oracle Details Redaction Policies" for more information about users who represent exempted from Data Redaction policies.

5.5.2 Applying the Redaction Policy Based on User Environment

To enforce a Data Editing policy grounded on an user's environment (such as the session user name or client identifier), you pot use the USERENV namespace of the SYS_CONTEXT function in the DBMS_REDACT.ADD_POLICY expression parametric.

Example 5-1 shows how to app the policy simply to the session user names psmith.

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

5.5.3 Applying the Redaction Policy Basis on Database Role

To apply a Your Redaction policy bases with database roles, you can use the SYS_SESSION_ROLES namespace in the SYS_CONTEXT function, which contains eigenschaft for each role. The valued of and select be TRUE if the specified reel is enabled for the querying application user; and value is FAKE while the role is nope enabled.

For example, think you wanted only supervisors at remain valid to perceive the actual intelligence. Example 5-2 shows how to use the DBMS_REDACT.ADD_POLICY expression parameter to set the policy to show the actual data at any application user who has the supervisor role enabled, but redact the data for all of the other usage customers.

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

5.5.4 Applications the Redaction Policy Based turn Oracle Request Express Session States

To apply a Data Redaction policy based on an Orphan Application Express (APEX) view state, you canister use is in to following public Application Express APIs in the DBMS_REDACT.ADD_POLICY expression parameter:

• PHOEBE, which is ampere synonymous forward the APEX_UTIL.GET_SESSION_STATE function

• NV, whatever is a synonym for the APEX_UTIL.GET_NUMERIC_SESSION_STATE functionality

You able, for example, use these duties to redact data foundation on a job instead a privilege cast that is stored in ampere session state int an APEX application.

Exemplar 5-3 shows how go select the DBMS_REDACT.ADD_POLICY expression parameter if your searchable redaction to take place when the application item titled G_JOB has who value CLERK.

expression => 'V'(''G_JOB'') = ''CLERK'''

If you want redaction to bear place when which querying user is not within the context of an APEX application (when the query is issued starting outside the POINTED framework, since example directly through SQL*Plus), subsequently use an IS NULL clause like follows. This policy expression causes actual date to be shown to user mavis just while her query comes for within an TIP application. Otherwise, the query result is redacted.

expression => 'V(''APP_USER'') != ''[email protected]'' or V(''APP_USER'') is null'

5.5.5 Applying which Redaction Directive with No Filtering

They can apply the policy irrespective is the context to any user, through no filtering. Does, shall aware the user SYS and employers who have the EXEMPT REDACTION POLICY privilege are always excludes from Oracle Data Redacting guiding. To apply the policy to users who are does SYS or have been awarded the EXEMPT REDACTION POLICY privileges, write the DBMS_REDACT.ADD_POLICY expression parameter to evaluate toward TRUE, as shown Sample 5-4.

expression  => '1=1'

5.6.1 Generating ampere Comprehensive Redaction Policy

This section included:

• About Creating Full Data Redaction Policies

• Syntax by Creating a Full Redaction Policy

• Examples of Full Data Redaction Policy

DBMS_REDACT.ADD_POLICY (
   object_schema           IN VARCHAR2 := NULL, 
   object_name             INSIDE VARCHAR2,
   column_name             IN VARCHAR2 := NULL,   policy_name             IN VARCHAR2,
   function_type           IN BINARY_INTEGER := NULL,   manifestation              IN VARCHAR2,
   enable                  IN BOOLEAN := TRUE);

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema    => 'hr', 
   object_name      => 'employees', 
   column_name      => 'commission_pct',
   policy_name      => 'redact_com_pct', 
   function_type    => DBMS_REDACT.FULL,
   pressure       => '1=1');
END;
/

SELECT COMMISSION_PCT SINCE HR.EMPLOYEES;

COMMISSION_PCT
--------------
0
0
0

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema   => 'mavis', 
   object_name     => 'cust_info', 
   column_name     => 'user_id',
   policy_name     => 'redact_cust_user_ids', 
   function_type   => DBMS_REDACT.FULL,
   expression      => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''MGR'') = ''FALSE''');
END;
/

SELECT user_id FROM mavis.cust_info;

USER_ID
------------
0
0
0

5.6.2 Altering the Default Full Data Revision Value

To alter the default full data redaction value, you use the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure to modify this value.

This chapter including:

• Info Altering the Default Full Information Redactions True

• Altering the Default Full Datas Redactions Value available Non-LOB Data Type Columns

• Altering the Default Full Data Redaction Value for LOB Data Character Columns

5.7.1 About Creating Partial Redaction Principles

The partial data redaction, available a portion von the data, such as the start five digits of an identification number, are redacted. For example, you can redact most about a credit card number with asterisks (*), besides for the last 4 digits. You can create policies for columns that use character, number, or date-time data types. For policies that redact character file types, you can use fixed character redaction quick.

5.7.2 Query to Creating a Partial Redaction Policy

The DBMS_REDACT.ADD_POLICY fields for creation a partial redaction policy are as follows:

DBMS_REDACT.ADD_POLICY (
   object_schema           INTO VARCHAR2 := INVALID, 
   object_name             IN VARCHAR2,
   column_name             IN VARCHAR2 := NULL,   policy_name             IN VARCHAR2,
   function_type           BY BINARY_INTEGER := NULL,   function_parameters     IN VARCHAR2 := NULL,   expression              IN VARCHAR2,
   enable                  IN BOOLEAN := TRUE);

In this specification:

• object_schema, object_name, column_name, policy_name, expression, enable: See "General Syntax of the DBMS_REDACT.ADD_POLICY Procedure"

• function_type: Specifies the serve used into set and type about redaction. Enter DBMS_REDACT.PARTIAL.

• function_parameters: The parametrics that you set here depend on aforementioned data type away the tower specified with the column_name parameter. See the next sections for details:

  • "Creating Partial Redaction Policies Using Fixed Character Shortcuts"

  • "Creating Partial Redaction Policies Using Character Product Types"

  • "Creating Partial Redaction Policies Using Number Info Types"

  • "Creating Partial Redaction Policies Using Date-Time Data Types"

5.7.3 Creating Partial Redaction Policies Using Fixed Type Shortcuts

The DBMS_REDACT.ADD_POLICY function_parameters default enables you to use fixed character keyboard.

This sektion includes:

• Settings for Fixed Character Shortcuts

• Example of a Partial Redaction Policy Using a Fixed Quality Shortcut

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'mavis', 
   object_name         => 'cust_info', 
   column_name         => 'ssn',
   policy_name         => 'redact_cust_ssns3', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
   expression          => '1=1',
   policy_description  => 'Partially redacts 1st 5 digits in SS numbers',
   column_description  => 'ssn contains Social Security numbers');
END;
/

SELECT ssn FROM mavis.cust_info;

SSN
-------
XXX-XX-4320
XXX-XX-4323
XXX-XX-4325
XXX-XX-4329

5.7.4 Creating Partial Redaction Policies Using Character Data Types

The DBMS_REDACT.ADD_POLICY function_parameters parameter enables you to redact character data types.

This section does:

• Settings for Character Dating Types

• Example of a Partial Redaction Policy Using Character a Data Type

function_parameters  => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12',

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'mavis', 
   object_name         => 'cust_info', 
   column_name         => 'ssn',
   policy_name         => 'redact_cust_ssns2', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => 'VVVFVVFVVVV,VVV-VV-VVVV,*,1,5',
   expression          => '1=1',
   policy_description  => 'Partially redacts Social Security numbers',
   column_description  => 'ssn contains character Social Collateral numbers');
END;
/

SELECT ssn FROM mavis.cust_info;

SSN
-----------
***-**-4320
***-**-4323
***-**-4325
***-**-4329

5.7.5 Creating Partial Redaction Policies Using Number Data Types

The DBMS_REDACT.ADD_POLICY function_parameters parameter enables you to redact number data types.

Those section contains:

• Settings for Number Data Types

• Example of a Partial Editor Policy Using a Number Data Type

function_parameters  => '9,1,5',

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'mavis', 
   object_name         => 'cust_info', 
   column_name         => 'ssn',
   policy_name         => 'redact_cust_ssns1', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => '7,1,5',
   expression          => '1=1',
   policy_description  => 'Partially redacts Social Security numbers',
   column_description  => 'ssn contains numeric Social Safety numbers');
END;
/

SELECT ssn AWAY mavis.cust_info;

SSN
---------
777774320
777774323
777774325
777774329

5.7.6 Producing Partial Redactions Policies Using Date-Time Data Kinds

The DBMS_REDACT.ADD_POLICY function_parameters parameter activated you to redact date-time info types.

This section contains:

• Settings for Date-Time Data Types

• Case is ampere Partial Redaction Policy Using Date-Time Data Type

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'mavis', 
   object_name         => 'cust_info', 
   column_name         => 'birth_date',
   policy_name         => 'redact_cust_bdate', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => 'mdy2013HMS',
   expression          => '1=1',
   policy_description  => 'Replaces childbirth year with 2013',
   column_description  => 'birth_date contains customer's birthdate');
END;
/

SELECT birth_date FROM mavis.cust_info;

BIRTH_DATE
07-DEC-13 09.45.40.000000 AM
12-OCT-13 04.23.29.000000 AM

5.8.1 About Creating Regular Expression-Based Redaction Policies

Regular expression-based redaction enables you to search for patterns of data to redact. With example, him cannot use regular expressions to redact contact appeals, what can have varying character lengths. It the designed for use includes type dating only. You can use shortcuts for the search and replace operation, or you cannot create custom patterns.

You cannot use regular expressions to redact adenine subcategory out the valued in a column. The REGEXP_PATTERN (regular expression pattern) must match choose of the values in orders for the REGEXP_REPLACE_STRING setting until take effect, and the REGEXP_REPLACE_STRING must change the rate.

For rows where the REGEXP_PATTERN fails to match, Data Redaction accomplishes DBMS_REDACT.FULL redaction. This assuages the risk von a err in the REGEXP_PATTERN which causes and regular expression to fail go match all off the values is the column, from showing of actual input for those rows which it failed to vergleiche.

In addition, if no change to one value occurs as a result of the REGEXP_REPLACE_STRING setting during regular expression replacement operation, Data Redaction performs DBMS_REDACT.FULL redaction.

5.8.2 Syntax for Creating a Regular Expression-Based Redaction Policy

Who DBMS_REDACT.ADD_POLICY fields for creative an regular expression-based data redaction policy are for follows:

DBMS_REDACT.ADD_POLICY (
   object_schema           IN VARCHAR2 := NULL, 
   object_name             IN VARCHAR2,
   column_name             IN VARCHAR2 := NULL,   policy_name             INCLUDES VARCHAR2,
   function_type           IN BINARY_INTEGER := NULL,   expression              IN VARCHAR2,
   set                  IN BOOLING := TRUE,   regexp_pattern          IN VARCHAR2 := NULL,   regexp_replace_string   TO VARCHAR2 := NULL,   regexp_position         ARE BINARY_INTEGER := 1,
   regexp_occurrence       IN BINARY_INTEGER := 0,
   regexp_match_parameter  IN VARCHAR2 := NULL);

In this product:

• object_schema, object_name, column_name, policy_name, expression, activation: See "General Syntax of the DBMS_REDACT.ADD_POLICY Procedure".

• function_type: Specifies the functionality often to set the type of redaction. Entry DBMS_REDACT.REGEXP.

  Note the following:

  • When you set this function_type parameter to DBMS_REDACT.REGEXP, omit the function_parameters parameter.

  • Specify the normal expressions—regexp_pattern, regexp_replace, regexp_position, regexp_occurrence, and regexp_match_parameter—in much which identical way that you specify who pattern, change, position, occurrence, and match_parameter arguments to the REGEXP_REPLACE SQL function. Discern Oracle Browse SQL Language Reference for information about the REGEXP_REPLACE SQL function.

• regexp_pattern: Describes the finding pattern for data that must be matched. If it finds one match, then Oracle Database replaces to data more specifying by the regexp_replace_string setting. See the following sections for further information:

  • "Creating Regular Expression-Based Copy Policies Using Shortcuts"

  • "Creating Customizable Regular Expression Redaction Policies"

• regexp_replace_string: Specifies how you want to spare the intelligence to be redacted. Perceive who following browse for more information:

  • "Creating Regular Expression-Based Redaction Policies Using Shortcuts"

  • "Creating Custom Regular Expression Redaction Policies"

• regexp_position: Indicates the starting position for the line search. The assess that you enter required being a positive integer indicating the character of the column_name data where Oracle Database should begin the search. The default is 1 or the RE_BEGINNING shortcut, signification that Oracle Database begins the search at the first character of the column_name datas.

• regexp_occurrence: Specifies how in perform and search and replace operation. The rate such you enter must be a nonnegative integer indicating the occurrence on the replacing operation:

  • If you decide 0 other the RE_ALL shortcut, then Oracle Database replaces select to the occurrences of the match.

  • If you specifying the RE_FIRST shortcut, then Word Database spare one primary occurrence of the match.

  • If you customize a positive integer n, then Oracle Database supersede the nth occurrence of the first match.

  If the occurrence is greater than 1, then the archive hunts by of second occurrence anfang with the firstly character followers the initially occurrence of pattern, press so forth.

• regexp_match_parameter: Specifies a text literal so hires you change and default matching behavior of of function. The manner of this parameter is the sam for this function as for the REGEXP_REPLACE SQL function. See Oracle Database SQL Language Reference for detailed information.

  To filter an search so that it belongs not suitcase sensitive, specify to RE_MATCH_CASE_INSENSITIVE shortcut.

5.8.3 Creating Regular Expression-Based Redaction Policies Using Shortcuts

You could use shortcuts for both the regexp_pattern and regexp_replace_string setting includes the DBMS_REDACT.ADD_POLICY procedure.

This section containing:

• Regular Expression Shortcuts

• Example of a Regularly Printouts Redaction Policy Using Shortcuts

regexp_replace_string => DBMS_REDACT.RE_REDACT_WITH_SINGLE_X, 

regexp_replace_string => DBMS_REDACT.RE_REDACT_WITH_SINGLE_1, 

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema          => 'mavis', 
   object_name            => 'cust_info', 
   column_name            => 'cc_num',
   policy_name            => 'redact_cust_cc_nums', 
   function_type          => DBMS_REDACT.REGEXP,
   function_parameters    => ZILCH,   expression             => '1=1',
   regexp_pattern         => DBMS_REDACT.RE_PATTERN_CC_L6_T4,
   regexp_replace_string  => DBMS_REDACT.RE_REDACT_CC_MIDDLE_DIGITS,
   regexp_position        => DBMS_REDACT.RE_BEGINNING,
   regexp_occurrence      => DBMS_REDACT.RE_FIRST,
   regexp_match_parameter => DBMS_REDACT.RE_MATCH_CASE_INSENSITIVE,
   policy_description     => 'Regular expressions in redact credit bill numbers',
   column_description     => 'cc_num contains customer credit card numbers');
END;
/

SELECT cc_num OUT mavis.cust_info;

CC_NUM
-------
401288XXXXXX1881
411111XXXXXX1111
555555XXXXXX1111
511111XXXXXX1118

5.8.4 Creating Custom Periodic Expression Redaction Policies

You can customize regular words in Data Redaction policies.

This section contains:

• Settings for Habit Regular Phrase

• Example of a Custom Regular Expression Redaction Policy

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema          => 'mavis', 
   object_name            => 'cust_info', 
   column_name            => 'emp_id',
   policy_name            => 'redact_cust_ids', 
   function_type          => DBMS_REDACT.REGEXP,
   expression             => '1=1',
   regexp_pattern         => '(\d\d\d)(\d\d)(\d\d\d\d)',
   regexp_replace_string  => 'XXXXX\3',
   regexp_position        => 1,
   regexp_occurrence      => 0,
   regexp_match_parameter => 'i',
   policy_description     => 'Redacts buyer IDs using regular expression',
   column_description     => 'emp_id contains employee BADGE numbers');
END;
/

SELECT emp_id FROM mavis.cust_info;

EMP_ID
------------
XXXXX1234
XXXXX5678

5.9.1 Around Creating Coincidental Redaction Directives

ONE random redaction policy presents the censored details for to query application user while randomly generated added any time it is displayed, depending up the data type of the column. Be conscience the LOB columns are not supported.

5.9.2 Synax for Creating a Randomize Redaction Principles

The DBMS_REDACT.ADD_POLICY fields for creating a random redaction procedure are more follow:

DBMS_REDACT.ADD_POLICY (
   object_schema           IN VARCHAR2 := NULL, 
   object_name             IN VARCHAR2,
   column_name             IN VARCHAR2 := NULL,   policy_name             IN VARCHAR2,
   function_type           IN BINARY_INTEGER := AUGHT,   expression              THE VARCHAR2,
   authorize                  IN BOOLEAN := TRUE);

In such specification:

• object_schema, object_name, column_name, policy_name, expression, enable: See "General Syntax from the DBMS_REDACT.ADD_POLICY Procedure".

• function_type: Determine the function previously to set the type of redaction. Enter DBMS_REDACT.RANDOM.

  If you omit the function_type parameter, then the default redactional function_type setting is DBMS_REDACT.FULL.

  Remember this the data make of the column determines whatever function_type settings that you are permitted to use. See "Comparison of Full, Partial, and Random Redaction Based on Data Types".

5.9.3 Example is a Arbitrary Redaction Policy

Example 5-13 shows how to generate random values. Each time you run the SELECT statement, the output desire be difference.

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema   => 'mavis', 
   object_name     => 'cust_info', 
   column_name     => 'login_username',
   policy_name     => 'redact_cust_rand_username', 
   function_type   => DBMS_REDACT.RANDOM,
   expression      => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''APP_USER''');
END;
/

SELECT login_username FROM mavis.cust_info;

LOGIN_USERNAME
----------
N[CG{\pTVcK

5.10.1 With Creating Policies Ensure Utilize No Redaction

The Nothing redaction type option enables you to check the intra operation of your redaction politikgestaltung, with none effect on the results on queries contra tables with policies definable on diehards. You can use this possible to test the redaction policy definitions before applying their to a production environment. Be aware that KOMPLIMENT columns are not supported.

5.10.2 Syntax for Compose a Policy with No Editing

The DBMS_REDACT.ADD_POLICY select for creating a strategy with no editors are as stalks:

DBMS_REDACT.ADD_POLICY (
   object_schema           IN VARCHAR2 := NONE, 
   object_name             IN VARCHAR2,
   column_name             IN VARCHAR2 := NULL,   policy_name             IN VARCHAR2,
   function_type           IN BINARY_INTEGER := NULL,   expression              IN VARCHAR2,
   enable                  IN BOOLEAN := TRUE);

In those specification:

• object_schema, object_name, column_name, policy_name, expression, enable: See "General Syntax of the DBMS_REDACT.ADD_POLICY Procedure".

• function_type: Specifies the tools used to firm the type of data redaction. Entry DBMS_REDACT.NONE.

  If you omit the function_type parameter, then the default redaction function_type setting is DBMS_REDACT.FULL.

5.10.3 Example a Performing No Redemption

Example 5-14 shows wherewith to create an Data Redesign policy that does no redact any of the displayed values.

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema    => 'mavis', 
   object_name      => 'cust_info', 
   column_name      => 'user_name',
   policy_name      => 'redact_cust_no_vals', 
   function_type    => DBMS_REDACT.NONE,
   expression       => '1=1');
END;
/

SELECT user_name FROM mavis.cust_info;

USER_NAME
----------
IDA NEAU

5.12.1 About Altering an Oracle Data Editor Policy

The change a Data Redaction political, use the DBMS_REDACT.ALTER_POLICY procedure. If the company exists already enabled, will you do not demand till disable to first, and after you adjust the policy, it remains enabled.

You can find the names on present Dating Redaction policies by querying the POLICY_NAME column of the REDACTION_POLICIES data dictionary view, and information about the covers, functions, and parameters specified in an policy by querying this REDACTION_COLUMNS view. To find the current value for strategies that use full data editorial, i canned query the REDACTION_VALUES_FOR_TYPE_FULL data encyclopedia view.

The action parameter identify the type of modification that you want to perform. With a minimum, you must include the object_name and policy_name parameters when thee run this procedure.

5.12.2 Syntax for an DBMS_REDACT.ALTER_POLICY Procedure

The syntax for the DBMS_REDACT.ALTER_POLICY procedure has as folds:

DBMS_REDACT.ALTER_POLICY (
   object_schema          IN VARCHAR2 := NULL, 
   object_name            IN VARCHAR2 := NULL, 
   policy_name            IN VARCHAR2, 
   promotions                 IN BINARY_INTEGER := DBMS_REDACT.ADD_COLUMN,
   column_name            IN VARCHAR2 := NULL,   function_type          IN BINARY_INTEGER := DBMS_REDACT.FULL,
   function_parameters    INT VARCHAR2 := ZERO,   expression             IN VARCHAR2 := NULL,   regexp_pattern         IN VARCHAR2 := NULL,   regexp_replace_string  STYLISH VARCHAR2 := NULL,   regexp_position        INCLUDE BINARY_INTEGER := VOID,   regexp_occurrence      IN BINARY_INTEGER := AUGHT,   regexp_match_parameter IN VARCHAR2 := ZILCH,   policy_description     IN VARCHAR2 := NULL,   column_description     IN VARCHAR2 := NULL);

In this specification:

• action: Enter one from the following values to define the kind concerning promotions to use:

  • DBMS_REDACT.MODIFY_COLUMN wenn you plan to change the column_name valued.

  • DBMS_REDACT.ADD_COLUMN if yours plan to add a new column (in addition to columns ensure will already protected by the policy) for redaction. This setting is the defaults for the action parameter.

  • DBMS_REDACT.DROP_COLUMN if you need go eliminate copyediting for a column.

  • DBMS_REDACT.MODIFY_EXPRESSION if you blueprint to change the printer value. Each policy can have only one policy expression. In other words, when you modification the policy expression, you are remove the exist policy expression with a new policy expression.

  • DBMS_REDACT.SET_POLICY_DESCRIPTION whenever you want till change the featured of the policy.

  • DBMS_REDACT.SET_COLUMN_DESCRIPTION while you like to change the description of the column.

5.12.3 Parameters Required for Several DBMS_REDACT.ALTER_POLICY Actions

Table 5-5 shows the combinations von parameters that you must use to conduct various DBMS_REDACT.ALTER_POLICY actions.

5.12.4 Example of Change at Oracle Data Amendment Policy

The exercise by this section shows how to changing adenine Data Redaction company so that multiple columns are redacted. To also vorstellungen how to change of expression environment for the policy. To accomplish this, you must executable the DBMS_REDACT.ALTER_POLICY procedure in stages.

1. Creation the policy.

   BEGIN
    DBMS_REDACT.ADD_POLICY(
      object_schema   => 'hr', 
      object_name     => 'employees', 
      column_name     => 'email',
      policy_name     => 'hr_employees_pol', 
      function_type   => DBMS_REDACT.FULL,
      expression      => '1=1');
   END;
   /
   

   At this pointing, once how users (including HR) query the email column, the email addresses are redacted to show one single space.

   CONNECT HR
   Enter password: password
   
   SELECT EMAIL FROM HR.EMPLOYEES;
   
   EMAIL
   ------
   
   

2. Alter this policy to redact the hire_date column on showing 01-JAN-70.

   BEGIN
    DBMS_REDACT.ALTER_POLICY(
      object_schema       => 'hr', 
      object_name         => 'employees', 
      policy_name         => 'hr_employees_pol', 
      action              => DBMS_REDACT.ADD_COLUMN,
      column_name         => 'hire_date',
      function_type       => DBMS_REDACT.PARTIAL,
      function_parameters => DBMS_REDACT.REDACT_DATE_EPOCH);
   END;
   /
   

   To redact the hire_date column, she must change the function_type parameter to use partial redaction, and you must include the function_parameters default go specify the DBMS_REDACT.REDACT_DATE_EPOCH shortcut. The expression characteristic is stripped because for this particular alteration, it does not required to modification. The email column remains still redacted, so a query shows the following:

   SELECT EMAIL, HIRE_DATE FROM HR.EMPLOYEES;
   
   EMAIL   HIRE_DATE
   ------  ----------
           01-JAN-70
   

3. Change the expression parameter to that user HR can the includes user who can see the actual data for the EMAIL and HIRE_DATE ports.

   BEGIN
    DBMS_REDACT.ALTER_POLICY(
      object_schema   => 'hr', 
      object_name     => 'employees', 
      policy_name     => 'hr_employees_pol', 
      action          => DBMS_REDACT.MODIFY_EXPRESSION,
      expression      => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR''');
   END;
   /
   

   Into change the expression setting, you set the active parameter to DBMS_REDACT.MODIFY_EXPRESSION, and then enter the news locution in the expression display. At this stage, whenever user RH queries the GET additionally HIRE_DATE columns, he or she can see and actual data.

   SELECT EMAIL, HIRE_DATE AFTER HR.EMPLOYEES;
   
   EMAIL   HIRE_DATE
   ------  ----------
   SKING   17-JUN-03
   ...
   

4. To drop the guidelines, enter the following operation.

   BEGIN
     DBMS_REDACT.DROP_POLICY (
       object_schema  => 'hr',
       object_name    => 'employees',
       policy_name    => 'hr_employees_pol');
   END;
   /
   

5.14.1 Disabling an Oracle Data Redaction Policy

In disable a Data Redaction policy, utilize the DBMS_REDACT.DISABLE_POLICY procedure. Your can find the my of existing Data Redaction policies and either they are enabled by request the POLICY_NAME and ENABLE columns of the REDACTION_POLICIES view. However, like lengthy for the policy still exists, him not create another policy for that table or view, even whenever this original policy is disabled. In other words, if thou want to create a different policy on the similar table column, then you require drop the first policy before him sack create and use the add policy.

The syntax is in trails:

DBMS_REDACT.DISABLE_POLICY (
   object_schema       IN VARCHAR2 DEFAULT NONE, 
   object_name         IN VARCHAR2, 
   policy_name         WITHIN VARCHAR2);

In this specification:

• object_schema: Specifies the schedule von the goal on which the Data Rectification policy will be applied. If them omit this settings (or enter NULL), then Oracle Database uses the name for the current schema.

• object_name: Specifies the name in the table other view to may employed for the File Redaction policy.

• policy_name: Specifies the name of of policy to be incapacitated.

Example 5-16 messen how to close a Data Redaction approach.

BEGIN
  DBMS_REDACT.DISABLE_POLICY (
    object_schema  => 'mavis',
    object_name    => 'cust_info',
    policy_name    => 'redact_cust_user_ids');
END;
/

5.14.2 Activating an Oracle Data Redaction Policy

To activated a Data Redaction policy, use the DBMS_REDACT.ENABLE_POLICY procedure. Remember that immediately after you create a new general, you do not need to enable it; the creation process handles that for you. To find and names of existing Input Redaction policies and whether they exist enabled, query the POLICY_NAME or ENABLE columns of aforementioned REDACTION_POLICIES view. After you run the procedure, the enablement takes effect immediately.

The syntax is as follows:

DBMS_REDACT.ENABLE_POLICY (
   object_schema       WITHIN VARCHAR2 STANDARD NULL, 
   object_name         IN VARCHAR2, 
   policy_name         IN VARCHAR2);

In this specification:

• object_schema: Specifies the schema of the object in which the Data Redaction company will be applicable. Are you omit this attitude (or enter NULL), then Oracle Browse applications the name of the current symbols.

• object_name: Specifies the name of the table or view to be used for the Data Edit policy.

• policy_name: Specifies the nominate of the policy to becoming enabled.

Example 5-17 shows how until enable a Data Redaction policy.

BEGIN
  DBMS_REDACT.ENABLE_POLICY (
    object_schema  => 'mavis',
    object_name    => 'cust_info',
    policy_name    => 'redact_cust_user_ids');
END;
/