Dynamic Data Masking (DDM) in Sql Server 2016

Dynamic Data Masking (DDM) in Sql Server 2016

The Dynamic Data Masking (DDM) is introduced in Sql server 2016 that mask the data to secure to the non-privileged users. It provides the mechanism to secure the sensitive data from the users. It can be applied on table column to mask the data. It can't changes the actual data but it provide the masking to secure the data  for the application layers

Dynamic Data Masking has four types that is below 

1. Default
2. Custom String(Partial Data Masking)
3. Email
4. Random

1:- Default

Default dynamic data masking functions is based on the data type of the column on which it is applied.

• If the data type of the column is date and time, then it display the data in 1900-01-01 00:00:00.000 format.
• If the data type of the column is numeric then it displays 0.
• If data type of the column is string, then It display X for each character and max it displays 4 X’s.

Example:-

--- Script to Create a DDM_Employee table

Create table DDM_Employee

(

       Employee_ID int,

       Employee_DOB datetime masked with (function = 'default()'),

       Employee_Name varchar(100),

       Employee_Email_Id nvarchar(100),

       MobileNumber   varchar(10))

-- Here i am inserting the records in the DDM_Employee table

INSERT INTO DDM_Employee values (7891,'05/18/1999','Alok Singh','alok@test.com')

INSERT INTO DDM_Employee values (7892,'08/02/1960','Anil Singh','anil@test.com')

INSERT INTO DDM_Employee values (7893,'01/15/1982','Sachin Kumar','sachin@test.com')

INSERT INTO DDM_Employee values (7894,'09/22/1977','Pradeep','pradeep@test.com')

Now, I am fetching all the inserted records from the DDM_Employee table

Select * from DDM_Employee

OUTPUT:-

Employee_ID	Employee_DOB	Employee_Name	Employee_Email_Id	MobileNumber
7891	18-05-1999 00:00	Alok Singh	alok@test.com	9876543210
7892	02-08-1960 00:00	Anil Singh	anil@test.com	9876543211
7893	15-01-1982 00:00	Sachin Kumar	sachin@test.com	9876543212
7894	22-09-1977 00:00	Pradeep	pradeep@test.com	9876543213

Creating DDM_Readonly user & Grant select (read) permission on table DDM_Employee

CREATE USER DDM_Readonly WITHOUT LOGIN

--Grant select permission to the user: reader

GRANT SELECT ON DDM_Employee TO DDM_Readonly

Checking the data using user DDM_Readonly who is having select permission on DDM_Employee table

EXECUTE AS USER = 'DDM_Readonly'

SELECT * FROM [dbo].[DDM_Employee]

REVERT

OUTPUT:-

Employee_ID	Employee_DOB	Employee_Name	Employee_Email_Id	MobileNumber
7891	01-01-1900 00:00	Alok Singh	alok@test.com	9876543210
7892	01-01-1900 00:00	Anil Singh	anil@test.com	9876543211
7893	01-01-1900 00:00	Sachin Kumar	sachin@test.com	9876543212
7894	01-01-1900 00:00	Pradeep	pradeep@test.com	9876543213

Now, Add DEFAULT() masking function on the Employee_Name column

ALTER Table DDM_Employee

ALTER COLUMN Employee_Name ADD MASKED WITH (FUNCTION='DEFAULT()')

Now Selecting the recods with 'DDM_Readonly user

EXECUTE AS USER = 'DDM_Readonly'

SELECT * FROM [dbo].[DDM_Employee]

REVERT

Employee_ID	Employee_DOB	Employee_Name	Employee_Email_Id	MobileNumber
7891	01-01-1900 00:00	xxxx	alok@test.com	9876543210
7892	01-01-1900 00:00	xxxx	anil@test.com	9876543211
7893	01-01-1900 00:00	xxxx	sachin@test.com	9876543212
7894	01-01-1900 00:00	xxxx	pradeep@test.com	9876543213

Let us remove the UNMASK permission from the user reader by executing the following statement

--Remove Unmask permission from the user: reader

REVOKE UNMASK TO DDM_Readonly

2:- Custom String OR Partial Data Masking

Custom String OR Partial Dynamic Data Masking function provides the facility to add Prefix or Suffix in the column values. Following is the syntax to mask the data:

Syntax:  Partial(prefix, padding, suffix)

Prefix – Starting numbers of character to be displayed. The data type of Prefix is INT.

Suffix – Last number of characters to be displayed from specified column value. The data type of Prefix is INT

Padding –Custom padding string for masking. The data type of Prefix is NVARCHAR

Example:-

Now, I am altering MobileNumber column to apply the Partial Data masking

---Add DEFAULT() masking function on the Name column

ALTER Table DDM_Employee

ALTER COLUMN MobileNumber ADD MASKED WITH (FUNCTION='Partial(2,"XXXXX",3)')

Now, I am fetching all the inserted records from the DDM_Employee table

EXECUTE AS USER = 'DDM_Readonly'

SELECT * FROM [dbo].[DDM_Employee]

REVERT

OUTPUT:-

Employee_ID	Employee_DOB	Employee_Name	Employee_Email_Id	MobileNumber
7891	00:00.0	xxxx	alok@test.com	98XXXXX210
7892	00:00.0	xxxx	anil@test.com	98XXXXX211
7893	00:00.0	xxxx	sachin@test.com	98XXXXX212
7894	00:00.0	xxxx	pradeep@test.com	98XXXXX213

3:-Email Data Masking

Email dynamic data masking function displays the first character of an email address and rest is replaced by XXX@XXXX.com.

Now, I am altering Employee_Email_Id column to apply the Email Data masking

---Add Email() masking function on the Name column

ALTER Table DDM_Employee

ALTER COLUMN Employee_Email_Id ADD MASKED WITH (FUNCTION='Email()')

Now, I am fetching all the inserted records from the DDM_Employee table

EXECUTE AS USER = 'DDM_Readonly'

SELECT * FROM [dbo].[DDM_Employee]

REVERT

OUTPUT:-

Employee_ID	Employee_DOB	Employee_Name	Employee_Email_Id	MobileNumber
7891	00:00.0	xxxx	aXXX@XXXX.com	98XXXXX210
7892	00:00.0	xxxx	aXXX@XXXX.com	98XXXXX211
7893	00:00.0	xxxx	sXXX@XXXX.com	98XXXXX212
7894	00:00.0	xxxx	pXXX@XXXX.com	98XXXXX213

4:-Random Data Masking

Random function can be applied on a column of numeric type. It returns a random value between the specified ranges.

Now, I am altering Employee_Email_Id column to apply the Email Data masking

---Add Random () masking function on the Name column

ALTER Table DDM_Employee

ALTER COLUMN Employee_ID ADD MASKED WITH (FUNCTION='Random(10,20)')

Now, I am fetching all the inserted records from the DDM_Employee table

EXECUTE AS USER = 'DDM_Readonly'

SELECT * FROM [dbo].[DDM_Employee]

REVERT

OUTPUT:-

Employee_ID	Employee_DOB	Employee_Name	Employee_Email_Id	MobileNumber
17	00:00.0	xxxx	aXXX@XXXX.com	98XXXXX210
16	00:00.0	xxxx	aXXX@XXXX.com	98XXXXX211
14	00:00.0	xxxx	sXXX@XXXX.com	98XXXXX212
17	00:00.0	xxxx	pXXX@XXXX.com	98XXXXX213