One of the new features SQL Server 2016 introduced is Dynamic Data Masking. This feature allows sensitive data to be masked at the database layer with no changes to the application.
Run the following script in a new database to setup a sample table.
If we then run a select statement against the new table we get this…
Let’s imagine we only want our standard users to be able to see the Work Phone/Email but not the personal ones or the salary. Let’s setup a new login for StandardUser with select permission on our table.
Now let’s create another user for Accounting that should have access to see all the data unmasked.
Notice how we gave this user the UNMASK permission, allowing them to see unmasked data on tables with masked fields.
Let’s start by masking the PersonalEmail field
If we then execute our previous select statement running as our StandardUser account we’ll see that we now get a masked version of an email address…
If we run that same query as the accounting user we can see they do in fact get the unmasked data…
In the example above we masked the data using the email function, there are 4 different masking functions available depending on the type and shape of mask you want to use…
- Default will apply a mask depending on the type of the data. String types will return X’s, numeric types will return 0’s and Dates will return 1900-01-01.
- Email when used with a valid email address if will mask it by keeping the first letter, the @ and the domain suffix with X’s padded between.
- Random wen used on a numeric type will return a random number inside the range you specify.
- Custom String will expose the first x letters followed by a custom string, followed by the last x letters.
Let’s finish masking our table using the methods above.
For salary we want a random number between 10,000 and 100,000 which can be done with the random function.
For this we can use the Custom String function to return the 3 letters of the country code then 6 X’s then the last 3 characters.
As a StandardUser if we query the Employee table with our new masks we now get this…
Dropping A Dynamic Data Mask
If you want to remove a mask then you can use the alter column syntax with DROP MASKED.
Things To Note
- Data masking does not prevent updates so although the StandardUser above can’t see the unmasked data it can reset it.
- Always Encrypted columns can’t be masked.
- Data masking does not protect against users range checking the data to attempt to work out what it is. For example in the email sample above if the StandardUser executed an if statement checking if the email is equal to ‘[email protected]’ it would return true. Also the salary field would still allow things like where statements that return results in a given range to work out what the underlying value is.
- Computed columns can’t be masked.