Tuesday, August 16, 2016

How does Dynamic Data Masking work in SQL Server 2016

As we stated that dynamic data masking is one of many new security features coming in SQL Server 2016 to protect your sensitive data. Dynamic Data masking is nothing more than a new security feature to hide sensitive data in the result sets of queries over designated database fields, while the data in the database is not changed. 
DDM is used to create a new representation of data with random characters or data that are structurally similar to original data. It is normally done for the protection of our sensitive data. This functional term is also known as data obfuscation.

To understand the functionality of dynamic data masking, we can create a membership data table having some dynamic data masking definitions as given below:
Use Demo
Go
----- create table with data masking defination
CREATE TABLE MemberShip 
  (
   MemberID int IDENTITY PRIMARY KEY, 
   ---- Data Masking Type - Default
   FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL, 
   LastName varchar(100) NOT NULL, 
    ---- Data Masking Type - Custom String
   PhoneNo varchar(12) MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)') NULL, 
    ---- Data Masking Type - Email
   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL,
    ---- Data Masking Type - Random
   MembershipFee float MASKED WITH (FUNCTION = 'random(1, 12)') NULL
   ); 

 ----- Insert values into MemberShip table 
INSERT MemberShip (FirstName, LastName, PhoneNo, Email, MembershipFee) VALUES  
('Ryan', 'Arjun', '555-123-4567', 'ryan.arjun@gmail.com',5400), 
('Kimmy', 'Wang', '555-936-4568', 'kimmy,wang@yahoo.com.co',5400), 
('Black', 'Smith', '932-123-0569', 'black.smith@aol.com',5400),
('John', 'Ray', '932-025-0523', 'john.ray@rediffmail.com',5400); 

---- Pull values from MemberShip table
SELECT MemberID As Id, FirstName, LastName, PhoneNo, Email, MembershipFee as Fee FROM dbo.MemberShip; 
Id
FirstName
LastName
PhoneNo
Email
Fee
1
Ryan
Arjun
555-123-4567
ryan.arjun@gmail.com
5400
2
Kimmy
Wang
555-936-4568
kimmy,wang@yahoo.com.co
5400
3
Black
Smith
932-123-0569
black.smith@aol.com
5400
4
John
Ray
932-025-0523
john.ray@rediffmail.com
5400
In the above table, we have defined all the Data Masking type associated with various data columns such as –
  1. FirstName column is using the default data masking type where first character of the name will be display only and other information will be hide with X characters.
  2. PhoneNo column is using the custom string data masking type where starting five character will display only and other information will be hide with X characters.
  3. Email column is using the Email data masking type where starting five character will display only and other information will be hide with X characters.
  4. MemberShipFee column is using the Email data masking type where starting five character will display only and other information will be hide with X characters.

Create a user and Granting Permissions to View masked Data
Now, we have the Membership data table and we need to verify the data masking functionality on this table. For this, we need to create a user and authorize him to get access on the above data tale as given below:
Use Demo
Go
----- Create User
CREATE USER RyanArjun WITHOUT LOGIN; 
----- Give permission
GRANT SELECT ON Membership TO RyanArjun; 
 
We need to pull the data for the testing user as given below:
Use Demo
Go
----- Pull the data with the respective user
EXECUTE AS USER = 'RyanArjun'; 
SELECT Id=MemberID, FirstName, LastName, PhoneNo, Email, Fee= MembershipFee
FROM dbo.MemberShip; 
REVERT;   
Id
FirstName
LastName
PhoneNo
Email
Fee
1
RXXXXXX
Arjun
555-1XXXXXXX
rXXX@XXXX.com
2.112504
2
KXXXXXX
Wang
555-9XXXXXXX
kXXX@XXXX.com
1.681927
3
BXXXXXX
Smith
932-1XXXXXXX
bXXX@XXXX.com
1.824688
4
JXXXXXX
Ray
932-0XXXXXXX
jXXX@XXXX.com
2.633747

For the testing user ‘RyanArjun’, Dynamicdata masking is working perfectly and the requested sensitive data is not available for him.

How to identify the Masked Columns in your database?
In SQL Server 2016, we can use the sys.masked_columns view to query for table-columns that have a masking function applied to them. Basically, sys.masked_columns view inherits from the sys.columns view which is capable to returns all columns in the sys.columns view, plus the is_masked and masking_function columns, indicating if the column is masked, and if so, what masking function is defined. This view only shows the columns on which there is a masking function applied as given below:
Use Demo
Go
----- Pull the data from sys.masked_columns
SELECT C.NAME, TBL.NAME AS TABLE_NAME, C.IS_MASKED, C.MASKING_FUNCTION 
FROM SYS.MASKED_COLUMNS AS
JOIN SYS.TABLES AS TBL  
ON C.[OBJECT_ID] = TBL.[OBJECT_ID] 
WHERE IS_MASKED = 1; 
NAME
TABLE_NAME
IS_MASKED
MASKING_FUNCTION
FirstName
MemberShip
1
partial(1, "XXXXXX", 0)
PhoneNo
MemberShip
1
partial(5, "XXXXXXX", 0)
Email
MemberShip
1
email()
MembershipFee
MemberShip
1
random(1, 3)

