Applies to
Summary
This article explains how to mask SQL Server data using a specific value generator.
Description
ApexSQL Mask is a data classification and masking tool, that easily masks and replaces sensitive SQL Server data. For masking and replacing data, ApexSQL Mask uses two types of masks:
- Substitution
- Redaction
The Substitution type of mask uses generators to create data that will be used for masking and replacing sensitive data. Among these generators, there is a Specific value generator which usage will be explained in this article.
Mask SQL Server data
When connected to the selected database, this database masking tool will present the classified columns automatically in the main grid window, by default. These columns are with sensitive data in them and therefore automatically assigned masks to.
To easily distinguish classified columns from regular ones, classified columns have a red dot in front of their name:
Besides already classified sensitive columns, ApexSQL Mask can mask any other column from the selected database.
To mask a column from the selected database, click on the All columns button under the Home tab:
This action will list all columns in the grid panel. Tick the checkbox in the front of the name of the column to select columns for masking, and the red dot will appear.
For this article, the AddressLine2 column from the Person.Address table in the AdventureWorks2019 sample database will be used:
To add already predefined mask, right-click on the selected column, and from the context menu choose the Add mask command. This will open a submenu where type and name of a mask can be chosen that will be applied:
Also, for the chosen columns, the newly created mask can be applied to.
To create a new mask, click on the Manage button under the Home tab:
The Manage mask window will be opened with all applied masks to the selected database:
To create the Substitution type of mask, tick the Substitution checkbox under the Type section, and then click on the New button:
The Create substitution mask window will appear, wherein the Level section, the Column is checked by default. In the Name text box, it is required to enter the name of the new mask, while the Description text box is optional and used to put a description of the new mask:
When the above is set, the next step is to choose the type of generator from the Type drop-down list.
For this article, a Specific value generator will be chosen:
Specific value generator
When the Specific value generator is selected as an option to mask SQL Server data, two options will appear in the section below:
- Null
- Value
If the Null option is selected, then only the nullable columns can be masked. All data from that nullable columns will be masked with the null value.
If the Value option is checked, the data from the selected columns will be replaced with the value that is typed in the Value text box. For this article, all data will be replaced with xxxxxxx value:
When all is set, click on the Apply button in the Create substitution mask window:
The Apply to columns window will be opened. From the Available grid, columns can be chosen that will be transferred to the Applied grid by using the right blue arrow in the middle.
In this case, the AddressLine2 column will be moved. By clicking on the OK button, the newly created mask will be applied to the selected column:
The focus will go back to the Create substitution mask window, whereby clicking on the OK button will create a new mask.
That newly created mask will now be present in the Manage mask window among all other masks. To close the window, just click the OK button:
The main window of ApexSQL mask will be shown. By clicking on the column that is masked with the Special value generator, in the Preview dialog on the right side of the main window, the final result can be seen as shown below:
