Dynamic Data Masking

SQL Server 2019 provides dynamic data masking (DDM), which limits sensitive data exposure by masking it to non-privileged users. This is not really a form of encryption at disk but nevertheless is useful in certain scenarios, such as if you want to hide sections of a credit card number from support staff personnel. Traditionally, this logic would have been implemented at the application layer; however, this is not the case now because it is controlled within SQL Server.

Note

A masking rule cannot be applied on a column that is Always Encrypted.  

Types

You can choose from four different masks where selection usually depends on your data types:

• DEFAULT: Full masking according to the data types of the designated fields
• EMAIL: A masking method that exposes the first letter of an email address, such as [email protected]
• RANDOM: A random masking function for use on any numeric type to mask the original value with a random value within a specified range
• CUSTOM: Exposes the first and last letters and adds a custom padding string in the middle

Implementing DDM

The following example creates a table with different masking rules applied to the columns. FirstName will only expose the first letter, Phone will use the default masking rule, and for Email, we will apply the email masking rule:

CREATE TABLE dbo.Users 
  (UserID INT IDENTITY PRIMARY KEY,  
   FirstName VARCHAR(150) MASKED WITH (FUNCTION = 'partial(1,"XXXXX",0)') NULL,  
   LastName VARCHAR(150) NOT NULL,  
   Phone VARCHAR(20) MASKED WITH (FUNCTION = 'default()') NULL,  
   Email VARCHAR(150) MASKED WITH (FUNCTION = 'email()') NULL);  
   GO
  
INSERT dbo.Users
(FirstName, LastName, Phone, Email) 
VALUES   
('Arun', 'Sirpal', '777-232-232', '[email protected]'),  
('Tony', 'Mulsti', '111-778-555', '[email protected]'),  
('Pedro', 'Lee', '890-097-866', '[email protected]') ,
('Bob', 'Lee', '787-097-866', '[email protected]');  
GO

Figure 2.21: Table with masking rules applied

To confirm your masking rules, the following query should be executed:

SELECT OBJECT_NAME(object_id) TableName, 
    name ColumnName, 
    masking_function MaskFunction
  FROM sys.masked_columns
  ORDER BY TableName, ColumnName;

Figure 2.22: Table with query executed

If you connect to the database as a SQL login that has only read access (as indicated by the following code), you will see the masked data. In other words, the login does not have the right to see the true value of the data, as demonstrated in the following code.

EXECUTE AS USER = 'support'
SELECT SUSER_NAME(), USER_NAME();  
SELECT * FROM  dbo.Users

Figure 2.23: Table with data partially masked

If you decide to allow the user to see the data in its native form, you can issue the GRANT UNMASK command as shown here:

GRANT UNMASK TO support;
 GO
 EXECUTE AS USER = 'support'
SELECT SUSER_NAME(), USER_NAME();  
SELECT * FROM  dbo.Users

Figure 2.24: Table with data unmasked

Issue the REVOKE command to remove this capability:

REVOKE UNMASK TO support;
EXECUTE AS USER = 'support'
SELECT SUSER_NAME(), USER_NAME();  
SELECT * FROM  dbo.Users

Figure 2.25: Table with unmask revoked

As you can see, implementing this feature requires no changes to application code and can be controlled via permissions within SQL Server by deducing who has and has not got the ability to view the data. Even though this is not a true form of encryption at disk level, this feature is only a small element of the wider security strategy for your SQL servers and is best used in conjunction with other features discussed so far to provide broader defense.