NEW T-SQL FEATURES IN SQL SERVER 2016 – XVIII | Dynamic Data Masking

Tags

NEW T-SQL FEATURES IN SQL SERVER 2016 – XVIII | Dynamic Data Masking

In this post we shall check out the new features Microsoft introduced in SQL Server 2016 | Dynamic Data Masking

New feature – XVIII | Dynamic Data Masking

Dynamic data masking limits sensitive data exposure by masking it to non-privileged users.

Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. DDM can be configured on the database to hide sensitive data in the result sets of queries over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.

**-From Microsoft

More Details-

1. A central data masking policy acts directly on sensitive fields in the database.

2. Designate privileged users or roles that do have access to the sensitive data.

3. DDM features full masking and partial masking functions, as well as a random mask for numeric data.

4. Simple Transact-SQL commands define and manage masks.

Types of masks available are
1. Default

2. Email

3. Random

4. Custom String

Examples

Prepare data for examples of Dynamic Data Masking

--

CREATE TABLE testDataMASKING
(
	  Id INT
	 ,[Name] VARCHAR(100)
	 ,DOJ DATETIME
	 ,EmailId VARCHAR(100)
	 ,Cell VARCHAR(20)
	 ,Salary DECIMAL(38,2)
)
GO

INSERT INTO testDataMASKING VALUES
(1,'Pawan',GETDATE() - 100 , 'Pawan@MSBISKILLS.COM','917-470-9890', 100000),
(2,'Ramesh',GETDATE() - 20 , 'Ramesh@MSBISKILLS.COM','917-471-9890',1900000),
(3,'Avtaar',GETDATE() - 19, 'Avtaar@MSBISKILLS.COM','917-470-9810',200000),
(4,'Sharlee',GETDATE() - 200, 'Sharlee@MSBISKILLS.COM','917-470-3890',300000),
(5,'Vaibhav',GETDATE() - 10, 'Vaibhav@MSBISKILLS.COM','917-470-9000',4000000),
(6,'Ishu',GETDATE() - 18, 'Ishu@MSBISKILLS.COM','917-470-9981',900000),
(7,'Chandar',GETDATE() - 19, 'Chandar@MSBISKILLS.COM','917-470-1234',190000),
(8,'Kishan',GETDATE() - 45, 'Kishan@MSBISKILLS.COM','917-470-7634',180000)
GO

--

/*Create a Read only user*/

--

CREATE USER OnlyRead WITHOUT LOGIN
GRANT SELECT ON testDataMASKING TO OnlyRead
GO

--

Current data that we need to mask

--

SELECT * FROM testDataMASKING

Id          Name                    DOJ                     EmailId                     Cell                 Salary        
----------- --------------- ------- ----------------------- --------------------------  -------------------- ------------- 
1           Pawan                   2017-10-15 14:30:29.903 Pawan@MSBISKILLS.COM        917-470-9890         100000.00     
2           Ramesh                  2018-01-03 14:30:29.903 Ramesh@MSBISKILLS.COM       917-471-9890         1900000.00    
3           Avtaar                  2018-01-04 14:30:29.903 Avtaar@MSBISKILLS.COM       917-470-9810         200000.00     
4           Sharlee                 2017-07-07 14:30:29.903 Sharlee@MSBISKILLS.COM      917-470-3890         300000.00     
5           Vaibhav                 2018-01-13 14:30:29.903 Vaibhav@MSBISKILLS.COM      917-470-9000         4000000.00    
6           Ishu                    2018-01-05 14:30:29.903 Ishu@MSBISKILLS.COM         917-470-9981         900000.00     
7           Chandar                 2018-01-04 14:30:29.903 Chandar@MSBISKILLS.COM      917-470-1234         190000.00     
8           Kishan                  2017-12-09 14:30:29.903 Kishan@MSBISKILLS.COM       917-470-7634         180000.00     
                                                                                                                           
(8 rows affected)   

--

Example of Default Function Masking for Name and DOJ column

--
     
ALTER TABLE testDataMASKING
	   ALTER COLUMN [Name] ADD MASKED WITH (FUNCTION='DEFAULT()')
	 
ALTER TABLE testDataMASKING
	   ALTER COLUMN DOJ ADD MASKED WITH (FUNCTION='DEFAULT()')

