Dynamic Data Masking with MS SQL Server

Post created using SQL Server 2016 SP2

Lets say you have some users who need access to data in a database but you want to obfuscate sensitive data in certain columns. You can do this through masking the data by user roles.

To do this is relatively simple. You need to add additional definitions to your table columns. Take for example my below table and data.

DROP TABLE IF EXISTS dbo.People;
GO

CREATE TABLE dbo.People  
	(
	 PeopleId		int				IDENTITY PRIMARY KEY  NOT NULL
	,Forename		varchar(100)	MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL
	,Suranme		varchar(100)	NOT NULL
	,DOB			date			MASKED WITH (FUNCTION = 'default()')		NULL 
	,Email			varchar(100)	MASKED WITH (FUNCTION = 'email()')			NULL
	,UserName		varchar(100)	MASKED WITH (FUNCTION = 'default()')		NULL
	,PayBandId		tinyint			MASKED WITH (FUNCTION = 'random(100, 255)') NULL
	);

INSERT dbo.People (Forename, Suranme, DOB, Email, UserName, PayBandId) 
VALUES   
	 ('Etta', 'Coleman', '1992-07-01','ecoleman@demomail.com','Etta92' ,3)
	,('Jimi', 'Bell', '1970-03-18','jbell@demomail.com.co','jimihendrix' ,6)
	,('Tom',	'Vasquez', '1986-05-23','tvasquez@demomail.net','vasq86' ,8)
	,('Davina', 'Lawrence', '2000-11-09','dlawrence@demomail.net','davina' ,1);

This will create a table like below.

Lets now create a user to view the masked data and grant that user read permissions.

-- Create database user.
CREATE USER MaskUser WITHOUT LOGIN;  
-- Grant read.
GRANT SELECT ON dbo.People TO MaskUser;  

Now you can run the following code as MaskUser and it will return the data masked.

-- Run as Mask User and revert after.
EXECUTE AS USER = 'MaskUser';  
SELECT * FROM dbo.People;  
REVERT;

Finally you can grant MaskUser the ability to read the non obfuscated data like so.

-- Grant MarkUser the ability to read the data unmasked.
GRANT UNMASK TO MaskUser;  
EXECUTE AS USER = 'MaskUser';  
SELECT * FROM dbo.People;  
REVERT; 

For further reading head on over to the full article on MS Docs.