With SQL Server 2016 just around the corner, a series of new features and enhancements will be introduced. More specifically some important Security Upgrades; Always Encrypted, Row Level Security, and Dynamic Data Masking. This post covers SQL Server 2016 – Dynamic Data Masking (DDM).

Note: DDM is also available in Azure SQL Database see the following link for details -> http://bit.ly/1eG7Que

Dynamic Data MaskingWhat is Dynamic Data Masking? Dynamic Data Masking enables you to anonymize sensitive data, real-time obfuscation of data to prevent unauthorized access by controlling how the data appears in the output of database queries. It is implemented within the database itself, the logic is centralized and always applies when the sensitive data is queried. Traditionally, obfuscation of sensitive data as been coded in the application layer, using views to limits the exposure of sensitive pieces of data, and third-party tools… Bottom line, it is now quite simple to configure DDM rules on sensitive columns, which can be done on an existing database without affecting database operations or requiring changes in application code.

In SQL Server 2016 there are four types of masks rules: (details)

  • Default: Full masking – replaces characters with XXXX, numbers with 0 and date/time data types with 01.01.1900 00:00:00.0000000
  • Email: Masking method which exposes the first letter of an email address and the constant suffix “.com”, in the form of an email address. aXXX@XXXX.com.
  • Custom String: Partial Masking – method which exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix
  • Random: A random masking function for use on any numeric type to mask the original value with a random value within a specified range.

Creating a Dynamic Data Mask
This example creates a table with three different types of dynamic data masks, inserts data and queries the table.

query output:
q1

Let’s create a new user and grant SELECT permission on the table. Queries executed as the testuser view masked data.

As you can see the testuser database user is unable to see the real data values for each of the columns that have been masked
query output:
q2

Now let’s grant UNMASK permission to a specific user to see unmasked data on the table.

The output shows all columns of data in clear text, even the ones that have been masked by rule definition
query output:
q1

Summary
DDM is not a replacement for security access control mechanisms, nor is it a method for encrypting physical data. Dynamic Data Masking is about simplifying the obfuscation of sensitive data by streamlining the logic in the database itself (no external dependencies). Dynamic Data Masking is complementary to other SQL Server security features (auditing, encryption, Row-Level Security, etc.) and it is highly recommended to use it in conjunction with them to better protect your sensitive data in your databases.

  • Jeremy Kreyling

    This is a great way to demo a solution to an audience using real data and removing identifying information. I could see this very useful for a solution utilizing healthcare data or education information (HIPAA and FERPA come to mind).

  • Johnson Welch

    This was a fabulous article, full of helpful information. There I found one ,more very informative article explaining how to implement Dynamic data masking In SQl Server. You may also have a look: http://www.sqlmvp.org/dynamic-data-masking/