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

p1

Step 2: Insert records into table

p2

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

p3

Step 4: Execute the query

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

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.

p6

Use the below script to revoke the UNMASK permission of user, 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.

p8

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.

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.

p12
p13

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

 

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.