Mask SQL Server data with substitution type on the row level

Applies to

ApexSQL Mask

Summary

This article explains how to mask SQL server data with the substitution type of mask on the row level.

Description

ApexSQL Mask is a tool that masks sensitive SQL Server data. This database masking tool offers two types of rules for mask SQL Server data:

• Substitution, where is used a certain number of generators to change entirely sensitive data with the masked one
• Redaction, where are used values or part of values for masking sensitive data

The Row level of masking allows users to mask a particular row or selection of rows in the targeted column, further in the article will be explained this option.

By starting the application, the Connect to SQL Server window will appear. In the Connection to SQL Server window the SQL Server, type of Authentication, and a Database is needed to be set. When all is set, click on the Connect button:

The main window of ApexSQL Mask will appear, where the automatically classified columns will be presented by default in the grid panel on the left side of the primary window:

Besides automatically classified columns, the ApexSQL Mask provides the possibility to mask SQL Server data from the whole selected database. To choose any column from the selected database, in the Home tab, click on the All columns button:

For this article the LastName column from the Person. Person table will be chosen. By checking the LastName column, the red dot will appear in the front the name of the column, and on that selected column can be applied already created or newly created mask:

When the column is selected, to create a new mask in the Home tab, click on the Manage button:

The Manage masks window will open. By default, the Substitution mask is selected. Click on the New button to start the creation of a new substitution mask:

The Create substitution mask window will be shown. By default, the Column radio button is checked.

Mask SQL Server data in the Row-level

If there is need to mask special row or to exclude some rows from masks, those can be done by checking the Row radio button in the Level section:

The Change target column dialog will appear, with all classified columns. Choose the columns from the Available grid, in this case, the LastName column and click on the OK button:

It will back to the Create substitution window, where for the Row level masking the following should be set:

• Name – in this text box, enter a name for a new substitution mask (e.g. Row mask)

• Description – this text box is optional, and it will contain the description of the newly created mask
• Source column – the drop-down list that includes the targeted column, also and all columns from the same table. The value of the specific row(s) from the source column will be used to find the related row(s) in the targeted column, which data will be masked. For this article, the value in the row of the BusinesEntityID column, that is specified as a source column, will be used to find the related row (s) in the targeted column, the LastName column

• Preview grid – shows the row’s data from the selected source column

• Find values – shows the pattern that will be created with Wildcard editor. That pattern will be used for finding the specific row or rows in the source column. The Wildcard editor dialog will be opened by click on the ellipse button next to the Combo box:

  

  • Wildcard editor – dialog in which can be added pattern for searching row in the source column. By using specific characters that can be found in the Syntax drop-down list, will be made a pattern for searching. A list of characters represents any single character syntax with seven number pattern [7] will be used for searching a row in this article. Click OK:

    

• Exclude – by selecting this option the matching values will be excluded from substitution
• Include nulls – when this option is checked null values will be replaced with a substitution
• Type – the type of generators that will be used for substitution:
  • Predefined
  • Use original
  • Random
  • Specific value
  • Regular expression
  • SQL script
  • External source

For this article will be used the Specific value generator. The Value option will be checked and that value will be REPLACE:

When all options above are set, click the OK button:

The Manage masks window with the newly created mask will appear. Click the OK button to close the Manage mask window :

In the grid panel will be presented classified columns along with the LastName column in the Person. Person table. When clicking on the LastName column, in the Preview window the seventh row will be masked with the REPLACE value while other values will not be masked.