Dynamic Data Masking in SQL Server 2016

For any organisation, database is the most important component because that contains sensitive data which is only accessible to the user on need to know basis. To avoid the unauthorised users to see the sensitive data, SQL Server 2016 introduced a new security feature called Dynamic data masking (DDM).

What is Dynamic Data Masking?

Dynamic Data masking is nothing more than a new security feature to hide sensitive data in the result sets of queries over designated database fields, while the data in the database is not changed. DDM is used to create a new representation of data with random characters or data that are structurally similar to original data. It is normally done for the protection of our sensitive data. This functional term is also known as data obfuscation.

Benefits of Dynamic Data Masking

The best things about DDM is that we can easily incorporate this feature with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.

1. A central data masking policy acts directly on sensitive fields in the database.
2. Designate privileged users or roles that do have access to the sensitive data.
3. DDM features full masking and partial masking functions, as well as a random mask for numeric data.
4. Simple Transact-SQL commands define and manage masks.

The purpose of dynamic data masking is to limit exposure of sensitive data, preventing users who should not have access to the data from viewing it. Dynamic data masking does not aim to prevent database users from connecting directly to the database and running exhaustive queries that expose pieces of the sensitive data. 

Types of Dynamic Data Mask

Data is masked on the fly and underlying data in the database remains intact and a masking rule may be defined on a column in a table, in order to obfuscate the data in that column. Four types of masks are available.

Function	Description	Examples
Default Mask	Full masking according to the data types of the designated fields. For string data types, use XXXX or fewer Xs if the size of the field is less than 4 characters  For numeric data types, use a zero value  For date and time data types, use 01.01.2000 00:00:00.0000000  For binary data types, use a single byte of ASCII value 0.	Example column definition syntax: Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL. Example alter syntax: ALTER COLUMN Gender ADD MASKED WITH (FUNCTION = 'default()')
Email Mask	Masking method which exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. .aXXX@XXXX.com.	Example definition syntax: Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL. Example alter syntax: ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')
Random Mask	A random masking function for use on any numeric type to mask the original value with a random value within a specified range.	Example definition syntax: Account_Number bigint MASKED WITH (FUNCTION = 'random([start range], [end range])'). Example alter syntax: ALTER COLUMN [Month] ADD MASKED WITH (FUNCTION = 'random(1, 12)')
Custom String Mask	Masking method which exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix. Note: If the original value is too short to complete the entire mask, part of the prefix or suffix will not be exposed.	Example definition syntax:  FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(prefix,[padding],suffix)') NULL. Example alter syntax: ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')

Best Practices and Common Use Cases for Dynamic Data Masking

We do not need any special permission to create a table with a dynamic data mask, only the standard CREATE TABLE and ALTER on schema permissions. It should be great if we can use common cases for the DDM as given below:

1. Creating a mask on a column does not prevent updates to that column. So although users receive masked data when querying the masked column, the same users can update the data if they have write permissions.
2. A proper access control policy should still be used to limit update permissions.
3. Using SELECT INTO or INSERT INTO to copy data from a masked column into another table results in masked data in the target table.
4. Dynamic Data Masking is applied when running SQL Server Import and Export. A database containing masked columns will result in a backup file with masked data (assuming it is exported by a user without UNMASK privileges), and the imported database will contain statically masked data.

Limitations and Restrictions

Dynamic Data Masking is designed to simplify application development by limiting data exposure in a set of pre-defined queries used by the application and a masking rule cannot be defined for the following column types:

1. Encrypted columns (Always Encrypted)
2. FILESTREAM
3. COLUMN_SET or a sparse column that is part of a column set.
4. A mask cannot be configured on a computed column, but if the computed column depends on a column with a MASK, then the computed column will return masked data.
5. A column with data masking cannot be a key for a FULLTEXT index.
6. For users without the UNMASK permission, the deprecated READTEXT, UPDATETEXT, and WRITETEXT statements do not function properly on a column configured for Dynamic Data Masking.

To understand the functionality of DDM, please visit at Working with Dynamic Data Masking in SQL Server 2016

Conclusion
The purpose of dynamic data masking is to limit exposure of sensitive data, preventing users who should not have access to the data from viewing it. Dynamic data masking does not aim to prevent database users from connecting directly to the database and running exhaustive queries that expose pieces of the sensitive data. Dynamic data masking is complementary to other SQL Server security features (auditing, encryption, row level security etc.) and it is highly recommended to use this feature in conjunction with them in addition in order to better protect the sensitive data in the database.

References: Microsoft