Pre-step 1: Creation of Database and Seedlist Tables

Before performing a Data Mask, you need to create the requisite databases, tables and functions:

  • Either create a Database to hold user-defined functions and seed-data tables or create a schema to hold these functions and tables in the database you are masking. Note that the credentials you supply to access the database being masked also need to have access to the user-defined functions and seed-data tables.

  • Create the required tables by running C:\VIPTDM\SubsetCloneAndMask\EnvironmentPrep\DDL\MySQL\MySQL_Tables_Create.sql.The SQL supplied here assumes that the tables are to be created in schema [dbo] in database [Masking]. If you wish to store the tables in a different location edit [Mask] and [dbo] as required in the SQL file.

  • Create the required user-defined functions by runningC:\VIPTDM\SubsetCloneAndMask\EnvironmentPrep\DDL\MySQL\MySQL_Functions_Create.sql. As above you may need to edit [Mask] and [dbo].

Note: the simplest way to run the supplied SQL is to connect to your database with SQL Server Management Studio, open a new query window, paste the SQL statements into it and click on the Execute button. See https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15  for full technical documentation on SQL Server Management.

Pre-step 2: Configuration (Setting the Database Details)

Once you have set up the requisite databases, tables and functions, you need to point the automation VIP workflows to the databases and schema that they will execute actions against. The Configuration Tab is used to define the connection details that will be used by the automated Data Mask actions:

  • Open the Control Spreadsheet – VIPMaskSQLServer.xlsx . This is contained in C:\VIPTDM\SubsetCloneAndMaskRun\Mask\SQLServer\VIPMaskSQLServer.xlsx.

  • Select the Configuration Tab

  • IMPORTANT STEP: In the Database Connection row, add the Database Connection details for the database you want to use.

  • Name the database and schema you wish to mask in the Staging Database and Staging Schema rows

Initially the control spreadsheet should contain the following sheets:

SoftKeys

This sheet can be used to provide details of foreign key relationships which are not enforced by foreign key constraints in the database

ScanParameters

This sheet specifies the column datatypes which the SCANCOLUMNS action (see below, Run SCANCOLUMNS action) will process

MaskingFunctions (User-defined functions)

This sheet provides the names of the user-defined functions available for masking. Note that if these functions are not defined in [Masking].[dbo] you should edit [Masking].[dbo] to your chosen [DB].schemas.

NOTE: Be sure to close this Control spreadsheet before going further in Masking as the next steps will need to update the Control spreadsheet which cannot happen if it is open.

This step only needs to be run one time.

Pre-step 3: Populate Seedlist

MySQL_Tables_Create.sql runs as part of the initial set-up and only defines the seedlist tables but does not populate them. To load data into the tables you should run command script Mask_InsertLists.cmd which is in

C:\VIPTDM\SubsetCloneAndMaskRun\Mask\MySQL\EnvironmentPrep

Input: Seedlist folder containing .csv files (C:\VIPTDM\SubsetCloneAndMask\EnvironmentPrep\Seedlists)

Function: This step runs SQLMask.enc.vip which reads each .csv file in the seedlist folder. The file name is used as the seedlist name and the contents of the file are inserted into the database seedlist table.

Note: Pre-steps 1-3 must have been run before running the remaining Masking steps.

Data Masking

Data Masking using VIP Server

TEST DATA AUTOMATION DATA MASKING