Replacing Sensitive Data By Using the Data Masking Pack

Install Oracle Database 11g Release 2.

Verify that the Spatial option is installed and the supporting database objects are created. Follow these steps for the verification:

1. Use SQL*Plus and logon to the database as sys or system.
2. Issue this select statement: SELECT name, value FROM v$option WHREE name = 'Spatial'
   • You should see True in the value column
   • If you see False, then you need to use the Oracle Installer and install the Spatial option.
3. Issue this statement: DESCRIBE sdo_geometry
   • You should see the object type and its methods listed
   • If you see an "object does not exist" error message, then you need to install Oracle Intermedia and then create the objects for Oracle Spatial. 
     To install Intermedia, as sys, run:
     
     • cd $ORACLE_HOME/ord/admin/ordinst.sql
     • Make sure there are no errors.  Java in the server must be installed prior to intermeida.  You will get an error if it's not.  If you get the error, install Java in the server, and then run ordinst again.
     • After ordinst.sql is successfully run, you can create the Spatial objects.  As sys, run:
       • cd $ORACLE_HOME/md/admin
       • mdinst.sql

Optionally install Oracle Enterprise Manager Grid Control 10.2.0.5 if you want to use it instead of Enterprise Manager Database Control.

Download and unzip the datamask.zip file into your working directory. In this tutorial the working directory is datamask.

Log in to SQL*Plus as the SYSTEM user.

Execute the OBE_DM_setup01.sql script to prepare for this tutorial. This script deletes objects that may have been created during a previous execution of the tutorial.

Execute the OBE_DM_setup02.sql script to create the HR_TEST and OE_TEST schemas. This step simulates cloning the production database to an instance that is used for masking, but much smaller in scope. Enter a password for the HR_TEST and OE_TEST users when prompted.

The HR_TEST and OE_TEST users are created and objects are created in their schemas.

Execute the OBE_DM_setup03.sql script to add columns and data to the HR_TEST.EMPLOYEES table.

The script also creates a table named HR_TEST.MASK_DATA which is used to illustrate how you use a data table from a commercial provider.

Launch Enterprise Manager Database Control by entering the following URL: https://<hostname>:1158/em

Note: If you are using Enterprise Manager Grid Control 10.2.0.5, specify the appropriate URL for your environment.

Enter the following information to log in to Enterprise Manager Database Control:

Username: SYS

Password: ********

Connect As: SYSDBA

Note: If you are using Enterprise Manager Grid Control, login using the SYSMAN database account.

Click the Schema tab on the Database Instance Home page.

Note: If you are using Enterprise Manager Grid Control, navigate to the Database Instance Home page by clicking Targets tab > Databases tab > Oracle database SID link. You may be asked to set the preferred database credential. If so, use the SYSTEM database account.

Click Tables in the Database Objects region.

Enter the following information:

Schema: hr_test

Object Name: employees

Click Go.

Select View Data in the Actions list. Click Go.

View the data in the HR_TEST.EMPLOYEES table to determine likely candidates for data masking. For ease of comparison after perform the data masking operation, click EMAIL to sort the rows on the EMAIL column.

The rows are now sorted based on the EMAIL column. Keep this browser window open for comparison with the data after it has been masked. In the next section, you will begin masking the columns that you have identified as containing sensitive data.

Open a new browser window or tab, and launch Enterprise Manager Database Control by entering the following URL: https://<hostname>:1158/em

Note: If you are using Enterprise Manager Grid Control 10.2.0.5, specify the appropriate URL for your environment.

Enter the following information to log in to Enterprise Manager Database Control:

Username: SYS
Password: *********
Connect As: SYSDBA

Click Login.

Note: If you are using Enterprise Manager Grid Control, login using the SYSMAN database account.

On the Database Instance Home page, click the Schema tab.

Note: If you are using Enterprise Manager Grid Control, navigate to the Database Instance Home page by clicking Targets tab > Databases tab > Oracle database SID link.

Click Definitions in the Data Masking section.

On the Data Masking Definitions page, Click Create.

On the Create Masking Definition page, enter the following:

Name: HR Employee Mask

Description: HR Employee Masking Policy

In the Columns section, click Add.

On the Add Columns page, enter the following:

Schema: hr_test

Table Name: employees

Click Search.

Notice that the Comment column contains information supplied by the application DBA indicating which columns are mask candidates. Select the EMPLOYEE_ID column. Click Add.

Note that the foreign key columns were automatically added to the masking definition. On the Create Masking Definition page, click the + icon under the Dependent Columns heading.

On the Add Dependent Columns page, enter the following:

Schema: hr_test

Table Name: managers

Click Search.

Select the MGR_ID column. Click Add.

On the Create Masking Definition page, click the icon under the Format heading.

On the Define Column Mask page, select Random Numbers from the Format Entry list. Click Add.

Enter the following information:

Start value: 100000

End value: 999999

Click the icon in the Sample column to view sample data.

Sample data is displayed. You can click the icon again to view additional sample values. Click OK when you have finished viewing sample values.

The Data Masking Definitions page is displayed showing the HR Employee Mask masking definition.

On the Data Masking Definitions page, click the Format Library link.

On the Format Library page, click Create.

On the Create Format page, enter the following information:

Name: Anglo-American First Name

Description: Masking format for first name

Select Table Column in the list and click Go.

On the Create Format page, enter the following information:

Table Name: hr_test.mask_data

Column Name: first_name

Click OK.

On the Create Format page, click OK.

A confirmation message is displayed on the Format Library page. Click Create.

On the Create Format page, enter the following information:

Name: Anglo-American Last Name

