Dynamic Data Masking: Masking the sensitive data from users

Dynamic data masking (DDM) limits sensitive data exposure by masking it to non-privileged users. – MS docs

Why DDM is even required: lets assume you have a table Employee with column phone number and have to provide read permission to developers but still want to hide the phone numbers from them. Previously we used to either use column level encryption or remove the data from the table altogether or create a view on top of the table without having this column and provide read permission to developers on this View. But starting from SQL Server 2016 Microsoft has introduced Dynamic Data Masking, which greatly reduces the effort in handling such issues.

How it works: We have a table Employee with below data.

Unmasked data

Let us say that we want to mask the PhoneNumber which is currently visible to everyone having read permission onto the table. To add the DDM for this column use below command.

ALTER TABLE [dbo].[Employee] 
ALTER COLUMN PhoneNumber varchar(100) MASKED WITH (FUNCTION = 'default()');  

Once masked, when we run select on the table as user ‘Developer101’ whom we haven’t provided the UNMASK permission.

EXECUTE AS USER = 'Developer101'
SELECT * FROM [dbo].[Employee]
REVERT
Masked data

The “PhoneNumber” is now not visible to the user User1. To let the user see the unmask data use below command.

GRANT UNMASK TO Developer101;  

To remove DDM from the table use below command.

ALTER TABLE [dbo].[Employee]   
ALTER COLUMN [PhoneNumber] DROP MASKED;  

Microsoft provides multiple functions to mask the data which are as below:

Different Masking functions
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2017#defining-a-dynamic-data-mask

The functions which are used the most are Default and Partial.

I hope it helps!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s