Sunday , April 2 2023

Dynamic Data Masking(DDM) in SQL Server

What is Dynamic Data Masking?

Dynamic Data Masking is a feature announced in SQL Server 2016. With Dynamic Data Masking, you can restrict unauthorized access to your data.

Microsoft introduced serious innovations about the data security with SQL Server 2016. Two of these features are Always Encrypted and Row-Level Security. You can find detailed information in the following articles.

What is Always Encrypted in SQL Server“,
Row Level Security in SQL Server

What is the difference between masking and encryption?

With DDM, we do not encrypt the data in the database. We ensure that critical columns are not visible to people even if they have select permission on the table. For users who need to read critical columns, extra privileges are required except select permission. I will share the scripts that grant extra permission to read critical columns with DDM.

Before this feature was announced, we were able to do column level encryption. You can find the details in the article “Column Level Encryption On SQL Server“. You can understand the differences by reading both articles.

Let’s continue by making an example. Suppose we have a customer table. In this table there is critical information such as customer’s identitiy number, email address, mobile phone and birthdate. You may not want the customer representative to see this information. In such a case, it is useful to use DDM.

Dynamic Data Masking Functions

With DDM, we can encrypt data with 4 different function.

1) Default

This masking method allows masking according to the data type of the column to be masked.

If column is a data type such as binary, varbinary, image, masking is done with a single byte value of 0.

For date type columns, the value 01.01.1900 00: 00: 00.0000000 is used.

For string type columns, the value XXXX is used.

For Numeric type columns, an empty value is used.

2) Random

Replaces data of the Numeric type with a random number value within the range specified by the random function.

3) Email

It takes only the first letter of the email and changes the rest to “[email protected]“.

4) Custom

It allows us to encrypt data with our own encryption function.

SQL Server Data Masking Example

Create a table with the following script and insert sample records into the table. phone numbers was inserted randomly. You should not call the phone numbers. 🙂

When we select from our table, the result will be as follows.

Masking by using Default Function

Mask the identity number column with the default method using the following script.

When we select our table again, we will see all the data unencrypted. Because, this operation is invalid for sysadmins and db_owners.

Create a login and grant db_datareader permission. Then, try to select from this table with this login. You may want to read the article “How To Create a Login On SQL Server(Manage Logins)“.

Instead of granting db_datereader permission, you can also GRANT Select permission as follows.

As you can see below, the login we grant Select permission can read the data as masked.

Masking by using Email Function

Mask it as follows with the email method and select again from the table with DDMUser.

As you can see, it took only the first letter of the Email column and masked the rest as “[email protected]”.

Masking by using Random Function

Mask the NumberofChildren column with the random method using the following script. It replaces the specified column values with a random value between 10 and 30.

As you can see below, although Faruk is not married, Faruk seems to have 29 children. 🙂

Masking by using Custom Function

Mask the Name column with the Custom method using the following script. In this method, it first lists 2 letters of the relevant column, then XXXX, and finally the last 2 letters.

Grant Unmask Permission To See Unmasked Data

We can Grant Unmask Permission to logins to see the original version of the masked data with the following script.(This script can be executed only sysadmins or db_owners)

Revoke Unmask Permission

Dropping Masking

We can drop the dynamic data masking on the table with the following script.

We need ALTER ANY MASK permission to perform the masking operations. Right-click the database, click properties, you can then grant permissions for specific users from the permission tab.

SQL Server Data Masking Limitations

You can not mask;

Encrypted columns,

Filestream columns,

Column_set or a sparse column if its a part of a column_set,

Computed Columns(You can mask columns those creates computed columns. Thus computed column can be masked)

You can not specify masked columns as a key for FULLTEXT index.


Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *