OBJECTIVE
The objective of this POC is to obfuscate sensitive data in a Oracle database using Oracle Data Masking Pack.
For this the below activities need to be completed in order to accomplish the objective:
- Existing EM12c pre-requisites in place for Data Masking
- Install EM Agent (Plugin 12.1.0.3 or Higher )
- Create Data Masking Definition for the application TESTMAPS
- Execute masking
OVERVIEW
Oracle database masking requires Oracle Enterprise Manager (OEM) 12c, Oracle Data Masking Pack, an Oracle database. The target Oracle Database TESTMAPS can be created using duplicate database of RMAN or using normal backup restore capabilities of RMAN.
For the POC we have used the TESTMAPS for Application Data Model (ADM) creation, Data Masking Definition and subsequently masking the same Database. This was done were provided access only to test Database.
In theory you will need to create ADM and Data Masking Definition in production Database and execute the mask script in the staging/test database.
ASSUMPTIONS
- EM12c ( 12.1.0.2 or higher )
- DB Plugin 12.1.0.3 with Patch 16438087 or higher for Agents
- The Oracle Database server TESTMAPS is already discovered in EM12c
DATA MASKING DEFINITION CREATION
We will now work on creating the data masking definition.
Login to EM
Navigate to Data Discovery and Modeling
Enterprise -> Quality Management ->Data Discovery and Modeling
Create the Application Model with name TESTMAPS the source database will be ORACLE DATABASE server TESTMAPS
ADD the schema MAPSPROD which has the tables that are required for Masking.
Submit Job for Metadata Collection
Next we edit the ADM TESTMAPS to add tables to it if required, also add any dependent columns if they do not have referential integrity at Oracle level.
To add tables manually ( at a later Stage post discovery)
Example Mps_daily_txn_poc1
Add and mark the required columns as sensitive (THIS IS IMPORTANT if not done then columns will not be visible during masking definition step.)
SAVE and RETURN
Navigate to Data Masking Definition
Enterprise ->Quality Management ->Data Masking Definition
Click Create for New Masking Definition
Provide name of Masking Definition, ADM and Reference Database
Click Add to Add relevant columns
Choose Schema Name, Table Name and Column Name and click the relevant column and click define format.
Next Select the sensitive column and click define format and add
For POC the column MDT_CRD_NUM had mix of 16 and 19 digit credit card number with Master ,Visa ,Maestro and Rupay Formats, therefore we used condition using ADD Condition.
Here condition is MDT_CRD_NUM like ‘4%’ for Visa Credit Card Numbers
Then we import the VISA Format using IMPORT FORMAT Button.
Similar step for Master Card
MDT_CRD_NUM like ‘5%’ and MDT_CRD_NUM not like ‘5044%’
For Default condition use Preserve Original Data from FORMAT ENTRY
Click OK
For faster masking of large tables we can add parallel clause
I.e. Use parallel execution when possible value 8
Parallel degree will depend on number of cores and threads available in the Target Oracle Database server.
Example if 4 core and 2 threads we can have parallel degree of 8.
Click OK to Save Masking Definition
Next Step is to generate the Script as shown below
One can view the generate script job details using View Job Details.
To save Script for manually masking use save script option of Actions or to view script use view script option from Actions.
Next Step is to Schedule Job for Masking Execution
Click Schedule job , provide named credentials ( Host and Database ) and click submit.
You can click view job details to see the progress.
Once the job is successfully completed, you can go and select the columns to check the masked data.
END OF DOCUMENT
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
manojshringarpure 