How to mask SQL Server data with the substitution mask with ApexSQL Mask (4312075)

This article will explain how to create a substitution mask and what types of generators are using to replace original data with masked one.

Database masking tool offers two types of masking data:

• Substitution – this type of masks uses different generators for replacing data entirely
• Redaction – to mask SQL Server data. This type of mask uses specific values, or parts of that values and replace it with custom value. More about the redaction type of masking can be found in the How to mask sensitive SQL Server data with redaction mask article

Creating the new substitution mask

To create a new substitution mask, under the Home tab, click the Manage button:

The Manage masks window will be opened, by default the Substitution radio button is checked:

By clicking the New button on the Manage masks window:

The Create substitution mask window will be opened. This window contains two radio buttons in the level sections:

• Column – is used for masking data on the column level
• Row – is used for masking data on the row level

In the Name text box enter a name for a new substitution mask and optional the brief description for a new substitution mask can be entered in the Description field:

From the Type drop-down list in the Create substitution mask window can be chosen the different types of generators, which will be used for mask SQL Server data:

• Predefined
• Use Original
• Random
• Specific value
• Regular expression
• SQL script
• External source

Types of the substitution generators

The Predefined generator

If the Predefined generator is selected form the type-drop-down list, it will offer the numerous generators which contain data from the different area of the life: Art, Auto industry, etc. For more information, see the How to mask SQL Server data with the predefined generator article.

The Use original generator

The Use original generator uses the data from the selected column (original data) in masking, as its name suggests. By using this generator, the database developer will have the opportunity to mask SQL Server data with original mixed data by the checked the shuffle check-box:

In the following example, it will be shown how the Use original generator feature will mask data:

The Random generator

With this generator, the data from the selected column will be masked with randomly chosen symbols. The Random generator will be used one or more characters for replacing the data from the selected column ( the number of characters is not related to the number of characters in the original data):

The Specific value

In this case, a specific value or null value is used to mask SQL Server data.

• Null – null value will be used on nullable columns, and original data will be masked with null
• Value – the original data will be replaced with any value that is typed in the Value text-box

The regular expression

A regular expression generator makes data based on the given sequences and that values will be used for replacing original data. In the Create substitution mask window bellow the Type drop-down list (where is chosen the regular expression generator ) will appear:

1. Text box – where the appearance of the regular expression will be displayed or can type how the regular expression will be looked

2. Insert – in this section, the three sequences can be used to define the regular expression:

   • List – contains a list of the predefined generators used for making the regular expression. Besides predefined generators, a text or CSV file can also be used:

   

   • Snippet – in this drop-down list can be found a list of the snippets, which can be used for the sequence:

   

   • Counter – under this drop-down list can be chosen order of how the regular expression will be displayed:

   

3. Separator – text box for separator, mainly used for CSV files
4. Refresh – button for refreshing the Preview window where the newly created regular expression is appears
5. Preview window – in this window can be seen data made by the regular expression generator, which will be used to mask SQL Server data

The SQL Script generator

When the SQL Script generator is used, the original data will be masked with data that is made by using an SQL script. More information about this can be found in the How to mask data in SQL databases using the substitution rule type with the SQL script generator article.

The External source generator

The external source generator will masked original data by using data from Excel, CSV, JSON, XML files. The explanation about how to use external data sources can be found in How to mask sensitive SQL Server data with external data sources article.

Applying a mask to columns

When the substitution generator for masking data is created, the next step is to apply the mask to the selected columns. In the Create substitution mask window, click on the Apply button:

The Apply to columns window will be shown. From the Available section, select the columns that want to be masked and click the right blue arrow to move selected columns to the Applied section on which the newly created substitution mask will be applied. Click on the OK button to close the Apply to columns window and confirm the changes:

On the Create substitution mask window, click the OK button to create a new substitution mask, which will appear in the Manage mask window: