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
- Default
- Custom String(Partial Data Masking)
- 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
|
No comments:
Post a Comment
Note: only a member of this blog may post a comment.