Dynamic Data Masking


Of the many new features in Microsoft SQL Server 2016, few have been more anticipated than Dynamic Data Masking. Dynamic Data Masking is a security feature designed to prevent sensitive data or personal information from being seen by non-privileged users.

Monitoring and managing user privileges has become an essential part of any organizations’ data security. Internal attacks have become of increasing concern because they cause more damage in total than outside threats. The attackers in these instances already have access or have stolen credentials to gain user privileges. Estimates are that these types of attacks cost United States businesses $400 billion annually, with $348 billion of that attributable to privileged users, according to a 2010 SANS Institute report. Many of these types of attacks are now occurring when security credentials are stolen, according to a report on InformationWeek‘s DARK Reading security blog.

The SANS Institute defines a privileged user as a “user who, by virtue of function, and/or seniority, has been allocated powers within the computer system, which are significantly greater than those available to the majority of users.”

As Microsoft explains:

[Dynamic data masking is] a security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.

What types of data would be masked? It could be a social security number, an e-mail address, credit card information, or even a telephone number.

If, for example, the mask was applied to a social security number, it might display only the last four digits of the number to a non-privileged user. Instead of 987-65-4321, only XXX-XX-4321 would be shown to a non-privileged user.

This feature is especially useful for meeting regulatory dictates or for data scrubbing to ensure data integrity. The latter instance is useful for those developers who need to work with data in realistic testing and development scenarios, notes TechTarget's SearchSQLServer. Static data masking, by contrast, causes data integrity problems.

Setting up dynamic data masking—as accomplished with the SQL Server 2016 CTP 2.2 release—is simple. There are three types of masks available:

  • a default for full masking of a designated field;
  • e-mail address masking;
  • a custom-string mask, which can be applied to phone or social security numbers

Any user can create tables using dynamic data masking; however, to add, replace, remove or change an existing mask requires a permission called “Alter Any Mask” and “Alter” at the table level. Microsoft’s Developer Network advises “It is appropriate to grant ALTER ANY MASK to a security officer.…The CONTROL permission on the database includes both the ALTER ANY MASK and UNMASK permission.”

Of course, how this feature will be applied depends on your organization’s existing security protocols as well as regulatory dictates of HIPPA and other laws.

Dynamic data masking is a piece of a comprehensive security strategy. It does not, for example, replace encryption. Aaron Bertrand, writing on the website mssqltips, concludes:

Dynamic data masking provides a simple way to implement obfuscation of private data (and in my tests so far, the performance impact is negligible). It is important to understand the limitations, and keep in mind that it is not true encryption and that the data cannot be protected in all scenarios.


Dynamic Data Masking or Real Time Masking

This refers to a scenario meant to protect a production database by returning obscured data on queries performed by unauthorized users, as defined by the masking policy. The actual data in the database is not changed. This means that ‘privileged’ users still get results containing all the data, while other users only get access to data to which they are entitled – again, based on the defined policies. Naturally, this implies that the determination of what data to return is done on the fly, in real time.

Data Masking in Action

We will do the following:

  1. Create a demo database for use
  2. Create a table with a number of columns
  3. Create the masking with different options
  4. Create a login/user which will have low privileges
  5. Check the effect of data masking

Now that the basic building blocks are done, we will next create the user for our experimentation and see the effect of data masking on them.

USE DataMaskingDB
GO

CREATE USER StandardUser WITHOUT LOGIN;
GRANT SELECT ON Employees TO StandardUser;
GO

USE DataMaskingDB
GO

SELECT * FROM Employees; -- this would show clear data
GO

EXECUTE AS USER = 'StandardUser';
SELECT * FROM Employees; -- this should show masked data
REVERT;
GO

The output for above will look like this:

As you can see, SQL Server 2016 introduces a powerful feature where privacy can be enhanced and leakage of sensitive data can be reduced. There are many compelling reasons for developers to use this capability inside their applications. It is worth a try!

Making the Move to SQL Server 2016

Here’s your chance to learn why this version has been called the best of the SQL Server family. Download Making the Move to SQL Server 2016, a new white paper from Datavail.


Download Now