Dynamic Data Masking in SQL Server on Linux

Today I was playing around with SQL Server on Linux and I really wanted to get column encryption working but for whatever reason that wasn’t working. But then I stumbled upon Dynamic Data Masking and that at least worked. First, create your table and tell SQL Server that you want one of the columns to be masked for non-privileged users:
CREATE TABLE People
(
	PersonId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	FirstName varChar(max) NOT NULL,
	LastName varChar(max) NOT NULL,
	SocialSecurityNumber varchar(11) MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)') NOT NULL
)
Insert some data:
INSERT INTO People (FirstName, LastName, SocialSecurityNumber) VALUES ( 'Bob', 'Dole', '123-45-6789' )
And, as an admin you’ll see the results unmasked:
PersonId  FirstName  LastName  SocialSecurityNumber
1         Bob        Dole      123-45-6789
Now, create a limited user (I’m creating this without a login just for demo purposes):
CREATE USER LimitedAccess WITHOUT LOGIN;  
This user doesn’t have permission to do pretty much anything so we’re going to grant it regular SELECT access. Because I want this user to be the application user, too, I’m also going to grant INSERT access.
GRANT SELECT ON People TO LimitedAccess;
GRANT INSERT ON People TO LimitedAccess;
And then finally I’m going to impersonate this user, insert a record and select all:
EXECUTE AS USER = 'LimitedAccess';
INSERT INTO People (FirstName, LastName, SocialSecurityNumber) VALUES ( 'Tim', 'Tool', '111-22-3333' );
SELECT * FROM People;
REVERT;
Which gives us a masked result
PersonId  FirstName  LastName  SocialSecurityNumber
1         Bob        Dole      XXX-XX-6789
2         Tim        Tool      XXX-XX-3333
Pretty cool. Microsoft has a security note that depending on what you mask there are ways to infer the contents still that’s really worth reading.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.