Data Bits : A few thoughts on Microsoft SQL Server's Dynamic Data Masking

Overview

Data security has many forms, functions and levels. One facet of that is utilizing data masking to obscure sensitive data but not blocking complete access to the data element. This allows the access methods (e.g. SQL) to be identical for different users but the resulting data can be adjusted based on regulatory requirements.

Microsoft SQL Server's Dynamic Data Masking (DDM) provides this functionality natively since SQL Server 2016.

Create an automated process to validate current masking

Create an automated SQL Server Agent job to leverage the system view sys.masked_columns to ensure that all the DDMs expected have been created and applied to the appropriate columns. Ideally this would leverage a metadata repository to check the results from the Account Usage views against the external requirements. Note that the system view is at the database level so for instances containing multiple databases the ability to traverse all desired databases will be necessary.

Consider custom versus provided masking functions

SQL Server provided several out-of-the-box masking functions such as Email, Datetime and even Random. While these certainly fulfill the role of obfuscating data consider using a custom function to tailor the results to your specific needs. For example, while the provided Email function will work it has will always show the suffix email address as ".com", regardless of the actual email address.

Potential security limitations

The ability to view the unmasked data can not be revoked from certain users, including those with dbo permissions. This means that the object owners will be able to view all data.

Additionally, depending on the type of data (e.g. salary), a determined user with the authority to execute custom queries could "work out" masked data values. Thus would likely require multiple iterations of different search criteria but it is possible.