11 Using Oracle Data Redaction in Oracle Enterprise Manager

The Oracle Data Redaction formats are used for commonly redacted data, such as ID numbers, credit cards, or phone numbers.

You can use several default Oracle Data Redaction formats (previously known as Oracle Data Redaction templates). As an example of the Oracle Data Redaction formats, a set of Social Security number formats enable you to quickly designate ways to redact Social Security numbers, such as redacting the first five numbers of the Social Security number.

Figure 11-3 displays the default Oracle Data Redaction formats.

Figure 11-3 Default Oracle Data Redaction Formats

Description of "Figure 11-3 Default Oracle Data Redaction Formats"

Each default Oracle Data Redaction format consists of a specific redaction function that determines the redacted output when the redaction format is used in an Oracle Data Redaction policy. For example, the Credit Card Numbers - NUMBER default redaction format replaces the first twelve digits of the column data with the digit 0, when it is used in an Oracle Data Redaction policy. That is, if the column data is 5555555555554444, the redacted output will be 0000000000004444.

If you have deployed the Enterprise Manager for Oracle Database plug-in 12.1.0.7 or higher on your system, then you can also create and save custom redaction formats, which you can then use in your redaction policies.

You can create and save custom Oracle Data Redaction formats using Enterprise Manager Cloud Control.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then click the name of a database target.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. Select the Formats tab.
7. Do one of the following:
   • To create a new redaction format, click Create.
   • To create a redaction format that is based on a default format, select the format and then click Create Like.
   If you select Create, then the following dialog box appears:
   
   Description of the illustration GUID-F08BB2F7-1350-43F9-AEB3-1A5A6D61331F-default.png
8. Provide a name and a description for the redaction format that you want to create.

   If you want to map the redaction format to a particular sensitive column type (such that the created redaction format can be used to redact the data of a column that is associated with the sensitive column type), then select a value for Sensitive Column Type.

   Select the function that the format should use to redact the column data. For Redaction Function, select as follows:

   • FULL if the format should redact the entire column data.

   • PARTIAL if the format should redact only a part of the column data. Ensure that you provide the function attributes, as well as the data type that you want to use the redaction format for.

   • REGEX if the format should redact data based on a regular expression. Ensure that you provide the function attributes.

   • RANDOM if the format should redact data in a random manner, using randomly generated values

   • NONE if the format will be used to only test the definition of a redaction policy, and not redact any column data

9. Click OK to create and save the custom redaction format.
   This format now can be used to create a redaction policy.

You can edit custom Oracle Data Redaction formats using Enterprise Manager Cloud Control, but not in SQL*Plus.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then click the name of a database target.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. Select the Formats tab.
7. Select the custom redaction format that you want to edit, then click Edit.
   A dialog box similar to the following appears:
   
   Description of the illustration GUID-5FA326FA-65B2-4137-B51A-05908067E932-default.png
   
8. (Optional) Choose to edit the format description, sensitive column type, redaction function, and the redaction function attributes.
9. Click OK to save the edited format.

Enterprise Manager Cloud Control displays the details of the Oracle-supplied and custom Oracle Data Redaction formats.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then click the name of a database target.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. Select the Formats tab.
   The Data Redaction Formats page appears, similar to the following page.
   
   Description of the illustration GUID-04363FF6-E337-4E54-8BA8-F94786462533-default.png
   
7. Select the required redaction format, then click View.

You can delete custom Oracle Data Redaction formats using Enterprise Manager Cloud Control.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then click the name of a database target.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. Select the Formats tab.
7. Select the custom redaction format that you want to delete, and then click Delete.
8. In the Confirmation dialog box, click Yes or No.

Use the Data Redaction page in Cloud Control to manage Oracle Data Redaction policies.

To redact the data present in a particular database table or view column, you must create an Oracle Data Redaction policy. Data is redacted using a redaction format that is specified by the Oracle Data Redaction policy. To redact data, you can use any of the Oracle-supplied redaction formats, or create and use a custom redaction format. If the table or view column that contains the data that you want to redact is mapped to a sensitive column type, Oracle uses the mapping to recommend suitable redaction formats for the data. Thus, Oracle Data Redaction policies encapsulate database schemas, database table and view columns, sensitive column types, and Oracle Data Redaction formats.

Figure 11-4 shows the Data Redaction page, which enables you to create and manage Oracle Data Redaction policies in Cloud Control.

Figure 11-4 Oracle Data Redaction Policies Page

Description of "Figure 11-4 Oracle Data Redaction Policies Page"

You can create an Oracle Data Redaction policy using Enterprise Manager Cloud Control.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then click the name of a database target for which you want to create an Oracle Data Redaction policy.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. In the Policies section of the Policies tab, select Create.

   If this is the first time that you are creating a Data Redaction policy, then the Data Redaction: Set up for enabling column sensitive type discovery dialog box appears. This feature enables the use of column sensitive type discovery for Data Redaction policies.

   To accomplish this, Enterprise Manager creates the GET_COL_DATA_SENSITIVE_TYPES procedure in the DBSNMP schema. To perform a sensitive type discovery for a selected column while creating an Oracle Data Redaction policy, a user must have the EXECUTE privilege on the DBSNMP.GET_COL_DATA_SENSITIVE_TYPES procedure. If the database is protected by Oracle Database Vault, then ensure that any users who must create Data Redaction policies are participants to realms that protect the DBSNMP schema.

7. If the Data Redaction: Set up for enabling column sensitive type discovery dialog box appears and if the current login user does not have the correct requirements, select a credential of a user who has the EXECUTE privilege on DBSNMP.GET_COL_DATA_SENSITIVE_TYPES. Then click OK.
8. On the Create Data Redaction Policy page, enter the following information:

   • Schema: Enter (or search for) the name of the schema that contains the data you want to redact.

   • Table/View: Enter (or search for) the table or field that contains the column you want to redact.

   • Policy Name: Enter a for the policy, such as emp_wages_pol.

   • Default Expression: Enter the default expression. The default setting is 1=1, which means that the policy always will be enforced. If you are not familiar with the components of a policy expression, then click the pencil icon beside the Policy Expression field to use Policy Expression Builder. Select Policy is in effect when, select the required conditions, then click Add. Click Edit if you want to edit the policy expression manually. After building the required policy expression, click OK. The Policy Expression Builder appears as follows:

     
     Description of the illustration GUID-16DF27F1-08F1-41F5-BAC9-4267EF0AC8D1-default.png
9. In the Object Columns section, click Add to add a table or view column to the redaction policy.

   A dialog box similar to the following appears:

   
   Description of the illustration GUID-C08E7D00-7129-4F2A-A998-D82906A8E83A-default.png

   The redaction policy is applied only on the table or view columns that are added to it.

10. From the Column menu, select the table or view column to which you want to apply the redaction policy.
    To the right of the Column menu is an icon that you can click to view the contents of the selected column.
    If the column contains sensitive data and has been mapped to a sensitive column type, then from the Sensitive Column Type menu, select the sensitive column type that it has been mapped to. If the search pattern in the Sensitive Column Type menu matches, then the sensitive column type is selected by default. For example, for a column listing credit card numbers, if there is a match, then the menu will list Undefined and CREDIT_CARD_TYPE. If there is no sensitive column type created, then the default Sensitive Column Type menu listing is only Undefined.
11. From the Redaction Format menu, select the redaction format that you want to use.
    The drop-down list is populated with the Oracle Database-supplied redaction formats, as well as the custom redaction formats that you have created and saved.

    If you do not want to use a pre-defined redaction format (that is, an Oracle-Database supplied redaction format, or a custom redaction format that you have created), and instead want to specify the redaction details while creating the redaction policy, select CUSTOM for Redaction Format.

    The Add dialog box adjusts to accommodate the type of redaction format and function that you select. For example, if you select the CUSTOM redaction format and the REGEX redaction function, then the Function Attributes region appears in the dialog box.

12. From the Redaction Function menu, select the function that you want to use to redact the column data.
    Select FULL if you want to redact the entire column data, PARTIAL if you want to redact only a part of the column data, REGEX if you want to redact the column data based on a regular expression, RANDOM if you want to redact the column data in a random manner, using randomly generated values, or NONE if you only want to test the definition of the redaction policy, and not redact any column data. Note that all the redaction functions may not be applicable for a particular redaction format. The drop-down list displays only the redaction functions that are applicable for the selected redaction format.
    If you selected CUSTOM for Redaction Format in the previous step, and PARTIAL or REGEX for Redaction Function, ensure that you specify the function attributes.
13. Click OK.
14. Repeat these steps starting with Step 8 for all the columns that you want to add to the redaction policy.
15. On the Create Data Redaction Policy page, click OK to create the data redaction policy.

    When you create an Oracle Data Redaction policy, it is enabled by default.

You can edit an Oracle Data Redaction policy using Enterprise Manager Cloud Control.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then search for and click the name of the database target for which the Oracle Data Redaction policy that you want to edit was created.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. In the Policies section of the Policies tab, select the redaction policy that you want to edit, then click Edit..
   Description of the illustration GUID-B428449E-6E09-4B05-A283-D90331F9099A-default.png
7. On the Edit Data Redaction Policy page, choose to edit the policy expression, add new columns to the redaction policy, modify the redaction details of a column that is a part of the policy, or delete a column from the redaction policy.

   You can do the following:

   • To add a new column to the redaction policy, in the Object Columns section, click Add, select the table or view column that you want to add, then specify the redaction details.

   • To modify the redaction details of a column that is a part of the policy, select the column, click Modify, then edit the redaction details.

   • To delete a column from the redaction policy, select the column, then click Delete.

