SQL Server 2016 Features – Dynamic Data Masking

Data privacy is a major concern for lots of organisations given services are out sources, moving data to the cloud and increase in volume of data. Examples of these include sensitive details like credit card numbers, Date of Birth, Financial and HR Data. Traditionally obfuscation happens at the application layer and still cannot avoid exposure of sensitive data.

SQL Server 2016 and Azure SQL DB now offer a built-in feature to obsfuscate sensitive data using Dynamic Data Masking. (DDM)

Define a Data mask at the column level when you CREATE or ALTER a table. Use one of the following masks to do either full or partial masks.

Full mask of column contents

ALTER TABLE [TableName] ALTER COLUMN [ColumnName] ADD MASKED WITH (FUNCTION = ‘default()’)

Partial mask of column contents

ALTER TABLE [TableName] ALTER COLUMN [ColumnName] ADD MASKED WITH (FUNCTION = ‘partial(2, "AA@AAA",3)’)

Mask type	Syntax	Alter syntax	Description
Default	PhoneNo varchar(15) MASKED WITH (FUNCTION = ‘default()’)	ALTER COLUMN PhoneNo MASKED WITH (FUNCTION = ‘default()’)	The complete value is masked with a character appropriate to the data type; for example, characters are shown as xxx, and numeric with 0.
Email	Email varchar(60) MASKED WITH (FUNCTION = ‘email()’) NULL	ALTER COLUMN Email WITH (FUNCTION = ‘email()’)	Exposes the first letter and replaces the .suffix with .com; for example, cxxxxx@xxxxx.com.
Custom String	Name nvarchar(50) MASKED WITH (FUNCTION = partial(2,”xxxx”, 1)’)	ALTER COLUMN Name ADD MASKED WITH (FUNCTION = ‘partial(5, “xxxx”, 0)’)	Partially masks the value, exposing a number of characters at the start and end with custom padding. Syntax is (FUNCTION = 'partial(prefix, [padding], suffix)').
Random	Ac_Number int MASKED WITH (FUNCTION = ‘random(0, 999999)’)	ALTER COLUMN [Year] ADD MASKED WITH (FUNCTION = ‘random(1, 9999)’)	For numeric data types. Replaces the actual value with a random value in a given range.

Technorati Tags: SQL 2016,Data Masking,Obfuscation