The Hidden Layers of Dynamic Data Masking in SQL Server

Introduction

Dynamic Data Masking (DDM) is a powerful feature in SQL Server designed to prevent unauthorized access to sensitive data. By applying masking rules to specific columns in your database tables, DDM limits data exposure by obfuscating the data for users who shouldn’t see it, without altering the actual data stored in the database.

Types of Masks

SQL Server provides four types of masking functions to suit different data protection needs:

• Default Mask: Applies full masking based on the data type of the field. For example, a string field would display as “XXXX” when masked.
• Email Mask: Reveals only the first letter of an email address and replaces the rest with “XXX“, ending with “.com“. For instance, “aXXX@XXXX.com“.
• Custom String Mask: Exposes the first and last characters of a string and replaces the middle characters with a custom padding string. For example, “KXXXa“.
• Random Mask: Specifically for numeric data types, this mask replaces the original value with a random number within a specified range.

It’s important to note that only the displayed data is masked; the underlying data in the database remains unchanged. This allows authorized users or roles—such as application administrators—to access the real data when necessary.

Permissions and Access Control

By default, the database owner (dbo) has permission to view unmasked data, adhering to the principle that the database owner should have unrestricted access. However, you can modify this behavior by granting or revoking permissions using appropriate roles and commands:

GRANT UNMASK TO [username];

This flexibility allows you to define a set of privileged users or roles who can view unmasked data, enhancing your data security management.

Can Users Modify Masked Data?

A common question is whether DDM prevents users from performing updates or other Data Manipulation Language (DML) operations on masked columns. The short answer is no. Users without the UNMASK permission cannot see the masked data, but they can still perform update operations on it. This might seem counterintuitive, but it allows data to be modified without exposing its contents to unauthorized users.

UNMASK Permission and Performance Walkthrough

Let’s explore this behavior with a practical example. You can run the following script in SQL Server Management Studio (SSMS) to follow along.

-- Demonstrates the UNMASK permission behavior with other DML permissions
-- Also explores the performance overhead

-- Create a sample database
USE master;
GO

