Using SQL Server 2016’s New Dynamic Data Masking

Security is one of the key areas where Microsoft has made a lot of important enhancements to the new SQL Server 2016 release. Microsoft added Always Encrypted, Row-level security and Dynamic Data Masking to SQL Server 2016. All of these new security features are designed to limit access to sensitive data but the ways that they do that are quite different. Dynamic data masking essentially obscures sensitive data by masking it for non-privileged users. Dynamic Data Masking helps limit access to sensitive data with minimal impact on the application layer. It is implemented in the database layer and doesn’t impact the application layer. You might use Dynamic Data Masking to enable developers to work with production tables without exposing sensitive data or you might use it in help desk or call center scenarios where you want to restrict the data the help desk personnel can see.

It’s important to understand that Dynamic Data Masking is not encryption. The key difference is that Dynamic Data Masking obfuscates data as it is displayed to the end user. However, it does not change or encrypt the data that is stored on the disk. Dynamic Data masking is implemented when a table is built using the CREATE TABLE statement or you can add it after the fact by using the ALTER COLUMN statement. Implementing Dynamic Data Masking on a column does not prevent authorized users from performing updates to that column. Even though the end users may see masked data when they querying the masked column they can still update, insert and delete the data if they have write permissions.

SQL Server 2016’s Dynamic Data Masking provides several built-in functions that you can immediately use without the need to write any data masking functions on your own. You can see the built-in data masking functions in the table below. 

Granting the UNMASK permission allows a user to see the data unmasked. You can see examples of using the MASK and UNMASK permissions in the following listing.

-- Adding the UNMASK permission
GRANT UNMASK TO User1;
-- Removing the UNMASK permission
REVOKE UNMASK TO User1;

There are a few considerations you need to be aware of with Dynamic Data Masking. You can’t use it with Always Encrypted columns or the FILESTREAM data type. Although the data is not masked when it is stored in the database if the user executes the SELECT INTO or INSERT INTO to copy data from a masked column into another table the results in the target table will be masked. You can use the sys.masked_columns view to see the columns that have a masking function applied to them. This DMV returns all columns and the is_masked and masking_function columns indicate if a column is masked and the masking function that was used.

Dynamic Data Masking can be combined with Always Encrypted, Row-level security and Transparent Data Encryption (TDE) to help create a comprehensive and layered security strategy.

HPE and Microsoft are the underwriters of this article.