MSSQL Data Masking with EM12c

OBJECTIVE

 

The objective of this Document is to  obfuscate sensitive data in a MS SQL Server database using Oracle Data Masking Pack and Oracle Database Gateway.

 

For this the below activities need to be completed in order to accomplish the objective:

1. Install Database Gateway for Microsoft SQL Server 11.2.0.3
2. Configure Database Gateway for Microsoft SQL Server 11.2.0.3
3. Configure oracle database for communication between SQL Server database CCRS_2_0
4. Create Data Masking Definition in Oracle Database FRSUAT for MSSQL Server
5. Execute masking

OVERVIEW

Oracle heterogeneous database masking requires Oracle Enterprise Manager (OEM) 12c, Oracle Data Masking Pack, an Oracle database, a heterogeneous database and a method of extracting and loading database from/to the heterogeneous database. For the extract and load, we will use Oracle Database Gateway 11.2.0.3.

 

For the heterogeneous database we will be using one MS SQL Server 2008 database CCRS_2_0.

Here we have shown how to extract data using Oracle Database Gateway from one heterogeneous database and load into same. Hence we will extract the data from CCRS_2_0MS SQL Server database into Oracle database FRSUAT, mask it, and then load the masked data into same CCRS_2_0

Oracle Data Masking Pack for non-Oracle Databases includes a restricted use license of the following Oracle Database Gateways:

• Database Gateway for APPC
• Database Gateway for DRDA
• Database Gateway for Informix
• Database Gateway for SQL Server
• Database Gateway for Sybase

If you want to use the Oracle Database Gateways listed above not solely for the purposes of Oracle Data Masking, then you must purchase full-use licenses for the Oracle Database Gateways.

Oracle Database Gateway 11.2.0.3 comes with the full installation of the Oracle Database software, 11.2.0.3 patch set update in the p10404530_112030_LINUX_5of7.zip file.

Before we start with the Masking, we require certain pre-requisite steps to be completed which are listed in the next section.

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 FRSUAT is already discovered in EM12c

 PRE-REQUISITE STEPS

To prepare for the labs please begin in order as listed below.

INSTALL DATABASE GATEWAYS FOR MS SQL SERVER

Overview

1. We will be using Oracle Database Gateway 2.0.3 to communicate with the heterogeneous databases. In this exercise we will install and the gateway to communicate with Microsoft SQL Server 2008 via the Oracle FRSUAT database

 

2. Ensure new ORACLE_HOME is set pointing to Gateway Home                                        cd /u01/home/oracle/patches/gw11203/gateways                                                      ./runInstaller
3. 

Click on Next

2. Accept the defaults.
3. Click on Next
4. Select Oracle Database Gateway for Microsoft SQL Server component Click on Next button
5. We need to enter the SQL Server details:In your /etc/hosts we have paired your specific SQL Server, so make sure the correct IP is mapped to hostname   in this file.Enter the below:

    

   SQL Server Database Server Host Name	=	X.X.X.X
   SQL Server Database Server Port Number	=	2435
   SQL Server Instance Name	=
   SQL Server Database Name   Click on Next	=	Ccrs_2.0

 Next

1. When complete, start a new terminal as root and execute the below sh script as root and then click OK button. It is extremely important that you complete this step successfully and then continue.
2. As root in a terminal window execute:
3. cd /u01/home/oracle/gw11203
4. ./root.sh
5. The installer will continue to configure Oracle Net however we will cancel this part of the install as you will perform this
6. Click on Cancel
7. Click on Yes button
8. Click on OK button
9. Click on Next button – Configuration Assistants
10. Click on OK button
11. Installation is Click on Exit button
12. Confirm by clicking on Yes

This completes the installation section. Next we will perform the necessary configuration to access the MS SQL Server from the Oracle database.

CONFIGURE ORACLE DATABASE GATEWAY FOR MICROSOFT SQL SERVER 11.2.0.3

Overview

Now that installation is complete we need to configure the initialization files. This will allow our ORACLE database to communicate with the SQL Server database

IMPORTANT NOTE: All the configuration files are sensitive to extra space,characters etc. Any unwanted or inaccurate changes will result in Gateway configuration giving errors. Use vi commands only to edit the files.

