Dynamic Data Masking in SQL Server

 Keeping Sensitive data in a database is always a pain. It's never fun to explain to your Security Team why you need to keep the sensitive data in your database. SQL Server 2016 came with a great feature to mask sensitive data in your tables. Dynamic Data Masking limits data exposure by masking the data to non-privileged users. You can control how much of the sensitive data to reveal when somebody queries the tables. This means web developers don't need to mask sensitive data in the web applications. SQL Server can do that for me before data hits to my IIS server.

   By using DDM (Dynamic Data Masking), we can hide part of a credit card number, Social Security Number or any other sensitive data. We need to define masking rules and apply these rules to the table columns. Before I continue to an example, I want to warn you.  DDM is not an encryption method, it only masks a part of the data. There are 4 type of masking rules you can create in SQL Server 2016

Default (Full Masking)

For String values, X character masks the real data.

For Numeric values number 0 masks the real data.

For date type values 01.01.1900 masks the real data

For binary values, 0 masks the real data.

Following example shows you how to create a column with a default mask, and how to add a mask to a column.

Now, If we query this table as it is right now, since I am the owner of this table, I will be able to see the masked data.

I need to create a new user or alter a user's record to demo masking functionality.

 I am going to create a test user and run the same query as that user in the following example.

Now My user is ready, I am going to run the same query as this user.

Now, this user can not see the sensitive data, Here is the rest of the Dynamic Data Masking options.

Email : It exposes the first letter of an email and the constant suffix of an email. (hXXXXXX@XXXX.com)

To demo this, I am going to create a new column in our table.

Random: This option is for numerical data masking. It masks the original value with a random value within a specified range.

To demo this option, I am going to create a new column with money data type.

Rather than $40.000, user sees a random number between 1 and 10

Custom String: This option exposes the first and last letters and adds a custom padding string in the middle.

   Dynamic Data Masking is a great addition to SQL Server. I want to point out one more time that data masking is not encryption, and it might be bypassed by using brute-force technique. For example, Since users can run queries against the masking field, they can run multiple queries to guess the masked value. Specially for numerical values.