8. On the Edit Data Redaction Policy page, after editing the required fields, click OK to save and enable the edited redaction policy.

You can find Oracle Data Redaction policy details such as whether the policy is enabled by using Enterprise Manager Cloud Control.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then search for and click the name of the database target for which the Oracle Data Redaction policy that you want to view was created.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. In the Policies section of the Policies tab, do one of the following:
   • Select the name of the policy in the table.
   • Select the required redaction policy, then click View.
7. To exit, click OK.

An Oracle Data Redaction policy is executed at run time only if it is enabled. When you create an Oracle Data Redaction policy, it is enabled by default.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then search for and click the name of the database target for which the Oracle Data Redaction policy that you want to enable or disable was created.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. In the Policies section of the Policies tab, select the redaction policy that you want to enable or disable, and then click Enable or Disable.
   
   Description of the illustration GUID-92B71A37-45BB-4FA1-8422-8CC77877D7DB-default.png
7. In the Confirmation dialog box, click Yes or No.

You can delete an Oracle Data Redaction policy using Enterprise Manager Cloud Control.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then search for and click the name of the database target for which the Oracle Data Redaction policy that you want to delete was created.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. In the Policies section of the Policies tab, select the redaction policy that you want to delete, and then click Delete.
7. In the Confirmation dialog box, click Yes or No.

You can create and apply named Oracle Data Redaction policy expression to multiple columns in tables and views in Oracle Enterprise Manager Cloud Control.

When you modify the policy expression, the change is reflected in all redacted columns in the database instance that use the policy expression. Cloud Control enables you to create, edit, view, apply to columns, and delete policy expressions. Before you can create and use named Data Redaction policy expressions, ensure that the COMPATIBLE initialization parameter is set to 12.2.0.0.

You can create and apply a named Oracle Data Redaction policy expression using Enterprise Manager Cloud Control.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then click the name of a database target for which you want to create an Oracle Data Redaction policy.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. On the Oracle Data Redaction page, select the Expressions tab.
7. Click Create.

   The Create dialog box appears.

   
   Description of the illustration GUID-CC12C8EB-35CC-4227-9854-476BC6BA9CDC-default.png
8. In the Create dialog box, enter the following information:
   • Expression Name: Enter a name for the policy expression. Existing policy expressions are listed on the Data Redaction page.
   • Description: Enter a brief description of the policy.
   • Expression: Enter the expression. For more complex expressions, such as applying or exempting the policy from specific users, click the Policy Expression Builder icon at the right of the Expression field. Click OK in the Policy Expression Builder to create the expression.
9. Click OK in the Create dialog box.

   After you create the policy expression, it is listed in the Data Redaction page and ready to be associated with a Data Redaction policy.

10. In the Data Redaction page, select the Policies tab.
11. Under Policies, select the row for the policy that redacts the column to which you want to apply the policy expression, and then click Edit.
12. Under Object Columns, select the column that you want and then click the Modify button.
13. In the Modify dialog box, select the expression from the Expression Name list.
14. Click OK, and then click OK again in the Edit Data Redaction Policy dialog box.

You can edit a named Oracle Data Redaction policy expression using Enterprise Manager Cloud Control.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then click the name of a database target for which you want to create an Oracle Data Redaction policy.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. On the Oracle Data Redaction page, select the Expressions tab.
7. Select the policy expression that you want to edit and then click Edit.
   
   Description of the illustration GUID-36FF93B8-0369-4DAE-8140-6CED35E67675-default.png
8. In the Edit dialog box, modify the Description and Expression fields as necessary. For more complex expressions, click the Policy Expression Builder icon, and then click OK after you have recreated the expression.
9. Click OK in the Edit dialog box.

You can view named Oracle Data Redaction policy expressions using Enterprise Manager Cloud Control.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then click the name of a database target for which you want to create an Oracle Data Redaction policy.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. On the Oracle Data Redaction page, select the Expressions tab.
7. Select the policy expression that you want to view and then click View.
   The View dialog box appears, showing the definition of the policy expression.
8. Click OK to exit the View dialog box.

You can delete named Oracle Data Redaction policy expressions using Enterprise Manager Cloud Control.

1. Log into Oracle Enterprise Manager Cloud Control as either user SYSTEM or SYSMAN.
   The URL is as follows:
   https://host:port/em
2. From the Targets menu, select Databases.
3. Select Search List, then click the name of a database target for which you want to create an Oracle Data Redaction policy.
4. On the home page of the database target, from the Security menu, select Data Redaction.
5. Log in to the database, if you are prompted to do so.
   Ensure that you log in to the database as a user that has the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
6. On the Oracle Data Redaction page, select the Expressions tab.
7. Select the policy expression that you want to delete, and then click Delete.
   The Delete Expressions confirmation dialog box appears.
8. Click OK.