Description: Masking format for last name

Select Table Column in the list and click Go.

On the Create Format page, enter the following information:

Table Name: hr_test.mask_data

Column Name: last_name

Click OK.

On the Create Format page, click OK.

A confirmation message is displayed on the Format Library page.

On the Format Library page, click the Data Masking Definitions link.

On the Data Masking Definitions page, select HR Employee Mask. Click Edit.

On the Edit Masking Definition: HR Employee Mask page, click Add.

On the Add Columns page, enter the following information:

Schema: hr_test

Table Name: employees

Click Search.

On the Add Columns page, select the FIRST_NAME and LAST_NAME columns. Click Add.

On the Edit Masking Definition: HR Employee Mask page, click the Format icon in the FIRST_NAME row.

On the Define Column Mask page, click Import Format.

On the Import Format page, select Anglo-American First Name. Click Import.

Click the icon in the Sample column to view sample data.

On the Define Column Mask page, click OK.

On the Edit Masking Definition: HR Employee Mask page, click the Format icon in the LAST_NAME row.

On the Define Column Mask page, click Import Format.

On the Import Format page, select Anglo-American Last Name. Click Import.

On the Define Column Mask page, click the icon in the Sample column to view sample data.

On the Define Column Mask page, click OK.

On the Edit Masking Definition: HR Employee Mask page, click OK.

On the Data Masking Definitions page, observe that four columns are masked.

On the Data Masking Definitions page, select HR Employee Mask. Click Edit.

On the Edit Masking Definition: HR Employee Mask page, click Add.

On the Add Columns page, enter the following information:

Schema: hr_test

Table Name: employees

Click Search.

On the Add Columns page, select the SALARY column. Click Define Format and Add.

On the Define Column Mask page, select Shuffle from the Format Entry list. Click Add.

On the Define Column Mask page, click the icon in the Sample column to view sample data.

On the Define Column Mask page, click OK.

On the Edit Masking Definition: HR Employee Mask page, click OK.

On the Data Masking Definitions page, select HR Employee Mask. Click Edit.

On the Edit Masking Definition: HR Employee Mask page, click Add.

On the Add Columns page, enter the following information:

Schema: hr_test

Table Name: employees

Click Search.

On the Add Columns page, select the COMMISSION_PCT column. Click Define Format and Add.

On the Define Column Mask page, select Shuffle from the Format Entry list. Click Add.

On the Define Column Mask page, click the icon in the Sample column to view sample data.

On the Define Column Mask page, click OK.

On the Edit Masking Definition: HR Employee Mask page, click OK.

On the Data Masking Definitions page, select HR Employee Mask. Click Edit.

On the Edit Masking Definition: HR Employee Mask page, click Add in the Columns section.

On the Add Columns page, enter the following information:

Schema: hr_test

Table Name: employees

Click Search.

Select the NATIONAL_ID column. Click Define Format and Add.

On the Define Column Mask page, click Add Condition.

On the Define Column Mask page, enter the following SQL query in the Condition field:

national_id in

(select national_id from hr_test.employees where country_id = 'UK')

Click Import Format.

On the Import Format page, select National Insurance Number Formatted. Click Import.

On the Define Column Mask page, click the icon in the Sample column to view sample data.

On the Define Column Mask page, click Add Condition.

On the Define Column Mask page, enter the following SQL query in the Condition field:

national_id in

(select national_id from hr_test.employees where country_id = 'US')

Click Import Format.

On the Import Format page, select Social Security Number Formatted. Click Import.

On the Define Column Mask page, click the icon in the Sample column to view sample data.

On the Define Column Mask page, select Default Condition. Select Preserve Original Data in the Format Entry list. Click Add.

On the Define Column Mask page, click OK.

On the Edit Masking Definition: HR Employee Mask page, click OK.

On the Data Masking Definitions page, observe that seven columns are defined for masking.

On the Data Masking Definitions page, select HR Employee Mask. Click Generate Script.

The Processing: Generating Data Masking Script page is displayed.

A message is displayed indicating that the script has been generated.

Scroll down the Script Generation Results: HR Employee Mask page. Expand Impact Report.

View the Impact Report and verify that there are no errors. Click Schedule Job.

On the Schedule Data Masking Job: HR Employee Mask page, enter the host credentials. Select Immediately in the Start section. Click Submit.

On the Data Masking Definitions page, a message is displayed indicating that the job has been submitted. Click View Job Details.

On the Job Run: MASKING_JOB_NNN page, verify that the Status is Succeeded. Click Database to return to the Database Home page.

Click the Schema tab on the Database Instance Home page.

Note: If you are using Enterprise Manager Grid Control, navigate to the Database Instance Home page by clicking Targets tab > Databases tab > Oracle database SID link.

Click Tables in the Database Objects region.

Enter the following information:

Schema: hr_test

Object Name: employees

Click Go.

Select View Data in the Actions list. Click Go.

View the masked data in the HR_TEST.EMPLOYEES table. ClickEMAIL to sort the rows on the EMAIL column.

Compare the values in the columns with the values you viewed prior to masking. Refer to your other Enterprise Manager Database Control window for the comparison.

Click OK to return to the Tables page.

Click the Database tab to return to the Database Instance Home page.

Return to your SQL*Plus window. Logged in to SQL*Plus as the SYSTEM user, execute the OBE_DM_cleanup.sql script.

Return to Enterprise Manager Database Control. Click the Schema tab. Click Definitions in the Data Masking section. On the Data Masking Definitions page, select the HR Employee Mask and click Delete.

The HR Employee Mask masking definition is deleted from the Enterprise Manager Database Control repository.