How to create SQL masking data project templates

Applies to

ApexSQL Mask

Summary

This article explains how to create SQL masking data project templates and save them.

Description

During the creation of a copy of the database in a non-production environment for testing purposes, the non-privileged users who do not have access to see sensitive data will work with masking data. This process of migration databases can be repeated from day to day, week to week, etc.

SQL masking data tool offers the option to save masked columns and types of the mask in the project file. By using a project file the database administrator does not need to repeat the process of selecting columns and rules for masking again. The project file will save all selected columns and rules for masking, and it can be used again when needed.

For purpose of this article, the AdventureWorks2016 database will be used:

• Table: HumanResources.Department
  • Column: Name
    • Substitution type of the mask with Random generator

• Table: Person.Address
  • Column: AddressLine1
    • Substitution type of the mask with Predefined generator

• Table: Person.EmailAddress
  • Column EmailAddress
    • Substitution type of the mask with Regular expression generator

Define project file

Let’s say that a DBA who works on the migration of a database from production to the test environment does this task periodically e.g. week after week. Because database developers who will work on the test environment do not have the right to see the sensitive data, the database administrator needs to use the SQL masking data tool to mask that sensitive data.

When the application is started, the Connect to SQL Server dialog appears. Select SQL Server instance, type of the Authentication and the desired Database, and then click the Connect button:

On the application main screen, all available tables will be shown as can be seen below:

As mentioned previously, SQL masking data will be applied to table HumanResources.Department which contains the sensitive data in the Name column. So, let’s select the Name column and add the Substitution type of the mask with the Random generator.

To do that, check the check box near the name of the column, then click the Manage button from the Home tab:

The Manage masks window will appear, from the Replace original data tab click the New button:

The Create substitution mask window will then appear. It’s mandatory to type the Name of the substitution masks and from the Type drop-down list select Random type, then click the Apply button to apply created mask:

The next step is to add a Predefined generator of the Substitution mask to column AddresLIne1 which is in the Person.Addres table.

• Find more information about adding a Predefined generator, in the following article: How to mask SQL Server data with the predefined generator

The last column which contains sensitive data is EmailAddress, which is stored in the Person.EmaliAddres table. As mentioned before, the Substitution type of mask will be used with a Regular expression generator.

• Find more information about adding a Regular expression generator, in the following article: Mask SQL Server data using regular expression generator

When all masks are added to the desired columns, it’s time to check if everything is added as planned or if something is off. The best way to do this is to click on the Classified button from the Home tab. The application will sort all masked columns on the left side of the main window. Now, it’s much easier to track which type of mask is used for which column.

On the right side of the main window is the Preview panel. In this panel, results of the SQL masking data process are shown:

When everything is added as planned, it’s time to save the project file. This can be done by clicking the Save button under Projects, and then Save as option:

The Save As window will appear as shown below. Select the desired folder where the project file should be saved, then type the file name, and click the Save button:

To open the saved project file, click the Open button from the Home tab, then select the previously saved project file and click the Open button:

When the project is opened, all previously added masks can be seen by click on the Classified button from the Home tab:

After processing and updating the database with masked sensitive data, this database can be deployed in the test environment.

Next time when the DBA has the same task, all previously added masks will be loaded by opening the saved project file. DBA can now continue to mask new columns that contain sensitive data.

FAQs

Q: If I create for example Specific value generator, then save the project file, will the Specific value be saved also?

A: Yes, it will be saved.

In the project file the following types of the mask will be saved as a template:

• Substitution
• Redaction

Substitution type of mask contains the following generators:

• Predefined
• Use original
• Random
• Specific value
• Regular expression
• SQL Script
• External source

The application will save all previously selected generators which are used to mask sensitive data.

Q: If a new column is added in the table, and the previously created project is opened, will the newly created column be loaded?

A: Yes, the newly added column will be loaded and shown in the main grid.