Dynamic Data Masking

This article will give a brief overview of Dynamic Data Masking and how to set it up in TimeXtender with a detailed step-by-step guide.

What is Dynamic Data Masking?

Dynamic Data Masking masks sensitive data from unauthorized users. An example of this would be if a bank representative is assisting a customer; they would only be able to see a masked version of the account number with only the last 4 digits being visible. Below you will see an example showing a partial data mask:


Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to specify how much sensitive data to reveal with minimal impact on the application layer. DDM can be configured on designated database fields to hide sensitive data in the result sets of queries. With DDM, the data in the database isn't changed. DDM is easy to use with existing applications, since masking rules are applied in the query results.

Many applications can mask sensitive data without modifying existing queries. There will be 3 types of DDM that we will discuss and implement:

  • Default DDM - the typical default mask that masks the entire value in a given field. For example, if a field value is '1234', it will be masked to show 'xxxx'.
  • Email DDM - this version of DDM will mask email data to ensure there is proper security available for email fields if necessary.
  • Random DDM - this version of DDM will allow for developers to choose random values to be inputted which will allow unauthorized end-users to build transformations and test without seeing actual sensitive data. For example, if a field value is '1234', Random DDM will mask it as '2341' or a similar random value.

Implementing Default DDM


  1. The first step is to open your DSA or MDW and scroll down to 'Script Actions'. Next, you will want to select 'Add Custom Step'.


  2. The next step is to input a name for your Custom SQL Step. Additionally, you will want to input your default data masking script. Due to TimeXtender's naming conventions, you will just need to establish a mapping to your source table and column in the parameters area. This will allow for your Data Masking rule to automatically update if there are any changes to your table or column. The script is below:
    ALTER TABLE [InsertTableName]
    ALTER COLUMN [InsertColumnName] InsertDataType() MASKED WITH (FUNCTION = 'default()');

  3. The next step in our process is selecting the respective table in your MDW or DSA and scrolling to the 'Advanced' option in the menu. From there, you will select 'Set Pre- and Post-Scripts'.


  4. Set the Post Step for Deploy Table Structure to your respective Data Mask script.
  5. Deploy and Execute the table so that your Data Masking Script can be applied as a post step.


Implementing Email DDM

In this instance, you can follow all of the same steps as listed above for Default DDM. However, there will be a difference in the script as it is aligned to email data. Below, you will find the script that you can use for Email DDM:

ALTER TABLE [InsertTableName]
ALTER COLUMN [InsertColumnName] nvarchar(50) MASKED WITH (FUNCTION = 'email()');

Implementing Random DDM

As stated above for Email DDM, you can follow all of the same steps as listed above for Default DDM. However, there will be a difference in the script as it is aligned to randomizing numerical data. In my example script, we will be working with INT datatypes. Below, you will find the script that you can use for Random DDM:

ALTER TABLE [InsertTableName]
ALTER COLUMN [InsertColumnName] int MASKED WITH (FUNCTION = 'random(1,100)');

Testing DDM


To ensure your Data Masking rule is working properly, you can test it by creating a fake user in the query tool within TimeXtender. In this instance, a user titled 't3st' was created. SELECT permissions were then granted to this user. Lastly, the query was then executed as the user. We were then provided with our results which shows the Data Mask working properly for CardNumber.

Was this article helpful?
1 out of 1 found this helpful


Please sign in to leave a comment.