Lenni's Technology Blog

Dynamic Data Masking (DDM) is a security feature introduced back in SQL Server 2016 that obscures sensitive data in the result set of a query, ensuring that unauthorized users can’t see the data they shouldn’t access. See my older blog post at https://lennilobel.wordpress.com/2016/05/07/sql-server-2016-dynamic-data-masking-ddm/ for an introduction to this feature. This blog post explains the new DDM capabilities added in SQL Server 2022; specifically, granular permissions.

For example, here is a table with four masked columns, populated with a few rows of data:

-- Create table with a few masked columns
CREATE TABLE Membership(
    MemberId int IDENTITY PRIMARY KEY,
    FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(2, "...", 2)') NULL,
    LastName varchar(100) NOT NULL,
    Phone varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
    Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL,
    DiscountCode smallint MASKED WITH (FUNCTION = 'random(1, 100)') NULL)

-- Populate table
INSERT INTO Membership VALUES 
 ('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com', 10),
 ('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co', 5),
 ('Dan', 'Mu', '555.123.4569', 'ZMu@contoso.net', 50),
 ('Jane', 'Smith', '454.222.5920', 'Jane.Smith@hotmail.com', 40),
 ('Danny', 'Jones', '674.295.7950', 'Danny.Jones@hotmail.com', 30)

In order for a user to see the data in the four masked columns, they must be granted the UNMASK permission. However, prior to SQL Server 2022, this was a database-wide permission; users that have the UNMASK permission can see every masked column in every table of every schema in the database.

The granular DDM permissions feature introduced in SQL Server 2022 is a significant enhancement that addresses this major limitation. Previously, SQL Server allowed granting or revoking the UNMASK permission only at the database level, which limited its flexibility and adoption. However, SQL Server 2022 expands this capability, offering much-needed granularity. Now, administrators can grant or revoke UNMASK permissions at various levels, providing tailored access control that matches specific security requirements.

This granular control can be applied:

• Database Level: As before, affecting all masked columns across the entire database.
• Schema Level: Affecting all tables within a specific schema.
• Table Level: Applying to all columns within a specific table.
• Column Level: The most granular level, targeting individual columns within a table.

This flexibility greatly enhances the practical use of Dynamic Data Masking by allowing precise control over who can see unmasked data, ensuring that only authorized users can access sensitive information at the level of detail appropriate to their role or needs. Let’s see how to grant UNMASK permissions at the individual column level.

We’ll set column-level UNMASK permissions for a specific user, named ContactUser, who has been tasked with reaching out to members. To facilitate this, they’ll need access to certain information that’s normally masked, specifically the FirstName, Phone, and Email columns within the Membership table. However, they don’t require access to the DiscountCode, which should remain masked:

-- Create a new user called ContactUser with no login
CREATE USER ContactUser WITHOUT LOGIN

-- Grant SELECT permissions on the Membership table to ContactUser
GRANT SELECT ON Membership TO ContactUser

-- Grant UNMASK permission on specific columns to ContactUser
GRANT UNMASK ON Membership(FirstName) TO ContactUser
GRANT UNMASK ON Membership(Phone) TO ContactUser
GRANT UNMASK ON Membership(Email) TO ContactUser

By running the above code, we’ve created ContactUser and granted them the SELECT permission on the Membership table. We’ve then gone a step further by granting the UNMASK permission, but specifically and only for the FirstName, Phone, and Email columns. This allows ContactUser to view these normally masked columns in their unmasked state, while the DiscountCode remains masked, adhering to the principle of least privilege.

Let’s see what happens when ContactUser accesses the Membership table, particularly focusing on the columns for which they’ve been granted UNMASK permissions:

-- Impersonate ContactUser to query the Membership table
EXECUTE AS USER = 'ContactUser'
SELECT * FROM Membership
REVERT 

By executing the code above, you’ll notice that ContactUser can view the FirstName, Phone, and Email columns without any masking, thanks to the granular UNMASK permissions that have been explicitly granted for these columns. However, the DiscountCode remains masked, with its values randomized between 1 and 100, demonstrating the effect of the random() masking function. This behavior aligns perfectly with our intent for ContactUser, allowing them access to the necessary contact information while keeping other sensitive data, like discount codes, masked. Run the code multiple times to observe the dynamic masking in action for the DiscountCode column.

Granular DDM permissions can also be granted at the schema and table level. For example:

-- View unmasked data in all columns of all tables in the dbo schema
GRANT UNMASK ON SCHEMA::dbo TO SomeUser

-- View unmasked data in all columns of the Membership table in the dbo schema
GRANT UNMASK ON dbo.Membership TO SomeUser

This new ability in SQL Server 2022 significantly enhances the usefulness of Dynamic Data Masking in SQL Server 2022. Happy coding!