Introduction to Dynamic Data Masking

Given the abundance of data we deal with day to day its increasingly likely that we’ll find ourselves storing items of a more sensitive nature. We may only deal with details sensitive about our business such as suppliers or profitability, but when that increases – for example customer details – then we want to ensure appropriate measures are being applied to protect that data.

One of the tools available within SQL Server (2016 and above) to help deal with sensitive data is Dynamic Data Masking. This can help to prevent unauthorised access to data being stored by masking the details which are presented to the person or application consuming the data.

Creating a data mask

Enough of the blurb, lets see how we can go about creating one of these masks. As its part of the database schema we have two ways to create one – as part of the table / field creation, or if we’ve already got an existing table then we can add the mask to a column in there.

Let’s start by creating a new table with some data masked in there, we’ll just store some names for now:

CREATE TABLE #DataMasking (
	ID INT IDENTITY(1, 1),
	FirstName VARCHAR(20)
		MASKED WITH (FUNCTION = 'default()'),
	Surname VARCHAR(20)
);

INSERT INTO #DataMasking (
	FirstName,
	Surname)
VALUES ('Andy', 'Brownsword'),
	('John', 'Smith'),
	('Jane', 'Doe');

You’ll see we’ve got the MASKED keyword after the data type and we can define what function to use to mask the data (we’ll come back to that later). Additionally if we wanted to add the mask to an existing field then we could add that too:

ALTER TABLE #DataMasking
ALTER COLUMN Surname
	ADD MASKED WITH (FUNCTION = 'default()');

What does masked data look like

If we try to query the table now we’ll likely see the data exactly as it was entered rather than anything being masked. This is due to us being the database owner in our little sandbox:

SELECT *
FROM #DataMasking;

If we want to see what it might look like for other users then we can use the EXECUTE AS statement we discussed recently. Let’s see how it looks when we try it that way:

CREATE USER Dummy WITHOUT LOGIN;

EXECUTE AS USER = 'Dummy';

SELECT *
FROM #DataMasking;

REVERT;

Great, exactly as we wanted and folks can’t go and look at our sensitive data.

Unmasking data

What happens when there’s someone who needs to look at this sensitive data? We wouldn’t want to be storing it just for the fun of it, there should be a reason to hold sensitive data. Maybe its a certain user or team who can access that data or maybe the access needs to only be available for specific time periods such as audit investigations.

We can grant the permission to unmask the data to a particular user or group with the following:

GRANT UNMASK TO Dummy;

EXECUTE AS USER = 'Dummy';

SELECT *
FROM #DataMasking;

REVERT;

In a similar vein we can also revoke the ability for the data to be unmasked too:

REVOKE UNMASK TO Dummy;

The ability to grant the unmask privilege to particular users or groups can be useful when the data is being accessed via a reporting tool and depending on the user querying the data will change the results of the report just for them without any extra legwork or logic in the presentation layer.

Note that this will need the authentication for that user to be passed through to the database for it to control the masking and unmasking of the data.

What about …

So now that we’ve seen how the data looks from the masked and unmasked perspective it’s worth considering what about…

What about when we insert data into that field? – well that’s not an issue, it’ll be stored as inserted (regardless of unmask permissions) and when being retrieved will only show the masked version.

What about if someone runs an Import/Export job from that table? – well the data will be exported according to their permissions. If they have unmasked access to the data then it’ll be taken in that format, and if they don’t then the process will copy the xxxx data instead (this is also the case with SELECT INTO and INSERT INTO).

Wrap up

Here we’ve covered some basics of the data masking – what it is, how to implement it, what it looks like, and how the data can be unmasked too.

This can be a very useful feature to help secure your sensitive data when coupled with other security features as it can have some drawback on its own (to be discussed later).

As always you can find more details in the official documentation.