EXECUTE AS USER = 'OnlyRead';  
SELECT * FROM testDataMASKING;  
REVERT;  

OUTPUT

Id          Name           DOJ                     EmailId                     Cell                 Salary            
----------- -------------- ----------------------- --------------------------- -------------------- ----------------- 
1           xxxx           1900-01-01 00:00:00.000 Pawan@MSBISKILLS.COM        917-470-9890         100000.00         
2           xxxx           1900-01-01 00:00:00.000 Ramesh@MSBISKILLS.COM       917-471-9890         1900000.00        
3           xxxx           1900-01-01 00:00:00.000 Avtaar@MSBISKILLS.COM       917-470-9810         200000.00         
4           xxxx           1900-01-01 00:00:00.000 Sharlee@MSBISKILLS.COM      917-470-3890         300000.00         
5           xxxx           1900-01-01 00:00:00.000 Vaibhav@MSBISKILLS.COM      917-470-9000         4000000.00        
6           xxxx           1900-01-01 00:00:00.000 Ishu@MSBISKILLS.COM         917-470-9981         900000.00         
7           xxxx           1900-01-01 00:00:00.000 Chandar@MSBISKILLS.COM      917-470-1234         190000.00         
8           xxxx           1900-01-01 00:00:00.000 Kishan@MSBISKILLS.COM       917-470-7634         180000.00         
                                                                                                                      
(8 rows affected)    

--

Example of Function Masking for Email

--

ALTER Table testDataMASKING
ALTER COLUMN EmailId ADD MASKED WITH (FUNCTION='Email()')

EXECUTE AS USER = 'OnlyRead';  
SELECT * FROM testDataMASKING;  
REVERT;  

OUTPUT

Id          Name            DOJ                     EmailId                  Cell                 Salary         
----------- --------- ----- ----------------------- -------------------- --- -------------------- -------------- 
1           xxxx            1900-01-01 00:00:00.000 PXXX@XXXX.com            917-470-9890         100000.00      
2           xxxx            1900-01-01 00:00:00.000 RXXX@XXXX.com            917-471-9890         1900000.00     
3           xxxx            1900-01-01 00:00:00.000 AXXX@XXXX.com            917-470-9810         200000.00      
4           xxxx            1900-01-01 00:00:00.000 SXXX@XXXX.com            917-470-3890         300000.00      
5           xxxx            1900-01-01 00:00:00.000 VXXX@XXXX.com            917-470-9000         4000000.00     
6           xxxx            1900-01-01 00:00:00.000 IXXX@XXXX.com            917-470-9981         900000.00      
7           xxxx            1900-01-01 00:00:00.000 CXXX@XXXX.com            917-470-1234         190000.00      
8           xxxx            1900-01-01 00:00:00.000 KXXX@XXXX.com            917-470-7634         180000.00      
                                                                                                                 
(8 rows affected)      

--

Example of Partial Masking for Cell column

--

ALTER Table testDataMASKING
ALTER COLUMN Cell ADD MASKED WITH (FUNCTION = 'partial(1,"p",1)')  

EXECUTE AS USER = 'OnlyRead';  
SELECT * FROM testDataMASKING;  
REVERT;  

OUTPUT

Id          Name         DOJ                     EmailId            Cell       Salary         
----------- ---------- - ----------------------- -----------------  ------- -- -------------- 
1           xxxx         1900-01-01 00:00:00.000 PXXX@XXXX.com      9p0        100000.00      
2           xxxx         1900-01-01 00:00:00.000 RXXX@XXXX.com      9p0        1900000.00     
3           xxxx         1900-01-01 00:00:00.000 AXXX@XXXX.com      9p0        200000.00      
4           xxxx         1900-01-01 00:00:00.000 SXXX@XXXX.com      9p0        300000.00      
5           xxxx         1900-01-01 00:00:00.000 VXXX@XXXX.com      9p0        4000000.00     
6           xxxx         1900-01-01 00:00:00.000 IXXX@XXXX.com      9p1        900000.00      
7           xxxx         1900-01-01 00:00:00.000 CXXX@XXXX.com      9p4        190000.00      
8           xxxx         1900-01-01 00:00:00.000 KXXX@XXXX.com      9p4        180000.00      
                                                                                              
