Welcome to the Delphix Masking Lab Guide. This guide is a supplement to the Delphix Admin Training for Oracle course, and provides several exercises to perform throughout the class. If you encounter any issues during the exercises, please do not hesitate to ask your instructor for advice.
Your instructor should have provided you with a Class Name and a Student Number. In order to access your lab server, point your web browser to: http://classname.agile.today/studentnumber
For example, if your Class Name is "acmetech" and your Student Number is 5, you would go to the site: http://acmetech.agile.today/5
| Host | IP |
|---|---|
| Delphix Masking Engine | 10.0.x.10 |
| Linux Source | 10.0.x.20 |
| Linux Target | 10.0.x.30 |
In the above IP addresses, the x denotes your Student Number. For example, if your student number is 5 , your Delphix Data Platform will be located at 10.0. 5.10.
| Credentials | |
|---|---|
| Initial Delphix Masking Engine Admin user | Admin |
| Initial Delphix Masking Engine Admin password | Admin-12 |
| Initial Delphix Virtualization Engine admin password | delphix |
| Source and Target delphix user (via SSH) | delphix |
| Source and Target oracle user (via SSH) | delphix |
In this exercise, you will:
-
Access the Masking Engine GUI
-
Log in to the Delphix Masking Engine
Steps
-
Connect to your Delphix Engine using Google Chrome on your lab server (see the Important IP Addresses section of the Getting Started guide above).
http://10.0.x.10/masking (replace 'x' with your student number)
Troubleshooting tip: If the above URL doesn't work, you might need to set up the masking engine first. In this case, you should log into the Delphix engine using the sysadmin user (user and password both = sysadmin and follow the Masking setup wizard).
-
Enter your User Id and Password
a. Default Username is Admin
b. Default Password is Admin-12
-
Close the Welcome Wizard by clicking on the X on the top right corner of the page. You should now be viewing the Environments page.
In the proceeding exercises we will add an application and associate with an environment.
In this exercise, you will:
- Add an Application
Steps
-
On the Environments List/Summary screen click on the Add Application button
-
Enter "Medical App Dev" for the Application name in the Application pop up box
-
Click on the Save button
The application should be added successfully.
In this exercise, you will:
- Add a new Environment
Steps
-
On the Environments List/Summary screen click on the Add Environment button
-
Select the Application Name from the drop-down list created in the previous exercise
-
Enter "Oracle Dev DB" for the Environment Name
-
Select Mask from the Purpose drop-drown list
-
Click on the Save button
The environment should now be visible in the Environment List/Summary screen.
In this exercise, you will:
-
Select an Environment
-
Create a Basic Oracle Database Connection
Steps
Note: If your class was stopped you will need to manually start it from the Delphix Engine before proceeding with the following exercise. Ask your instructor if you are unable to complete this step.
-
Log into the Masking Engine
-
Click on the Oracle Dev DB environment created in the previous exercise
-
Click on the Connector tab
-
Click the Create Connection button
-
Enter Connection Details
a. Chose Database - Oracle from the Type drop-down list
b. Verify that the Basic radio button next to the Type
c. Enter "Oracle 11g Dev" for the connection name
d. Type DELPHIXDB (in uppercase) in the Schema Name text box
e. Enter the IP address of TargetA (10.0.x.30 where 'x' is your Student Number) in the Host Name/IP field
f. Enter devdb as the SID
g. Enter 1521 as the port number for the listener on Linux Target
h. Enter DELPHIXDB (in uppercase) as the DB login ID
i. Provide the password: delphix
j. Click the Test Connection button. A Success message indicates whether the test was successful or not.
k. Save the connection
The connector should now be visible in the Connectors list for the environment.
In this exercise, you will:
- Create a Rule Set for Patient data
Note: A "rule set" points to a collection of tables or flat files that the masking engine uses for profiling, provisioning, and masking, and certifying data. For mainframe systems, the rule set represents a copybook definition for a file.
Steps
-
On the Environment List/Summary screen, click on the Environment Name created in the previous exercise
-
Click on the Rule Set tab
-
Click on the Create Rule Set button
-
Provide the details for the rule set as follows:
a. Enter "Patient Rule Set" in the name field for the Rule Set
b. Select Oracle 11g Dev from the Connector drop-down list
c. Check the boxes next to MEDICAL_RECORDS, PATIENT and PATIENT_DETAILS tables
- Click on the Save button
You will see the newly created Rule Set in the list for the environment.
In this exercise, you will:
-
Review an Inventory after creation of a Rule Set
-
Edit an Inventory to add or remove an assignment
Steps
-
On the Environment List/Summary screen, click on the Environment Name created in the previous exercise
-
Click on the Inventory tab
-
From the Select Rule Set drop-down menu choose the Patient Rule Set created previously
-
A list of tables will be displayed under the Contents section. Clicking on the name of each table will allow you to view the table metadata.
a. Click on the PATIENT table
b. Verify if there are any primary keys, foreign keys or indexes identified
c. Validate the Data Type of each Column
-
Edit the ADDRESS column by clicking on the corresponding pencil icon
-
Change properties of the column
d. Set Domain to ADDRESS
e. Choose the ADDRESS LINE SL algorithm
f. Select User for ID Method
g. Click Save
In this exercise, you will:
-
View Profiler Settings
-
Create a profiling job
-
Execute a profiling job
-
Assign Rule Set and Profile Set
-
Review inventory after profiling job has run
Steps
-
Click on the Settings tab
-
Click on Profiler under Domains in the left margin of the settings page
-
View the profiler expressions
a. Domain & Expression
b. Name
c. Level
-
Click on the Add Profiler Set button to view the default Profile Sets
-
Click Cancel and navigate back to the Environment List/Summary screen
In the next part of the exercise we will create and execute a profiling job.
-
Click on the Oracle Dev DB Environment
-
Click on the Profile button
-
On the Create Profile Job screen enter the details as follows:
d. Select Patient Rule Set from the Rule Set drop-down list
e. Enter Profile Patient Job for the job name
f. Choose HIPAA from the Profile Sets drop-down list
g. Set No. of Streams to 1
h. Leave other fields at their default values
i. Click the Save button
-
The newly created profiling job will be displayed at the bottom of the Environment Overview screen with a status of Created
-
Click on the blue play button under Action to execute the job. The status should change to Running and the stop Action button will be enabled.
-
Click on the Monitor tab to see the list of jobs and their progress
-
Click on the job name to view the job details
-
View the Completed, Processing, Waiting and Results tabs for the job
Note that the Results tab displays the number of sensitive columns and tables discovered by the profiler
In the next part of the exercise, we will review the Inventory after running the profile job.
-
Go to the Environment List/Summary screen by clicking on the Environments tab and choose the Oracle Dev DB environment
-
Click on the Inventory tab for the environment
-
Select the Patients Rule Set from the Rule Set drop-down list
-
Click on the MEDICAL_RECORDS table
Note that the Algorithm column has been populated for sensitive columns identified during profiling
- Click on the PATIENT and PATIENT_DETAILS tables, and note the Algorithms for the sensitive columns
In this exercise, you will:
-
Create an In-Place Masking Job
-
Execute Masking Job
-
View Results from Masking Job
Steps
-
On the Environment List/Summary screen, click on the Oracle Dev DB Environment Name created in the previous exercise
-
Click on the Mask button
-
Enter Details in the Create Masking Job dialog
a. Job Name: Patient Masking
b. Masking Method: In-Place
c. Rule Set: Patient Rule Set
d. No. of Streams: 1
e. Update Threads 1
f. Leave other fields at the default values
The Create Masking Job dialog should look like the screenshot shown below.
g. Click on the Save button
- The Masking job will appear on the Environment summary screen with astatus of Created
-
Before executing our masking job, we need to check the values of the PATIENT table before running our masking job, and again after to verify that the data has indeed been masked.
-
Open the Terminal app on the lab desktop
-
Type the following commands in the terminal window to run the script to query the PATIENT table
Note: You may need to widen your terminal window to prevent the output from wrapping. After typing in each command press the Enter or Return key on your keyboard.
a. cd Scripts/Masking602 (Note: the number after masking could be different if you are using a more up-to-date classrooom; confirm with your lab instructor should you have difficult here, or ls to find the correct selection.)
b. ./check_patient_devdb.sh
NOTE: Oracle SQL Developer is also available on the desktop to view the tables
Do not close the terminal window as you will need to validate that the data has changed after successfully executing the masking job.
- Click on the blue play button under the Action column that corresponds to the newly created Masking job to execute it
The job status will change to Running
- Click on the Monitor tab to view the progress and status of the job
- Click on the name of the job to view its details
- Click on the Completed, Process and Waiting tabs in the job details screen and note the results once the job has completed
- Click on the environment name to go back to the Environment summary screen. The status of the masking job should now be Succeeded
-
Open a new terminal window on the lab desktop and execute the following script:
~/Scripts/Masking602/check_patient_devdb.sh
Note: The values for the Firstname, Lastname, Address, Zipcode, Phone_number, and Email have been masked using the secure lookup algorithm while the values for the City column have been set to NULL.
In this exercise, you will:
-
Create a lookup file
-
Build a secure lookup algorithm
-
Update your inventory to use the new algorithm
Steps
-
Open the text editor on the desktop (mousepad)
-
Enter several names in the editor, one value per line, and save to desktop as FN2.txt
-
Navigate to SETTINGS -> ALGORITHMS in the masking UI
-
Select
-
With Secure Lookup Algorithm radial button selected enter Algorithm Name: Name2SL, a Description as shown below, and click Select to select the file you created in step 2 from your desktop
-
Save the algorithm and navigate to the Inventory of the Patient Rule Set
-
Select the Patient table from the table list on the left hand margin
-
On the Algorithm drop down select the new First Name2 SL algorithm (you will need to click on the Edit pencil next to the FIRSTNAME column to get here) and click Save
In this exercise, you will:
-
Create a segmented mapping algorithm
-
Assign the algorithm to your inventory
-
Execute a masking job
-
View the masked data
Steps
-
Navigate to Settings -> Algorithm
-
Select Add Algorithm
-
With the Segmented Mapping Algorithm radio button selected
a. Name the algorithm SSN SM
b. Define 3 alpha numeric segments of 3, 2, and 4 length
c. Click the Ignore comma checkbox
d. Define ignore characters of "-"," ","/" (dash, space, forward slash)
e. Save the algorithm
-
Navigate to your Environments
-
Click the Oracle DEV DB environment then choose the Inventory tab
-
Select the Patient table from the table list on the left hand margin
-
Click the edit pencil next to the SOCIAL_SECURITY_NUMBER column
-
From the domain drop down select the SSN domain
-
From the algorithm drop down select your SSN algorithm and save the changes
-
Navigate to the Overview tab and select the blue play button to run the Patient Masking job
-
When the job completes, view the Patient table data (with the check_patient_devdb.sh script from terminal session or SQL Developer)
-
Note that the FIRST_NAME column now contains values from our lookup file
-
Note that the SOCIAL_SECURITY_NUMBER column has now been masked
This concludes the exercise.
In this exercise, you will:
- Export an Inventory
Steps
-
On the Environment List/Summary screen, click on the Environment Name created in the previous exercise
-
Click on the Inventory tab
-
From the Select Rule Set drop-down menu choose the Patient Rule Set created previously
-
Click on the Export button
-
Accept the default inventory name and file name
-
Click the Save button
Note that a pop-up window should appear with a link to Download the inventory file. If the pop-up window does not appear, check the browser to see if it was blocked and allow it to display pop-ups.
-
Right-click on the link that appears on the pop-up window and select "Save link as..."
-
Save the file to the Desktop
This concludes the exercise.
In this exercise, you will:
-
View exported Inventory
-
Modify an exported Inventory
Note: You should have completed the "Export an Inventory" exercise before proceeding.
Steps
-
Open the exported CSV file from the previous exercise (opens with LibreOffice Calc by default)
NOTE: LibreOffice is the open source equivalent for MS Office Excel
Uncheck the Tab and Semicolon checkboxes under Separator options in the Import dialog box. Only the comma box should be checked.
-
Remove a column from the masking inventory
a. Note that columns H, I, J contain a domain, algorithm and "true" for sensitive columns
b. Note that columns H, I, J contain '-', '-', 'false' for columns that are not sensitive
c. For Patient table, DOB column, copy a dash into column H and I and copy false from another row into column J.
d. Save the changes
Note: A confirmation dialog box will pop up asking to confirm the file format. Click the "Use Text CSV Format" button.
-
Switch back to the Masking UI in the web browser and navigate to the Inventory for the Patient Rule Set and press the Import button
-
Select the modified CSV file from your desktop (Note: you may need to scroll down on the import dialog box to see the Save button)
-
Refresh the masking UI browser session
-
DOB should no longer be selected for masking
Note: This method of modifying an inventory can be used to speed up a large number of changes as compared to making inventory changes via the UI
In this exercise, you will:
- Refresh a rule set
Steps
- Using SQL Developer add a new column called BIRTHDAY to the PATIENT_DETAILS table, data type is date
-
Navigate to the Inventory tab in the Masking UI and select our Patient Rule Set
-
Select the PATIENT_DETAILS table in the table list on the left
Note: The new column is not shown in the inventory
-
Click the Rule Set tab and select the blue Refresh icon
-
Click the Inventory tab and note that the new column now appears in the Inventory
Note: This method is used to bring schema changes into the masking inventory
In this exercise, you will:
- Copy a rule set
Steps
-
Navigate to the Rule Set tab in the UI
-
Click the Copy icon
-
Name the new Rule Set Patient Copy and click Save
-
Click the Inventory tab, select the new Rule Set from the Select Rule Set drop down
- Confirm that the Masking Inventory is copied along with column definitions
Note: This method is used to duplicate an inventory
In this exercise, you will:
-
Select an Environment
-
Export an Environment
-
Log into the Masking Engine
-
Click on the Export icon corresponding to the Oracle Dev DB environment
- On the Export Environment dialog box accept the default Environment Name and File Name
- Click the Export button
Note: A pop-up window should appear with a link to Download the environment XML file. If the pop-up window does not appear, check the browser to see if it was blocked and allow it to display pop-ups.
- Right-click on the link that appears on the pop-up window and select "Save link as..."
- Save the file to the Desktop
In this exercise, you will:
- Import an Environment from a previously created export file
**Note: You will need to perform the exercise "Export an Environment" before proceeding **
-
Add a new application for QA
a. Click the Add Application button
b. Enter the name Medical App QA
-
On the Environments List/Summary screen click on the Import Environment button
-
On the Import Environment dialog box, select Medical App QA from the Application Name drop down
-
Enter "Oracle QA DB" for the environment name
-
Click on the Select button and browse to the location of the previously exported file
- Click on the Save button.
The Oracle QA DB environment will be displayed on the Environment list/Summary screen
Note: You may need to navigate to another tab and click Environments tab again to see the imported environment.
Note: This function is used to replicate a Masking environment and jobs for another copy of the schema
This completes the lab session