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.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Comments