Do not use backspace or any keyboard keys other than in vi mode.

Let us look at the configuration files in our gateway home

The key configuration file that we will be working with is initdg4mtest this has been created from initdg4msql.ora. Additionally the install creates sample listener.ora and tnsnames.ora files that we will use to configure access to MS SQL Server from the oracle database.

 

Contents of  initdg4mstest.ora are given below

 

# This is a customized agent init file that contains the HS parameters

# that are needed for the Database Gateway for Microsoft SQL Server

#

# HS init parameters

#

HS_FDS_CONNECT_INFO=[10.9.X.X]:2435//ccrs_2_0

# alternate connect format is hostname/serverinstance/databasename

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

_HS_FDS_HGOEXEC_BUNDLING=TRUE

HS_FDS_FETCH_ROWS=2000

HS_RPC_FETCH_SIZE=500000

HS_KEEP_REMOTE_COLUMN_SIZE=REMOTE

Add TNS Entries as given in below screenshot , notice the connect string that is linked to the respective SID

In $ORACLE_HOME/network/admin/tnsnames.ora

Since the database that we will be using to mask is listening on the listener LISTENER_FRSUAT, let’s set the environment and edit the respective listener and tnsnames to include the database gateway service.

In the terminal window execute the below commands

cd $ORACLE_HOME/network/admin/

vi listener.ora

Add entries as shown below in unix terminal Save and close the file

1. Reload the listener by sourcing environment file for ORACLElsnrctl reload LISTENER_FRSUAT
2. Then run lsnrctl status LISTENER_PROD and you will see the gateway service in the report (dg4sql)

lsnrctl status LISTENER_FRSUAT

1. We now need to configure ORACLE database which will get the data from the production SQL Server database CCRS_2_0

Create user mssql in Oracle Database and grant it below privileges

• SELECT_CATALOG_ROLE for database users
• SELECT ANY DICTIONARY privilege for database users
• EXECUTE privileges for the DBMS_CRYPTO package

 

For this Setup we had also granted DBA privilege to MSSQL user
Log in to the database as system and create a database link to access the production SQL Server database.

sqlplus mssql

<enter the password>

create public database link dg4poc connect to “dg4mstest” identified by “pocmask” using ‘pocmask’;

Here pocmask is user in MSSQL Database with read write privileges

Then execute:

select count(*) from complaints@dg4poc;

These rows were retrieved from the SQL Server database CCRS_2_0

PULL TABLES INTO Oracle FROM MSSQL

 This step is required to create Application Data Model , the ADM is created in our Oracle Database  FRSUAT. The MSSQL is not used for creating ADM.

Login into Oracle Database using mssql user and do CTAS to pull our selected tables into Oracle from MSSQL

create table COMPLAINTS as select * from COMPLAINTS@DG4POC where rownum < 100;

create table tempcomplaints_create_format1 as select * from tempcomplaints_create_format1 @DG4POC where rownum < 100;

create table tempcomplaintsheet_new as select * from tempcomplaintsheet_new @DG4POC where rownum < 100;

 Depending on table size and rows you can pull entire tables or specific number of rows. In any case the tables inside Oracle are deleted and rows pulled a fresh from MSSQL during Pre Mask Step during Masking Execution.

DATA MASKING DEFINITION CREATION

We will now work on creating the data masking definition. This will include the necessary commands to extract the data from the SQL Server database CCRS_2_0 into Oracle Database FRSUAT and load the masked data into a same MS SQL Server Database CCRS_2_0

1. Login to EM
2. Navigate to Data Discovery and Modeling

   Enterprise -> Quality Management – >Data Discovery and Modeling

   

3. Create the Application Model with name MSSQL ADM the source database will be ORACLE DATABASE server FRSUAT, this server also has the gateway installed and configured.

   

4. Add the tables which we created in Oracle Database using create table commands (from Sql Server via DB Link)

   

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

6. Navigate to Data Masking Definition

   Enterprise -> Quality Management -> Data Masking Definition

   

7. Click Create for New Masking Definition

   

8. Click Add to Add relevant columns

    

Choose Schema Name, Table Name and Column Name

We are Masking the column “cardno” had 16 and 19 digit credit card number, therefore we are using condition using ADD Condition Button

Here condition is “cardno” like ‘4%’  ( NOTICE cardno is small case and needs to be mentioned in “” as they have been created using CTAS

 

Then we import the VISA Format using IMPORT FORMAT Button

Similar we can add condition for MASTER starting with Digit 5

“cardno” like ‘5%’ and “cardno”  not like ‘5044%’

Then use IMPORT FORMAT for MASTER

As default condition we use FORMAT ENTRY Preserve Original Data. (This will not have any effect in our case as the rows are either matching Visa or other card formats.)

Click OK

 

Click OK to Save Masking Definition

Now we need to mention the Pre Mask and Post Mask Steps as shown in below screenshot

Using the Pre Mask Steps we shall delete the existing data of Oracle tables which we had created using CTAS and then we shall pull data from MSSQL CCRS_2_0 into Oracle Database FRSUAT .

PRE MASK STEPS SAMPLE

 

delete  mssql.tempcomplaintsheet_new;

delete  mssql.onusfile;

insert into mssql.tempcomplaintsheet_new select * from tempcomplaintsheet_new@dg4poc;

insert into mssql.onusfile select * from onusfile@dg4poc;

commit;

 

 

Then the masking definition gets executed

Using the Post Mask Steps we shall copy data from Oracle tables using copy command into target MSSQL CCRS_2_0

POST MASK STEPS SAMPLE

 

delete  tempcomplaintsheet_new@dg4poc;

delete  onusfile@dg4poc;

commit;

 

copy from  mssql/mssql@frsuat insert tempcomplaintsheet_new@dg4poc using select * from mssql.tempcomplaintsheet_new;

copy from  mssql/mssql@frsuat insert onusfile@dg4poc using select * from mssql.onusfile;

 

ERRORS NOTE:

• If you encounter below error during copying from Oracle to MSSQL ERROR:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Cannot insert explicit value for identity column in table ‘onusfile_poc’ when IDENTITY_INSERT is set to OFF. {23000,NativeErr = 544}[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Cannot insert explicit value for identity column in table ‘onusfile_poc’ when IDENTITY_INSERT is set to OFF.

{23000,NativeErr = 544}[Oracle][ODBC SQL Server Wire P

 

Then add below PL/SQL before the copy in Post Masking Script

“Using the Pass-Through Feature

The gateway can pass SQL Server commands or statements from the application to the SQL Server database using the DBMS_HS_PASSTHROUGH package.

Use the DBMS_HS_PASSTHROUGH package in a PL/SQL block to specify the statement to be passed to the SQL Server database, as follows:”

declare

num_rows integer;

begin

num_rows :=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@DG4POC(‘SET IDENTITY_INSERT dbo.onusfile ON’);

end;

/

2.)  If you encounter below error while copying the table from MSSQL server to oracle db with LONG COLUMNS we are getting below error:

 

SQL>  insert into complaints select * from complaints@dg4poc;

insert into complaints select * from complaints@dg4poc

*

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

 

Then in initdg4mstest.ora add the below line

HS_KEEP_REMOTE_COLUMN_SIZE=REMOTE

 

Click OK to Save

Next Step is to generate the Script as shown below

Next Step is to Submit Job for Masking Execution that will pull delete existing data in Oracle , pull data from MSSQL into Oracle , Mask the data in Oracle and delete the data in MSSQL and  Copy the masked data from Oracle into MSSQL.

You can click view job details to see the progress

END OF DOCUMENT

 

REFERENCES

 

1. OTN Oracle Database Gateways home page: http://www.oracle.com/technetwork/database/gateways/index.html
2. Oracle Database Gateway Installation and Configuration Guide 11g Release 2 [E12013-04]
3. Gateway for SQL Server User’s Guide: http://docs.oracle.com/cd/E11882_01/gateways.112/e12069/toc.htm
4. Master Note for Oracle Gateway Products Article ID1
5. Heterogeneous Connectivity: http://www.oracle.com/pls/db112/gateways
6. Oracle Data Masking Pack and Test Data Management Pack Licensing Overview

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.