SQL Server 2016 - Dynamic Data Masking

March 6, 2016 3 minutes read

anonymize • data masking • dynamic data masking • obfuscation • security • sql server • sql server 2016

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

What 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.

use Sandbox;

create table dbo.Customer
(
	CustomerID	int identity primary key, 
	FirstName	nvarchar(100) masked with (function = 'partial(1, "XXXXXXX", 0)') not null,
	LastName	nvarchar(100) not null, 
	CreditCard	nvarchar(19) masked with (function = 'partial(0, "XXXX-XXXX-XXXX-", 4)') not null,
	Mobile		nvarchar(12) masked with (function = 'default()') null,
	Email		nvarchar(100) masked with (function = 'email()') null,
	DateOfBirth	date masked with (function = 'default()') null
);

insert into dbo.Customer (FirstName, LastName, CreditCard, Mobile, Email, DateOfBirth)
	values 
	('John', 'Maskeroo', '0123-4567-8901-2345', '222.343.4343', 'jmaskeroo@zcustomer.com', '03/07/1965'),
	('Tomas', 'Lambdafoo', '4444-4444-4444-4444', '666.222.1111', 'tlambdafoo@zcustomer.com', '10/02/1971'),
	('Martha', 'Backatyoo', '9999-0000-9999-0000', '888.555.9999', 'mbackatyoo@zcustomer.com', '05/05/1968');

select * from dbo.Customer;

query output:

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

create user testuser without login;
grant select on dbo.Customer to testuser;

execute as user = 'testuser';
select * from dbo.Customer;
revert;

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:

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

create user testadmin without login;
grant select on dbo.Customer to testadmin;
grant unmask to testadmin;

execute as user = 'testadmin';
select * from dbo.Customer;
revert;

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

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.