Applies to
Summary
This article explains how to mask data in SQL database using the substitution rule type with the SQL script.
Description
ApexSQL Mask allows users to mask data in SQL database. This database masking tool uses two types of rules to mask sensitive data:
- Substitution – the type of mask that uses specific generators to hide sensitive data
- Redaction – the mask type that uses the specific value and replaces it with a characteristic value
The SQL script generator functionality is a part of substitution type masking and allows users to mask the actual data in columns with the generated result of executed SQL script.
In other words, to use this functionality, the substitution mask type and the SQL script generator will have to be configured, which will be explained further in this article.
Substitution mask type
When ApexSQL Mask is opened, the Connect to SQL Server window will appear. Here the SQL Server connection parameters and the target database, which has sensitive data for masking, should be entered. When all is set, by clicking on the Connect button, this database masking tool will be connected to the selected database:
That will open the main grid within which all classified columns with automatically assigned masks will be shown:
Beside these automatically assigned masks there is the possibility to define some custom masks if needed. To do that go to the Home tab and click on the Manage button:
The Manage masks window will be opened. All automatically assigned masks will be present here, and those masks can be edited or deleted and also new masks created. To create a new substitution mask in the Manage masks window click on the New button:
This opens the Create substitution mask window. The first setting that should be defined is whether a masking generator will be applied on the Column or the Row level. When the Column is checked, ApexSQL Mask will be masking all data in the chosen columns. Alternately, if the Row level is selected, extra precision will be possible, i.e. only fields with a specified value in a chosen column will be masked.
Second, from the Type drop-down list, the type of generator for substitution masks can be selected:
- Predefined – uses a dictionary of different terms categorized by their field of usage (business, education, production, etc.)
- Use original – uses original data and shuffles it in the chosen column
- Random – creates random masking value based on a column name and data type
- Specific value – uses the specific value to mask data in SQL database
- Regular expression – creates masking value base on regular expression
- SQL Script – masking data with executable SQL script
For this article, the SQL script will be chosen from the Type drop-down list to mask data in SQL database:
SQL script substitution mask
When the SQL script type of mask is selected, in the Create substitution mask window, two radio buttons will appear:
- Use this script ( selected by default)
- Use embedded script
If the Use this script is selected, by clicking on the Folder browser button, previously saved SQL Script can be searched for and opened. And that SQL script will be used to generate values for masking sensitive data:
Alternately, when the Use embedded script is selected, in the text box below a SQL script can be typed in, which will be used to generate values for masking sensitive data:
|
Quick tip: Statements in the SQL scripts must be those that return some results to make masking data possible, in both previously mentioned cases (like the SELECT statement, for example). |
When the SQL script is chosen, the next step to mask data in SQL database is clicking on the Apply button in the Create substitution mask window:
Apply to columns dialog will be opened, where the list of sensitive columns in the loaded database will be shown, from which a selection of desired columns for masking should be created using the arrows from the Available grid to the Applied grid. When the columns are selected click on the Ok button:
The view will return to the Create substitution mask window, where this newly created mask’s name in the Name field will be required. The Description field can be optionally used to easily recognize this mask’s settings in future usage:
The newly created substitution mask will appear in the Manage mask window.
Confirming these settings with the OK button, the focus will return to the main window where the scope of chosen columns in previous steps and their assigned mask generator will appear in the main grid; and the outcome of the assigned mask generator can be seen in the preview pane:
