2 Application Data Modeling

2.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

  Note:

  The EM_ALL_OPERATOR privilege is not required, if you have the following privileges:

  Target Privileges (applicable to all targets):

  1. Connect to any viewable target

  2. Execute Command Anywhere

  Resource Privileges:

  1. Job System

  2. Named Credential

  3. Oracle Data Masking and Subsetting resource privilege

• SELECT_CATALOG_ROLE for database users

• Select Any Dictionary privilege for database users

To create an Application Data Model: 

1. From the Enterprise menu, select Quality Management, then select Application Data Modeling. The selection is also available from the Security menu on the Databases page (Database Load Map).

   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, but you can initiate metadata collection by submitting a verification job as described in "Verifying or Upgrading a Source Database".

      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 table on the Application Data Modeling 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, then select Add Table to Application from the Actions menu.

      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 expand parent and dependent key relationships, or by select Expand All from the View menu to view all relationships.

5. Manually add a referential relationship:

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

      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:

   Note:

   Oracle recommends that you gather statistics prior to submitting the sensitive column discovery job for more accurate results. For example, to determine if there empty tables in the schema before unchecking the Scan Empty Tables option on the discovery job page.

   To automatically discover sensitive columns:

   1. Click the Sensitive Columns tab, then select Create Sensitive Column Discovery Job from the Actions menu.

      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.

      The Sensitive Columns subpage reappears.

   5. Click Save and Return to return to the Application Data Modeling 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 Modeling page.

   To manually add sensitive columns:

   1. From the Application Data Modeling 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. Select Set Sensitive Column Type from the Actions menu.

      The Set Sensitive Column Type pop-up window appears.

   4. Select the new type and click OK.

2.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 on the Application Data Modeling 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 on the Application Data Modeling 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.

2.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 Modeling page, select an ADM, then select Associated Databases from the Actions menu.

   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.

2.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.

• Importing an ADM

• Exporting an ADM

2.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 Verify and 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.

2.6 Using Self Update to Download the Latest Data Masking and Subsetting Templates

Use the Self Update feature to get the latest data masking and 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 Data Masking and Subsetting 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 Subsetting 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 Modeling.

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 Data Masking and Subsetting 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 Data Masking and Subsetting templates and remove them.

3. Return to the Self Update page.

4. Select the Data Masking and Subsetting 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.

2.7 Access Rights to Data Security Objects

By default, Enterprise Manager Administrators can access the primary data security object pages:

• Application Data Modeling

• Data Subsetting Definitions

• ns

• 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 these 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 data security objects (ADMs, ns, and data subsetting definitions) 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 data security objects; that is, on ADMs, data subsetting definitions, and ns. 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 and export it, 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 subsetting 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 Subsetting Definition Operator privileges can do any other operation except edit and delete the data subset definition and has the following rights:

  • View the data subsetting definition.

  • Create a data subset to export files.

  • Create a data subset on a database.

  • Save the subset script.

• n–a user with n Operator privileges can do any other operation except edit and delete the n and has the following rights:

  • View the n.

  • Generate a data masking script.

  • Schedule a data masking job.

  • Export a n.

Designer Privileges

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

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

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

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

2.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 Modeling. The selection is also available from the Security menu on the Databases page (Database Load Map).

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.