(8 rows affected)       

--

Example of Random Masking on column Salary

--

ALTER Table testDataMASKING
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION='Random(0,7)')	

EXECUTE AS USER = 'OnlyRead';  
SELECT * FROM testDataMASKING;  
REVERT;     

OUTPUT       

Id          Name      DOJ                     EmailId              Cell      Salary     
----------- --------- ----------------------- -------------------- ------- - ---------- 
1           xxxx      1900-01-01 00:00:00.000 PXXX@XXXX.com        9p0       3.69       
2           xxxx      1900-01-01 00:00:00.000 RXXX@XXXX.com        9p0       1.24       
3           xxxx      1900-01-01 00:00:00.000 AXXX@XXXX.com        9p0       3.35       
4           xxxx      1900-01-01 00:00:00.000 SXXX@XXXX.com        9p0       2.90       
5           xxxx      1900-01-01 00:00:00.000 VXXX@XXXX.com        9p0       1.98       
6           xxxx      1900-01-01 00:00:00.000 IXXX@XXXX.com        9p1       0.05       
7           xxxx      1900-01-01 00:00:00.000 CXXX@XXXX.com        9p4       0.12       
8           xxxx      1900-01-01 00:00:00.000 KXXX@XXXX.com        9p4       5.51       
                                                                                        
(8 rows affected)   

--

Drop All Masks and check data for readonly user

--

ALTER TABLE testDataMASKING 
ALTER COLUMN Salary DROP MASKED

ALTER TABLE testDataMASKING 
ALTER COLUMN Cell DROP MASKED

ALTER TABLE testDataMASKING 
ALTER COLUMN EmailId DROP MASKED

ALTER TABLE testDataMASKING 
ALTER COLUMN DOJ DROP MASKED

ALTER TABLE testDataMASKING 
ALTER COLUMN [Name] DROP MASKED

EXECUTE AS USER = 'OnlyRead';  
SELECT * FROM testDataMASKING;  
REVERT;  

--

OUTPUT after Masking removed for Onlyread user

--

Id          Name            DOJ                     EmailId                          Cell                 Salary         
----------- ----------- --- ----------------------- ---------------------------- --- -------------------- -------------- 
1           Pawan           2017-10-15 14:30:29.903 Pawan@MSBISKILLS.COM             917-470-9890         100000.00      
2           Ramesh          2018-01-03 14:30:29.903 Ramesh@MSBISKILLS.COM            917-471-9890         1900000.00     
3           Avtaar          2018-01-04 14:30:29.903 Avtaar@MSBISKILLS.COM            917-470-9810         200000.00      
4           Sharlee         2017-07-07 14:30:29.903 Sharlee@MSBISKILLS.COM           917-470-3890         300000.00      
5           Vaibhav         2018-01-13 14:30:29.903 Vaibhav@MSBISKILLS.COM           917-470-9000         4000000.00     
6           Ishu            2018-01-05 14:30:29.903 Ishu@MSBISKILLS.COM              917-470-9981         900000.00      
7           Chandar         2018-01-04 14:30:29.903 Chandar@MSBISKILLS.COM           917-470-1234         190000.00      
8           Kishan          2017-12-09 14:30:29.903 Kishan@MSBISKILLS.COM            917-470-7634         180000.00      
                                                                                                                         
(8 rows affected)                                                                                                        

--

Notes

1. Creating a mask on a column does not prevent updates to that column.
2. Any user can update the data if they have write permissions.
3. If you use SELECT INTO or INSERT INTO to copy data from a masked column then you will get masked data in the target table.
4. Dynamic Data Masking is also applicable on SQL Server Import and Export.
5. Masking is not applicable on following column types – Encrypted columns (Always Encrypted), FILESTREAM, COLUMN_SET or sparse column and computed column.

Refer Microsoft LINK below for more details-

1. https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

1 thought on “NEW T-SQL FEATURES IN SQL SERVER 2016 – XVIII | Dynamic Data Masking”

Pingback: NEW T-SQL FEATURES IN SQL SERVER 2016 | Complete List | Improving my SQL BI Skills

Share this

Related

1 thought on “NEW T-SQL FEATURES IN SQL SERVER 2016 – XVIII | Dynamic Data Masking”

Leave a comment Cancel reply