Adding Data Mask on an new Column
In this section, we will add new column called EndDate to MemberShip data table and also add data masking functionality also as given below:
Use Demo
Go

---- Add EndDate Column to MemberShip
ALTER TABLE dbo.MemberShip
ADD EndDate DateTime MASKED WITH (FUNCTION = 'default()') NULL

----UpDate EndDate value
Update MemberShip
set EndDate='2016-12-31'

---- Pull values from MemberShip table
SELECT MemberID As Id, FName =FirstName, LName= LastName,
PhoneNo, Email, MembershipFee as Fee, EndDate
FROM dbo.MemberShip; 
Id
FName
LName
PhoneNo
Email
Fee
EndDate
1
Ryan
Arjun
555-123-4567
ryan.arjun@gmail.com
5400
12/31/2016
2
Kimmy
Wang
555-936-4568
kimmy,wang@yahoo.com.co
5400
12/31/2016
3
Black
Smith
932-123-0569
black.smith@aol.com
5400
12/31/2016
4
John
Ray
932-025-0523
john.ray@rediffmail.com
5400
12/31/2016

----- Pull the data with the respective user
EXECUTE AS USER = 'RyanArjun'; 
SELECT MemberID As Id, FName =FirstName, LName= LastName,
PhoneNo, Email, MembershipFee as Fee, EndDate
FROM dbo.MemberShip; 
REVERT;
Id
FName
LName
PhoneNo
Email
Fee
EndDate
1
RXXXXXX
Arjun
555-1XXXXXXX
rXXX@XXXX.com
1.321
1/1/1900
2
KXXXXXX
Wang
555-9XXXXXXX
kXXX@XXXX.com
2.761
1/1/1900
3
BXXXXXX
Smith
932-1XXXXXXX
bXXX@XXXX.com
1.645
1/1/1900
4
JXXXXXX
Ray
932-0XXXXXXX
jXXX@XXXX.com
2.889
1/1/1900
Editing a Mask on an Existing Column
If we want to apply data masking on the existing column then we need to use the alter command to apply the data masking as given below:
Use Demo
Go

---- ALTER LastName Column to MemberShip
ALTER TABLE dbo.MemberShip
ALTER COLUMN LastName varchar(100) MASKED WITH (FUNCTION = 'default()') NULL

----- Pull the data with the respective user
EXECUTE AS USER = 'RyanArjun'; 
SELECT MemberID As Id, FName =FirstName, LName= LastName,
PhoneNo, Email, MembershipFee as Fee, EndDate
FROM dbo.MemberShip; 
REVERT; 
Id
FName
LName
PhoneNo
Email
Fee
EndDate
1
RXXXXXX
xxxx
555-1XXXXXXX
rXXX@XXXX.com
1.946
1/1/1900
2
KXXXXXX
xxxx
555-9XXXXXXX
kXXX@XXXX.com
1.501
1/1/1900
3
BXXXXXX
xxxx
932-1XXXXXXX
bXXX@XXXX.com
1.833
1/1/1900
4
JXXXXXX
xxxx
932-0XXXXXXX
jXXX@XXXX.com
2.512
1/1/1900
Now, we can see that we have applied data masking on almost all columns and testing user is not capable to see the actual data and sensitive data is secured.

How to remove Dynamic data masking?
If we want to remove data masking from any column then we simply need to use drop command just before masked clause as given below:
Use Demo
Go

---- Drop Data Mask from the column
ALTER TABLE dbo.MemberShip
ALTER COLUMN LastName  DROP MASKED;

----- Pull the data with the respective user
EXECUTE AS USER = 'RyanArjun'; 
SELECT MemberID As Id, FName =FirstName, LName= LastName,
PhoneNo, Email, MembershipFee as Fee, EndDate
FROM dbo.MemberShip; 
REVERT;
Id
FName
LName
PhoneNo
Email
Fee
EndDate
1
RXXXXXX
Arjun
555-1XXXXXXX
rXXX@XXXX.com
1.321
1/1/1900
2
KXXXXXX
Wang
555-9XXXXXXX
kXXX@XXXX.com
2.761
1/1/1900
3
BXXXXXX
Smith
932-1XXXXXXX
bXXX@XXXX.com
1.645
1/1/1900
4
JXXXXXX
Ray
932-0XXXXXXX
jXXX@XXXX.com
2.889
1/1/1900
Now, we can apply the DDM to our table and secure sensitive information from the end users. 

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands