Considerations When Using SQL Server 2016 Dynamic Data Masking

SQL Server 2016 introduced a new security feature called Dynamic Data Masking.  With the General Data Protection Regulations, (GDPR) breathing heavy down most IT in America’s neck, its good timing.

“Data Masking” is a term that has a strict definition, but different results when we actually discuss the technology behind it.  The main goal is to obfuscate critical data in a non-reversible process and I deem it separate from encryption technology. With SQL Server’s Dynamic Data Masking, the original data is still present in the database, with a masking rule applied to hide the data, allowing anyone with the unmask privilege to still view it.

grant unmask to <user/role>;

As Delphix has a very robust and non-reversible, masking product, I realize that you get what you paid for.  The real question is the cost of data vulnerability in an age of security breaches, (over 1000 reported so far in 2017) worth the loss of customer loyalty, revenue and possibly the company.

So how does Dynamic Data Masking work?  It’s a simple alter table command.  If you have a table called Employees that contains their social security numbers and you want to mask them, you can add a masking function to the SSNum column:

alter table store.Employees
alter column [SSNum] add masked with (function = 'partial(0,"XXX-XX-",4)');

select SSNum from store.Employees;

XXXX-XX-5426
XXXX-XX-8954
XXXX-XX-0143
XXXX-XX-5499
...

Anyone with the unmask privilege or DB_OWNER will be able to view the data.  As many development and testing environments grant higher privileges to the users and in SQL Server, it’s not rare for a developer to be the DB_OWNER, (I used to come across this all the time when recoveries were performed by the wrong OS user) this leaves this data still quite vulnerable.  I do like that if you were to take a backup and recover it with masking, the obfuscated data is what is recovered physically.  I’m more concerned about those odd environments where compliance hasn’t been put in place on owners of the database that would still view the originally masked data, but unmasked.

Performance isn’t impacted, (i.e. no referential integrity concerns or execution plans) as the optimizer  performs all steps against the real data, which leads me to wonder what happens with some of the newer monitoring tools that state they can display SQL and bind variable data without accessing the database directly.  Would they “sniff” the masked data or unmasked?  Would it matter who the OS User or roles in the database?

The masking can also be reversed on an object with just a simple command:

alter table store.Employees
alter column [SSNum] drop masked;

This seems too easy to get access to critical data.  Understand that having critical data secured is essential, but before choosing a product, make a list of what could happen, the impact to the organization if the data is breached and the value of having a masking or encryption product that is robust enough to handle your requirements.

I’m heading home from Devoxx Morocco tomorrow and will be heading to DOAG in Nuremberg, Germany the beginning of next week.  It appears that jet lag will be a semi-permanent state for me this month…. 🙂