Advertisement

Latest Post

Thursday, 27 July 2017

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




Government Jobs