IF (DB_ID(N'MaskingDemo') IS NOT NULL)
BEGIN
    ALTER DATABASE [MaskingDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [MaskingDemo];
END

CREATE DATABASE MaskingDemo;
GO

-- Create a table with a masked column
USE MaskingDemo;
GO

CREATE TABLE dbo.T1
(
    Col1 VARCHAR(10) MASKED WITH (FUNCTION = 'default()')
);
GO

-- Insert some data
INSERT INTO dbo.T1 (Col1) VALUES ('abc');
GO

-- View the data as a sysadmin
SELECT * FROM dbo.T1;
GO

-- Create an unprivileged user
CREATE USER User1 WITHOUT LOGIN;
GO

GRANT SELECT TO User1;
GO

-- Impersonate the unprivileged user
EXECUTE AS USER = 'User1';
GO

SELECT SYSTEM_USER;
GO

-- View the data as the unprivileged user
SELECT * FROM dbo.T1;
GO

-- Attempt to query the exact row
SELECT * FROM dbo.T1 WHERE Col1 = 'abc';
GO

-- Additional queries to test data visibility
SELECT * FROM dbo.T1 WHERE Col1 > 'aaa';
GO

SELECT * FROM dbo.T1 WHERE Col1 < 'aaa';
GO

SELECT * FROM dbo.T1 WHERE Col1 BETWEEN 'aaa' AND 'zzz';
GO

SELECT * FROM dbo.T1 WHERE Col1 BETWEEN 'BBB' AND 'zzz';
GO

-- Attempt to update the record
UPDATE dbo.T1 SET Col1 = 'xyz';
-- Expected error due to insufficient permissions:
-- Msg 229, Level 14, State 5, Line 63
-- The UPDATE permission was denied on the object 'T1', database 'MaskingDemo', schema 'dbo'.
GO

-- Revert to sysadmin
REVERT;
SELECT SYSTEM_USER;
GO

-- Conclusion: Dynamic Data Masking only obfuscates the data and does not prevent users from querying it.

As demonstrated, even after granting SELECT permission to the user, the data remains masked in query results unless the user has the UNMASK permission.

Granting Update Permission

-- Grant UPDATE permission to the user
USE MaskingDemo;
GO

GRANT UPDATE TO User1;
GO

-- Impersonate the user again
EXECUTE AS USER = 'User1';
GO

SELECT SYSTEM_USER;
GO

-- Update the record
UPDATE dbo.T1 SET Col1 = 'xyz';
GO

-- Verify that the data is still masked
SELECT * FROM dbo.T1;
GO

-- Attempt to delete the record
DELETE FROM dbo.T1 WHERE Col1 = 'xyz';
-- Expected error if DELETE permission is not granted:
-- Msg 229, Level 14, State 5, Line 101
-- The DELETE permission was denied on the object 'T1', database 'MaskingDemo', schema 'dbo'.
GO

-- Revert to sysadmin
REVERT;
SELECT SYSTEM_USER;
GO

-- Conclusion: Granting DML permissions does not override the masking; data remains masked in query results.

Performance Considerations

Now, let’s examine the performance overhead introduced by DDM.

-- Performance difference exploration

-- Create a test table without masking
USE MaskingDemo;
GO

DROP TABLE IF EXISTS dbo.T2;
GO

CREATE TABLE dbo.T2
(
    RowId INT IDENTITY(1,1) NOT NULL,
    Col1 VARCHAR(10) NOT NULL,
    CONSTRAINT CX_T2_RowId PRIMARY KEY CLUSTERED (RowId),
    INDEX IX_Col1 NONCLUSTERED (Col1)
);

-- Create an identical table with a masked column
DROP TABLE IF EXISTS dbo.T3;
GO

CREATE TABLE dbo.T3
(
    RowId INT IDENTITY(1,1) NOT NULL,
    Col1 VARCHAR(10) MASKED WITH (FUNCTION = 'default()') NOT NULL,
    CONSTRAINT CX_T3_RowId PRIMARY KEY CLUSTERED (RowId),
    INDEX IX_Col1 NONCLUSTERED (Col1)
);

-- Insert data into T2
SET NOCOUNT ON;
DECLARE @BatchSize INT = 10000;
DECLARE @i INT = 1;
WHILE @i <= @BatchSize
BEGIN
    DECLARE @val VARCHAR(10) = CAST(@i AS VARCHAR(10));
    INSERT INTO dbo.T2 (Col1) VALUES (@val);
    SET @i += 1;
END;

-- Insert additional batches for faster loading
DECLARE @NumberOfBatches INT = 99;
DECLARE @CurrentBatch INT = 1;
WHILE @CurrentBatch <= @NumberOfBatches
BEGIN
    DECLARE @BatchStartValue INT = @BatchSize * @CurrentBatch;
    INSERT INTO dbo.T2 (Col1)
    SELECT CAST(RowId + @BatchStartValue AS VARCHAR(10)) FROM dbo.T2 WHERE RowId <= @BatchSize;
    SET @CurrentBatch += 1;
END;
GO

-- Rebuild indexes to eliminate fragmentation
ALTER INDEX CX_T2_RowId ON dbo.T2 REBUILD;
ALTER INDEX IX_Col1 ON dbo.T2 REBUILD;
GO

-- Copy data to T3
INSERT INTO dbo.T3
SELECT Col1 FROM dbo.T2 ORDER BY RowId;

-- Rebuild indexes on T3
ALTER INDEX CX_T3_RowId ON dbo.T3 REBUILD;
ALTER INDEX IX_Col1 ON dbo.T3 REBUILD;

-- We now have two identical tables except for the masking on T3

-- Enable 'Include Actual Execution Plan' in SSMS (Ctrl+M)

-- Execute both queries simultaneously
SELECT * FROM dbo.T2; -- Query 1
SELECT * FROM dbo.T3; -- Query 2

-- Analyze the execution plans and note differences in cost and performance

Analysis:

• Execution Plan Costs:
  • Query 1 (unmasked): ~49%
  • Query 2 (masked): ~51%
• Estimated Subtree Costs:
  • Query 1: 2.926
  • Query 2: 3.029

The slight overhead in Query 2 is due to the Compute Scalar operator added by the masking function. However, the additional cost is minimal, even when scanning a million rows. The overhead is proportional to the number of rows that need masking, and queries that use index seeks will experience less overhead compared to full table scans.

Permission Changes in SQL Server 2022

Starting with SQL Server 2022, you can exercise more granular control over who can view unmasked data. You can grant or revoke the UNMASK permission at the database, schema, table, or column level to users, database roles, Microsoft Entra identities, or groups. This enhancement allows for more precise data security management.

For example, to grant UNMASK permission on a specific column:

GRANT UNMASK ON Schema.Table(Column) TO Role;

Conclusion

Dynamic Data Masking acts as a protective layer over your sensitive data, ensuring that unauthorized users see only obfuscated versions of the data while the underlying information remains unchanged in the database. It’s a powerful tool for enhancing data security without requiring significant changes to your database structure or application logic. By understanding and implementing DDM effectively, you can safeguard your data against unauthorized access while maintaining necessary functionality for authorized users.