BT

SQL Server 2016: Dynamic Data Masking

| by Jonathan Allen Follow 638 Followers on Jun 18, 2015. Estimated reading time: 1 minute |

Row-level security is great for all or nothing decisions, but there are times when users need access to a subset of the data. For example, the last four digits of a credit card or social security number. This can be done at the application level, but that leaves room for error. You only need to forget the mask one time to leak sensitive data.

SQL Server 2016 attempts to address this with a feature called Dynamic Data Masking. When a column is created with a mask, it defaults to returning only the data exposed through the mask. There are three types of masks currently available:

  • The Default mask returns 'XXXX', 0, or '01.01.2000 00:00:00.0000000' depending on the data type.
  • The Email mask returns 'aXX@XXXX.com' where “a” is the first letter in the email address and “com” is the top-level domain name.
  • The Partial mask return the first N characters, a constant expression such as 'XXX-XX-XX' and the last M characters.

There are two ways to unmask the data. The first is to have the UNMASK global permission, which turns off masking entirely for the user. The second way is to cast the masked column to the underlying data type. For example,

SELECT FirstName, CAST (Email AS nChar(50)) FROM Members;

Limitations

Because the mask can be cast away, this shouldn’t be thought of as a security feature. Rather, it is a convenience feature that can be used in conjunction with best practices such as encrypting sensitive information and not allowing users to execute ad hoc queries.

Masking may cause problems with ORMs. If the ORM doesn’t support field-by-field change tracking, it is possible to overwrite the actual value with the masked value during an update operation.

Rate this Article

Adoption Stage
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Implementation Of Dynamic data Masking by Johnson Welch

Thank you! Very interesting article. I have found another helpful post see here: www.sqlmvp.org/implement-dynamic-data-masking/

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

1 Discuss
BT