Did you say Data Masking?

Author: Eric Rouach, Madeira Data Solutions

Date: August 2022



If you are using SQL Server and need a lightweight encryption solution at the database user resolution with almost no changes to your application, then Dynamic Data Masking might definitely be for you!


DDM allows you to prevent unprivileged users from viewing secret data such as password, credit card numbers or any data you might consider sensitive.


It has been available since SQL Server 2016 and is also available for Azure SQL Database and Azure SQL Managed Instance. Some additional features are only available for preview on SQL Server 2022 by the time of this article.


DDM uses built-in functions applied on a column containing the sensitive data. It is very user friendly and easily removable. You should also note that DDM will not prevent highly privileged users from viewing the masked data so that it is not a substitute to Always Encrypted along with RBAC (Role Based Access Control).


Let's go through a few examples:


For the following demo, I have used the AdventureWorks2014 database.

You may copy and paste the following T-SQL code into SSMS and try it yourself!


USE [AdventureWorks2014];

GO


SELECT

*

FROM

Sales.CreditCard


Here's a sample of the result set:


--Create a new SQL Server login

CREATE LOGIN DDM_User1 WITH PASSWORD = 'StrongPassword123!';

GO


--Create a database user mapped to the previously created login

CREATE USER DDM_User1 FOR LOGIN DDM_User1


--Grant SELECT permissions to DDM_User1

GRANT SELECT TO DDM_User1


--Let's suppose we want to prevent DDM_User1 from viewing sensitive data such as credit card numbers;

--we'll take the following steps:


--Let's mask the [CardNumber] column so that only the last 4 digits are revealed.

--We'll use a Custom String (partial) function to match the credit card number format:

ALTER TABLE Sales.CreditCard

ALTER COLUMN CardNumber ADD MASKED WITH (FUNCTION = 'partial(0,"****-****-****-",4)')


/*Let's explain the function:

0 is the prefix: how many visible characters do we want to be visible at the beginning of the string

"****-****-****-" is the padding string: can be any character(s) we like to mask the data

4 is the suffix: how many visible characters do we want to be visible at the end of the string

*/


--Let's revoke the UNMASK permission from DDM_User1

REVOKE UNMASK FROM DDM_User1


--Let's impersonate DDM_User1 and query the dbo.CreditCards table

EXECUTE AS USER = 'DDM_User1'


SELECT

*

FROM

Sales.CreditCard


--The CardNumber column is now masked according to the Custom String (partial) function:


REVERT --the revert command stops impersonating DDM_User1


--Let's mask the [ExpYear] column so that a random value appears instead.

--We'll use a Random function to display a random number within a specified range:


ALTER TABLE Sales.CreditCard

ALTER COLUMN [ExpYear] ADD MASKED WITH (FUNCTION = 'random(9999,9999)')


--Again, let's test the result:


EXECUTE AS USER = 'DDM_User1'


SELECT

*

FROM

Sales.CreditCard


--As we specified a range from 9999 to 9999, the value shown is always 9999



REVERT


--Let's mask the [ModifiedDate] column so that 1900-01-01 00:00:00.000 appears instead.

--We'll use a Default function:


ALTER TABLE Sales.CreditCard

ALTER COLUMN [ModifiedDate] ADD MASKED WITH (FUNCTION = 'default()')


--Again, let's test the result:


EXECUTE AS USER = 'DDM_User1'


SELECT

*

FROM

Sales.CreditCard



REVERT


--Let's remove the masking from the [ModifiedDate] column:


ALTER TABLE Sales.CreditCard

ALTER COLUMN [ModifiedDate] DROP MASKED;


--Let's query the [Person].[EmailAddress] table


SELECT

*

FROM

[Person].[EmailAddress]



--Now we'll mask the [EmailAddress] column using the email() built-in function:


ALTER TABLE [Person].[EmailAddress]

ALTER COLUMN [EmailAddress] ADD MASKED WITH (FUNCTION = 'email()')


--Again, let's test the result:


EXECUTE AS USER = 'DDM_User1'


SELECT

*

FROM

[Person].[EmailAddress]



REVERT


--Note that a new Datetime() built-in function for SQL Server 2022 allows more granularity for masking date & time columns;

--it allows masking only certain parts of the date/time.



~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*