Featured Database Articles

How to Implement Dynamic Data Masking in Azure SQL Database and SQL Server

By Anoop Kumar

In a previous article, we discussed Dynamic Data Masking (DDM) and how DDM helps to secure PII/PHI data from unprivileged users. The DDM feature is available in SQL Server 2016 onwards as well as Azure SQL Database.

This feature is useful to implement data security for existing data sets and future data. One of the best parts of this feature is that it doesn’t require any change in the application and data doesn’t change at the physical level. The data masking can be achieved by using any available built in functions. We discussed all these functions in previous articles.

In this article, we will discuss how we can use these built in functions to mask the data based on different data patterns, like social security number, credit card number, date of birth, email address, name, etc.

Data Masking Functions

SQL Server provides four built in functions to mask data in SQL tables. These functions are as follows:

partial()

default()

email()

random()

We learned about these functions in a previous article. In this article, we will discuss how we can use these functions to achieve data masking.

Test Data Preparation

First, we will create test tables and insert test data in these tables. Later, we will apply data masking functions and see how the data looks like after data masking.

The command below masks the column EmailAddress in the TestDDM table using the email() function. The function masks the whole string of the email address except the first letter of the email, the special character of the address (@), and the constant suffix of the email address (.com).

Now, we will review the behavior of data masking implementation. We will run a Select query and review the data.

SELECT*FROM TestDDM;

Figure 3 TestDDM table Data - Unmasked

We can see that data is still visible as inserted. There is no change in data behavior and the data doesn’t mask. The reason for this behavior is user permission. In the current scenario, my ID has db_owner permission and has full access to the data.

To understand the behavior of mask functions and masked data, we will create a new database user TestMaskUser (without login) and will grant select permission on the TestDDM table to the newly created database user.

Now, we will change the context of the query execution and review the TestDDM data table.

EXECUTEASUSER='TestMaskUser';SELECT*FROM TestDDM;REVERT;

Figure 4 TestDDM table Data - Masked

We can see that after changing the user context, data in the TestDDM table has been masked and complete values are not visible to user TestMaskUser as expected.

SocialSecurityNumber is showing last 4 digits

EmailAddress is visible with first character, special character (@) and suffix (.com) only

CreditCardNumber has masked completely

Now, you can understand how easy it is to mask the data available in your database. In the next example, we will see the use of the random() function and how to mask the date of birth data.

random() - function is used on any numeric type to mask the original value with a random value within a specified range.

This SQL command below uses the random() function to mask the Income column in the TestPerson table. This function will replace the original value with a random number within a given range in the random() function.

Grant and Revoke UNMASK Permission

We know that data masking is a feature used to secure data that should not be visible to unprivileged users.However, there are methods to control the access of data. We can use the query below to grant permission to UNMASK the user. After this, the unmasked user can see the actual stored data in a table rather than masked data.

GRANT UNMASK TO TestMaskUser;

Figure 7 Grant UNMASK to DB User

Once we Grant UNMASK to TestMaskUser, the user can see actual data rather than masked data.

Similarly, if we Revoke UNMASK permission to TestMastUser, the user can see only masked data.

REVOKE UNMASK TO TestMaskUser;

Figure 8 Revoke UNMSK to DB User

Summary

Dynamic data masking is a great feature for both on-premise SQL Server and Azure SQL Database as well. This feature can help users to secure their critical data elements without making any change at physical level. All the unprivileged users can only see masked data and don’t have access to actual values. The DDM is one of the methods to de-identify data in SQL Server 2016 onwards and Azure SQL Database.