Skip Headers
Oracle® Database Testing Guide
12c Release 1 (12.1)

E20852-18
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

16 Application Data Models

Secure Test Data Management provides Enterprise Manager the capability to enable operations such as sensitive data discovery, data subsetting, and data masking. These capabilities enable scanning and tagging of sensitive data and modeling of data relationships incorporated within an Application Data Model (ADM). You must have the Oracle Data Masking and Subsetting Pack license to use test data management features.

The ADM stores the list of applications, tables, and relationships between table columns that are either declared in the data dictionary, imported from application metadata, or user-specified. The ADM maintains sensitive data types and their associated columns, and is used by test data operations, such as data subsetting and data masking, to securely produce test data. Creating an ADM is a prerequisite for data subsetting and data masking operations.

Figure 16-1 shows the Application Data Model's relationship to other test data management components as well as the production and test environments.

Figure 16-1 Test Data Management Architecture

Description of Figure 16-1 follows
Description of "Figure 16-1 Test Data Management Architecture"

You can perform several tasks related to Application Data Modeling, including the following tasks discussed in this chapter:

Note:

The procedures in this chapter are applicable to Oracle Enterprise Manager 12.1 and higher Cloud Control only.

See Also:

16.1 Creating an Application Data Model

The following procedure enables you to:

  • Initiate creation of an Application Data Model (ADM)

  • View and edit application tables

  • View referential relationships

  • Manually add a referential relationship

  • Discover sensitive columns

  • Set the type for sensitive columns

Before proceeding, ensure that you have the following privileges:

  • EM_ALL_OPERATOR for Enterprise Manager Cloud Control users

  • SELECT_CATALOG_ROLE for database users

  • Select Any Dictionary privilege for database users

Note:

When you create an ADM, the PL/SQL metadata collection packages are automatically deployed on the target database. The Database user must have DBA privileges to auto-deploy the packages.

To create an Application Data Model: 

  1. From the Application Data Models page, view the diagram that shows how you can create a database for a test environment.

    As the diagram shows, the first step is to create an ADM.

  2. Create an ADM:

    1. Click Create.

      A pop-up window requesting general properties information appears.

    2. Provide the required Name and Source Database.

      The Source Database is the source from which the metadata is to be extracted.

    3. Select an Application Suite:

      If you select Custom Application Suite:

      • By default, metadata collection is enabled for the ADM creation process.

      • If you uncheck "Create One Application For Each Schema," you create a shell ADM and will need to edit the ADM later to add applications and tables. Also, no metadata collection job is submitted, unlike the default choice.

      If you select Oracle Application Suite:

      • Oracle E-Business Suite–You provide database credentials for APPS user (or equivalent) and submit a job to create the ADM.

      • Oracle Fusion Applications–You provide database credentials for FUSION user (or equivalent) and submit a job to create the ADM.

      Note the following points about metadata collections:

      • The metadata collection for the selected application suite populates the ADM with the applications and tables in the suite.

      • The ADM can collect metadata for one or more schemas. An ADM application typically represents a schema. Each schema you select becomes an ADM application, and the ADM becomes populated with the tables in the schema, particularly in the case of custom applications. Note, however, that multiple applications can also map to a single schema, as in the case of Fusion Applications. The actual mapping depends on the application metadata discovered by the metadata collection job.

    4. Click Continue.

      Assuming that you selected Custom Application Suite, a Schemas pop-up appears in which you select schemas to include from the Available list.

    5. Click Continue, provide the schedule parameters, then click Submit to submit the metadata collection job.

      The ADM you created appears in the Application Data Models page. The Most Recent Job Status table column indicates that the metadata collection job is running. The model is locked, and you cannot edit it during this period until the status indicates that the job is complete.

  3. View and edit application tables:

    1. Select the model you created, then select Edit.

      The Applications and Tables subpage appears, displaying the applications found during metadata collection.

      To see the tables for an application, click the expand ( > ) icon.

    2. To edit an application, select the application, open the Actions menu, then select Add Table to Application.

      The Add Table to Application pop-up window appears.

    3. Click the Table search icon.

      The Search and Select pop-up appears, showing all of the tables from the selected schema that are not assigned to an application.

    4. Select an unassigned table, then click OK.

      The table name now appears in the Add Table to Application pop-up.

    5. After selecting a Table Type, click OK.

      The table now appears in the Applications and Tables view.

  4. View referential relationships:

    1. Click the Referential Relationships tab.

      There are three types of referential relationships:

      • Dictionary-defined

        Upon opening this tab, this view shows the referential relationships that the metadata collection extracted, resulting from primary key and foreign key relationships. You can remove relationships from the ADM if desired.

      • Imported from template

        If there are application templates available from the vendor of the enterprise application, for example, Oracle Fusion Applications or Oracle E-Business Suite, then the ADM can be created from the application vendor-supplied template by using the Import action on the ADM home page.

      • User-defined

        See the step below about manually adding a referential relationship for more information.

    2. Open an application view by selecting it, then using the chevron icon (>) to reveal parent and dependent key relationships, or by selecting Expand All from the View menu to view all relationships.

  5. Manually add a referential relationship:

    1. From the Referential Relationships tab, open the Actions menu, then select Add Referential Relationship.

      The Add Referential Relationship pop-up window appears.

    2. Select the requisite Parent Key and Dependent Key information.

    3. In the Columns Name list, select a dependent key column to associate with a parent key column.

    4. Click OK to add the referential relationship to the ADM.

      The new dependent column now appears in the referential relationships list.

  6. Discover sensitive columns automatically or add them manually:

    To automatically discover sensitive columns:

    1. From the Sensitive Columns tab, open the Actions menu, then select Create Sensitive Column Discovery Job.

      The Parameters pop-up appears.

    2. Select one or more applications and one or more sensitive column types.

      Each type you select is processed for each application to search for columns that match the type.

    3. Click Continue.

      The schedule pop-up window appears.

    4. Provide the required information, schedule the job, then click Submit when you have finished.

      The Sensitive Columns subpage reappears.

    5. Click Save and Return to return to the Application Data Models home page.

    6. When the Most Recent Job Status column indicates that the job is Successful, select the ADM, then click Edit.

    7. Select the Sensitive Columns tab, then click Discovery Results to view the job results.

    8. To set the sensitive status of any column, select the row for the column you want to define, open the Set Status menu, then select either Sensitive or Not Sensitive.

    9. Click OK to save and return to the Sensitive Columns tab.

      The sensitive columns you defined in the previous step now appear in the list.

    10. Click Save and Return to return to the Application Data Models page.

    To manually add sensitive columns:

    1. From the Application Data Models page, select an ADM, then click Edit.

    2. Select the Sensitive Columns tab, then click Add.

      The Add Sensitive Column pop-up appears.

    3. Provide the required information and an optional Sensitive Column Type, then click OK.

      The sensitive column now appears in the table for the Sensitive Columns tab.

  7. Change the type for sensitive columns:

    1. Click the Sensitive Columns tab.

      This view shows the sensitive columns that have already been identified.

    2. Select the sensitive column for which you want to change the type.

    3. Open the Actions menu, then select Set Sensitive Column Type.

      The Set Sensitive Column Type pop-up window appears.

    4. Select the new type and click OK.

16.2 Managing Sensitive Column Types

After you have successfully created an ADM, the next task is to create either a new sensitive column type or one based on an existing type.

To create a sensitive column type: 

  1. From the Actions menu of the Application Data Models page, select Sensitive Column Types.

    The Sensitive Column Types page appears.

  2. Click Create.

    The Create Sensitive Column Type pop-up appears.

  3. Specify a required name and regular expressions for the Column Name, Column Comment, and Column Data search patterns.

    • The Or Search Type means that any of the patterns can match for a candidate sensitive column.

    • The And Search Type means that all of the patterns must match for a candidate sensitive column.

    If you do not provide expressions for any of these parameters, the system does not search for the entity.

  4. Click OK.

    The sensitive column appears in the table in the Sensitive Column Types page.

To create a sensitive column type based on an existing type: 

  1. From the Actions menu of the Application Data Models page, select Sensitive Column Types.

    The Sensitive Column Types page appears.

  2. Select either a sensitive column type you have already defined, or select one from the out-of-box types that the product provides.

  3. Click Create Like.

    The Create Sensitive Column Type pop-up appears.

  4. Specify a required name and alter the existing expressions for the Column Name, Column Comment, and Column Data search patterns to suit your needs.

  5. Click OK.

    The sensitive column appears in the table in the Sensitive Column Types page.

16.3 Associating a Database to an Application Data Model

After you have created an Application Data Model (ADM), you can select additional databases to be associated databases of an ADM, as explained in the following procedure. See "Creating an Application Data Model" for instructions on creating an ADM.

To associate a database to an ADM: 

  1. From the Application Data Models page, select an ADM, select Actions, then Associated Databases.

    This dialog lists all of the databases associated with this ADM and the schemas assigned to each application per database. You can add more databases that give you a choice of data sources when subsetting and databases to mask during masking.

  2. Click Add, then select a database from the pop-up.

    The selected database now appears in the Database section of the Associated Databases dialog.

  3. To change a schema, select the associated database on the left, select the application on the right for which the schema is to be changed, then click Select Schema.

  4. Select the missing schema from the list in the pop-up, then click Select.

16.4 Importing and Exporting an Application Data Model

You can share Application Data Models (ADM) with other Enterprise Manager environments that use a different repository by exporting an ADM, which can subsequently be imported into the new repository.

An exported ADM is by definition in the XML file format required for import. You can edit an exported ADM XML file prior to import. When exporting an ADM for subsequent import, it is best to have one that uses most or all of the features—applications, tables, table types, referential relationships, sensitive columns. This way, if you are going to edit the exported file prior to import, it is clear which XML tags are required and where they belong in the file.

Note:

There are EMCLI verbs to export and import an ADM if you want to perform these operations remotely or script them.

16.4.1 Importing an ADM

There are two methods of import:

  • Import an ADM XML file from the desktop

  • Import an ADM XML file from the Software Library

To import an ADM XML file from your desktop:

  1. From the Actions menu, select Import, then select File from Desktop.

  2. In the pop-up that appears, specify a name for the ADM, the source database you want to assign to the ADM, and location on your desktop from which you want to import the ADM.

  3. Click OK.

    The ADM now appears on the Application Data Models page.

To import an ADM XML file from the Software Library:

  1. From the Actions menu, select Import, then select File from Software Library.

  2. In the Export File from Software Library pop-up that appears, select the desired ADM XML file on the left, then specify a name and the source database you want to assign to the ADM on the right.

  3. Click Import.

    The ADM now appears on the Application Data Models page.

After importing an ADM, you may want to discover sensitive columns or run a verification job. In the process of performing these tasks, the PL/SQL metadata collection packages are automatically deployed on the target database. The Database user must have DBA privileges to auto-deploy the packages.

16.4.2 Exporting an ADM

There are three methods of export:

  • Export a selected ADM to the desktop

  • Export an ADM from the Software Library

  • Export an ADM to a TSDP Catalog

To export an ADM as an XML file to your desktop:

  1. From the Application Data Models page, select the ADM you want to export.

  2. From the Actions menu, select Export, then select Selected Application Data Model.

  3. In the File Download pop-up that appears, click Save.

  4. In the Save As pop-up that appears, navigate to a file location and click Save.

    The system converts the ADM into an XML file that now appears at the specified location on your desktop.

To export an ADM from the Software Library:

  1. From the Actions menu, select Export, then select File from Software Library.

  2. In the Export File from Software Library pop-up that appears, select the desired ADM and click Export.

  3. In the File Download pop-up that appears, click Save.

  4. In the Save As pop-up that appears, navigate to a file location and click Save.

    The system converts the ADM into an XML file that now appears at the specified location on your desktop.

To export an ADM to a Transparent Sensitive Data Protection (TSDP) Catalog:

  1. From the Application Data Models page, select the ADM you want to export.

  2. From the Actions menu, select Export, then select Export to TSDP Catalog.

  3. The Application Data Models page displays a table of associated databases. Select a database and click the Export Sensitive Data button.

  4. In the Export Sensitive Data pop-up that appears, provide credentials for the selected database and click OK.

    A message appears on the Application Data Models page confirming that the sensitive data was copied to the database.

For detailed information on TSDP, see Oracle Database Security Guide.

16.5 Verifying or Upgrading a Source Database

After you have created an Application Data Model (ADM), the Source Database Status column can indicate Valid, Invalid, Needs Verification, or Needs Upgrade.

  • Invalid status–Verify the source database to update the referential relationships in the application data model with those found in the data dictionary, and to also determine if each item in the application data model has a corresponding object in the database.

  • Needs Verification status–You have imported an Oracle supplied template and you must verify the ADM before you can use it. This is to ensure that necessary referential relationships from data dictionary are pulled into the ADM.

  • Needs Upgrade status–You have imported a pre-12c masking definition, so you now need to upgrade the ADM.

To verify a source database: 

  1. Select the ADM to be verified, indicated with an Invalid status.

  2. From the Actions menu, select Verify.

  3. Select the source database with the Invalid status, then click Create Verification Job.

  4. Specify job parameters in the Create Verification Job pop-up, then click Submit.

  5. After the job completes successfully, click the source database and note the object problems listed.

  6. Fix the object problems, rerun the Verification Job, then check that the Source Database Status is now Valid.

To upgrade an ADM: 

  1. Select the ADM to be upgraded, indicated with a Needs Upgrade status.

  2. From the Actions menu, select Upgrade.

  3. Specify job parameters in the Create Upgrade Job pop-up, then click Submit.

  4. After the job completes successfully, check that the Source Database Status column now indicates Valid. If the column indicates Invalid, see the previous procedure.

16.6 Using Self Update to Download the Latest Data Masking and Test Data Management Templates

