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.