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)
)

p1

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')

p2

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  

p3

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.

p4

Step 5: Unmask the User

Grant UNMASK to DDM_Read

p5

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

p6

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

Revoke UNMASK to DDM_Read

p7

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)')

p8

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

p9

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)')

p10

p11

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()')

p12
p13

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.

p14

 

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About srikanth manda

I have got more than 9 years of experience in SQL Server and Microsoft Business Intelligence. Currently am working as Technical Architect in TCS (Tata Consultancy Services), Hyderabad. Very well versed in trouble shooting, Performance tuning, resolving the issues. Presently, exploring on the Bigdata and Hadoop, Spark and Scala.

View all posts by srikanth manda →

Leave a Reply

Your email address will not be published. Required fields are marked *