Oracle DATA MASKING WITH ORACLE ENTERPRISE MANAGER 12C DATA MASKING PACK FOR Oracle Database

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:

1. Existing EM12c pre-requisites in place for Data Masking
2. Install EM Agent (Plugin 12.1.0.3 or Higher )
3. Create Data Masking Definition for the application TESTMAPS
4. Execute masking

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.

• 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

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.

Originally published at http://manojshringarpure.wordpress.com on June 17, 2018.