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.