Implementing Dynamic Data Masking in SQL Server

Introduction to Dynamic Data Masking (DDM)

Dynamic Data Masking (DDM) is a technique that restricts access to sensitive data by masking it for users without the required privileges. This approach makes it easier to design and implement security measures in your applications, ensuring that sensitive information is protected while allowing authorized users to access necessary data.

Permissions Required for Dynamic Data Masking

To create a table with dynamic data masking, you don’t need special permissions beyond the standard CREATE TABLE and ALTER permissions for the schema. However, there are additional permissions needed for manipulating the data masks.

1. Modifying Masks: You need the ALTER ANY MASK permission, in addition to ALTER on the table.
2. Viewing Data: Users with SELECT permissions can see only the masked data. Assign UNMASK permission to allow viewing unmasked data.
3. Control Permissions: The CONTROL permission includes both ALTER ANY MASK and UNMASK.

Limitations and Restrictions of Data Masking

There are some column types that cannot be masked, including:

• Encrypted columns (Always Encrypted)
• FILESTREAM columns
• Sparse columns that are part of a column set
• Computed columns: If a computed column depends on a masked column, it will show masked data.
• Columns involved in a FULLTEXT index

Example: Creating a Table with a Mask

You can create a table with dynamic data masking as shown below:

CREATE TABLE MembershipMask  
(
  MemberID int IDENTITY PRIMARY KEY,  
  FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,  
  LastName varchar(100) NOT NULL,  
  Phone varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,  
  Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL  
);

Insert sample data:

INSERT MembershipMask (FirstName, LastName, Phone, Email) VALUES  
('Vipul', 'Bhatt', '456.123.4567', 'Vipul@Himanshupatel.in'),  
('Dev', 'Shah', '456.123.3333', 'Dev@Himanshupatel.in'),  
('Raj', 'Patel', '555.123.4569', 'raj@Himanshupatel.in');

Querying the table will display masked data:

SELECT * FROM MembershipMask;

Example output for a user with SELECT permission:

MemberID FirstName LastName Phone Email
1        VXXXXXXX  Bhatt     xxxx  VXXX@XXXX.com
2        DXXXXXXX  Shah      xxxx  DXXX@XXXX.com
3        RXXXXXXX  Patel     xxxx  RXXX@XXXX.com

Viewing the Data Masking Definition

Use the following query:

SELECT TBLS.name AS TableName, MC.NAME AS ColumnName, MC.is_masked AS IsMasked, MC.masking_function AS MaskFunction  
FROM sys.masked_columns AS MC  
JOIN sys.tables AS TBLS ON MC.object_id = TBLS.object_id  
WHERE is_masked = 1;

Dropping a Mask

To remove a mask from a column:

ALTER TABLE Membership  
ALTER COLUMN LastName DROP MASKED;

Adding or Editing a Mask on an Existing Column

ALTER TABLE Membership  
ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"XXX",0)');

Granting and Revoking Mask Permissions

GRANT UNMASK TO TestUserins;
GO
REVOKE UNMASK FROM TestUserins;
GO

Implementing Partial Masking

To reveal only the last 4 digits of a national ID number:

ALTER TABLE EmpInfo  
ALTER COLUMN NatID NVARCHAR(15)  
MASKED WITH (FUNCTION = 'partial(0, "xxxxx", 4)') NOT NULL;  
-- e.g., 716374314 becomes xxxxx4314

Implementing Random Masking

To mask a monetary value within a range:

ALTER TABLE EmpInfo  
ALTER COLUMN SalesYTD MONEY  
MASKED WITH (FUNCTION = 'random(101, 999)') NOT NULL;

Further Reading

For more information, refer to the official documentation:

Microsoft Docs on Dynamic Data Masking