Dynamic Data Masking in SQL Server 2016

Microsoft has introduced an impressive new feature in SQL Server 2016 called Dynamic Data Masking (DDM). Dynamic Data Masking allows a developer or administrator to decide how much of the
sensitive data to reveal with minimal impact on the application layer. This feature also helps to simplify the design and coding of security in your application by making the data at the database level.

Dynamic Data Masking does not modify or change the actual data stored in a table; it applies the masking functions on the table’s column at the time of returning a data as the result of a query. Dynamic
Data Masking supports four data masking functions, as listed below, using which you can mask the data at the database level:

1. Default
2. Random
3. Custom String
4. Email

Note: There are two ways using which you can apply the DDM functions. You can apply this at the time you create the table or you can apply this function in the existing table that contains data using an
ALTER statement.

Default:

The default function of Dynamic Data Masking masks data on the basis of the column’s data type.
• If the data type is date and time, then it shows the data as 1900-01-01 00:00:00.000 formats.
• If the data type is numeric then it shows as 0.
• If data type is string, then it displays data by adding X’s in the string.

Step 1: Lets create employee table

create table Employee
(
Employee_ID int,
Employee_DOB datetime masked with (function = 'default()'),
Employee_Name varchar(100),
Employee_Email_Id nvarchar(100)
)

Step 2: Insert records into table

insert into Employee values (1234,'05/17/1989','srikanth manda','srikanth.manda@tcs.com')
insert into Employee values (2134,'03/01/1990','ashrita manda','ashrita.manda@tcs.com')
insert into Employee values (1324,'06/21/1992','srilatha manda','srilatha.manda@tcs.com')
insert into Employee values (1254,'10/29/1987','pooja manda','pooja.manda@tcs.com')

Step 3: create a user to grant permission on Employee using below script.

CREATE USER DDM_Read WITHOUT LOGIN

GRANT SELECT ON dbo.[Employee] TO DDM_Read;  
GO  

Step 4: Execute the query

EXECUTE AS USER = 'DDM_Read'
SELECT * FROM [Employee]
REVERT

The below output we can see that user DDM_Read is not able to see the actual data for the Employee_DOB column because we have applied the Default Dynamic Data Masking function on this column. Hence, data of column Employee_DOB showing in the 1900-01-01 00.00.00.000 format.

Step 5: Unmask the User

Grant UNMASK to DDM_Read

After granting UNMASK permission to the user, DDM_Read, they will be able to see the actual data, like shown in the below figure.

EXECUTE AS USER = 'DDM_Read'
SELECT * FROM [Employee]
REVERT

Use the below script to revoke the UNMASK permission of user, DDM_Read.

Revoke UNMASK to DDM_Read

Random

 This DDM function is applied on numeric data types only. It displays a random value for the specified range. In the below example we will apply the Random function on the Employee_ID column.

-- apply Random DDM function on Employee_ID column
Alter Table[dbo].[Employee] Alter Column Employee_ID Add masked with (function='Random(1,4)')

EXECUTE AS USER = 'DDM_Read'
SELECT * FROM [Employee]
REVERT

Custom String

This DDM function uses the below syntax to mask the data:

Syntax : Partial(prefix,[padding],suffix)

• Prefix – Starting numbers of character to be displayed.
• Suffix – Last number of characters to be displayed from specified column value
• Padding –Custom padding string for masking.

We will apply the Custom String DDM function on Employee_Name column with the below values :

• Prefix = 3 — It will displayed first three characters of Employee_Name column values.
• Suffix= 9 — It will display last 9 characters of Employee_Name column values.
• Padding = $$@@$ — It will start masking from 4th character and display this Padding string.

--apply DDM Partial function on Employee_Name column
Alter Table[dbo].[Employee] Alter Column Employee_Name Add masked with (function='Partial(3,"$$@@$",9)')

The Email Function

 This DDM function will displays the first character of an email address, masking the rest of the characters with XXX@XXXX until the suffix “.com”. For example, if we apply the email DDM function for an email address like abc@ddm.com, then this email address will appear as “aXXX@XXXX.com”.

Using the below script, we will apply the email DDM function on the Employee_Email_Id column of the table, Employee, and check how the data will appear to the user, DDM_Read user.

-- apply Email DDM function on Employee_Email_Id column
Alter Table[dbo].[Employee] Alter Column Employee_Email_Id Add masked with (function='Email()')

We can use below script to remove all the Dynamic Data masking functions on the table :

Alter Table[dbo].[Employee] Alter Column Employee_Email_Id Drop Masked
Alter Table[dbo].[Employee] Alter Column Employee_Name Drop Masked
Alter Table[dbo].[Employee] Alter Column Employee_DOB Drop Masked
Alter Table[dbo].[Employee] Alter Column Employee_ID Drop Masked

After removal of all Dynamic Data Masking function a sensitive data will be visible to the user DDM_Read as shown in below figure.

Stay tuned lot more to come from Microsoft SQL Server 2016 including Business Intelligence.