Use the Self Update feature to get the latest data masking and data subsetting templates available from Oracle, out of band of the next major release cycle. With the auto-download feature enabled, new templates appear in the Software Library as they become available. Otherwise, you can access them manually as follows:

  1. From the Setup menu, select Extensibility, then select Self Update.

  2. On the Self Update page, scroll down and select Test Data Management templates.

  3. In the available updates table, select the templates you want to download and select Download from the Actions menu.

    This action downloads the templates to the Software Library from where you can import them into your Data Masking and Test Data Management environment or save them locally for editing.

  4. To save a downloaded template:

    1. Navigate to the appropriate home page (ADM, masking, or subset).

    2. From the Actions menu, select Export from the Software Library.

    3. Select a template in the pop-up window and click Save.

    4. Specify a location where to save the XML file.

    The template file is available for editing prior to being imported into Application Data Models.

  5. To import a downloaded template:

    1. Navigate to the appropriate home page (ADM, masking, or subset).

    2. From the Actions menu, select Import from the Software Library.

    3. Select a template in the pop-up window and specify appropriate values for the input parameters.

    4. Click Import. Template definitions are imported into the respective tables.

Reapply Templates if Upgrading to Database Plug-in 12.1.0.5 from Release 12.1.0.3 or 12.1.0.4

If you previously applied test data management templates and are upgrading from release 12.1.0.3 or 12.1.0.4, you have to remove the templates and then reapply them; otherwise, the templates will not be visible when importing or exporting from the Software Library.

  1. Go to the Self Update page.

  2. Select the test data management templates and remove them.

  3. Return to the Self Update page.

  4. Select the test data management templates and check for updates.

  5. In the available updates table, select the templates you want and then download and apply them using the respective actions from the Actions menu.

    The templates should now be visible in the Software Library.

16.7 Test Data Management and Access Rights

By default, Enterprise Manager Administrators can access the primary test data management (TDM) pages:

  • Application Data Models

  • Data Subset Definitions

  • Data Masking Definitions

  • Data Masking Formats

This is by virtue of having the TDM_ACCESS privilege, which is included in the PUBLIC role. The Super Administrator can revoke this privilege for designated administrators, thereby restricting access to the TDM pages. Without the privilege, the respective menu items do not appear in the Cloud Control console.

Additionally, Enterprise Manager provides a privilege access model that enables Super Administrators and administrators to limit access to TDM objects to authorized users only. The model involves the ability to grant Operator or Designer privileges to selected users.

Operator Privileges

Those granted Operator privileges can perform data masking and subsetting operations. Privileges can be granted on TDM objects; that is, on Application Data Models (ADM), data subsetting definitions, and data masking definitions. Operator privileges do not include the ability to edit and delete these objects.

  • ADM–a user (other than Super Administrator) with ADM Operator privileges can view an ADM but cannot edit and delete it, nor view its properties. To enforce this, the Edit and Delete icons, and the Properties menu are disabled. Additionally, the Sync option on the Create Verification Job page is disabled.

  • Data subset definition–a user (other than Super DSD Administrator) with Operator privileges can view but not edit and delete a subset definition. To enforce this, the Edit and Delete icons are disabled.

    A user with Data Subset Definition Operator privileges can do any other operation except edit and delete the data subset definition and has the following rights:

    • View the data subset definition.

    • Create a data subset to export files.

    • Create a data subset on a database.

    • Save the subset script.

  • Data masking definition–a user with Data Masking Definition Operator privileges can do any other operation except edit and delete the data masking definition and has the following rights:

    • View the data masking definition.

    • Generate a data masking script.

    • Schedule a data masking job.

    • Export a data masking definition.

Designer Privileges

Those granted Designer privileges can enhance, modify, and manage TDM objects. These users can also grant and revoke Operator and Designer privileges to others. Designer privileges imply the corresponding Operator privileges on a TDM object.

  • ADM–a user with Designer privileges can perform all operations on an ADM including delete.

  • Data subset definition–a user with Designer privileges can perform all operations on a subset definition including delete.

  • Data masking definition–a user with Designer privileges can perform all operations on a masking definition including delete.

16.8 Granting Privileges on an Application Data Model

You can grant privileges on an Application Data Model that you create so that others can have access. To do so, you must be an Enterprise Manager Administrator with at least Designer privileges on the ADM.

  1. From the Enterprise menu, select Quality Management, then select Application Data Models.

  2. Select the ADM to which you want to grant privileges.

  3. From the Actions menu, select Grant, then select as follows:

    • Operator–to grant Operator privileges on the ADM to selected roles or administrators, which means the grantees can view and copy but not edit and delete the definition.

    • Designer–to grant Designer privileges on the ADM to selected roles or administrators, which means the grantees can view, edit, and delete the definition.

  4. In the dialog that opens, select the type (administrator or role, or both). Search by name, if desired. Make your selections and click Select.

    Those selected now have privileges on the ADM.

  5. Use the Revoke action if you want to deny privileges previously granted.