Security to data visibility with Dynamic Data Masking

When I was talking with a recent customer around how they can use some of the new capabilities that are getting introduced with SQL Server 2016, they suddenly got interested in evaluating the feature around Dynamic Data Masking. I showed some quick demo and this was getting some interesting use cases for their product which was around a CRM implementation they had. This was the first time I saw a real customer getting excited about this security feature.

I told at the moment (as I write this blog), the SQL Server 2016 was on RC3 and could release anytime soon but was feature complete and they must try to test the application using this feature for their specific use case. After couple of days, I got an email from one of their developer that they were using Data Masking but were not able to see the masked data. They were clueless on how to play around with permissions for the same. I wrote to them a reply which I thought was worth to share via blog post too.

Understanding Permission with Data Masking

Let me double click on the basics of permission involving Data Masking. You do not need any special permission to create a table with a dynamic data mask, only the standard CREATE TABLE and ALTER on schema permissions. Adding, replacing, or removing the mask of a column, requires the ALTER ANY MASK permission and ALTER permission on the table. It is appropriate to grant ALTER ANY MASK to a security administrator.

Users with SELECT permission on a table can view the table data. Columns that are defined as masked, will display the masked data. Grant the UNMASK permission to a user to enable them to retrieve unmasked data from the columns for which masking is defined.

The CONTROL permission on the database includes both the ALTER ANY MASK and UNMASK permission. In the above scenario the need was to give the perfect permissions.

Use the sys.masked_columns view to query for table-columns that have a masking function applied to them. This view inherits from the sys.columns view. It returns all columns in the sys.columns view, plus the is_masked and masking_function columns, indicating if the column is masked, and if so, what masking function is defined. This view only shows the columns on which there is a masking function applied.

SELECT mc.name, tbl.name as table_name, mc.is_masked, mc.masking_function
FROM sys.masked_columns AS mc
JOIN sys.tables AS tbl 
ON mc.[object_id] = tbl.[object_id]
WHERE is_masked = 1;

At this moment I wanted to add, a masking rule cannot be defined for the following column types:

• Encrypted columns (Always Encrypted)
• FILESTREAM
• COLUMN_SET

Granting Permissions to View Unmasked Data

Granting the UNMASK permission allows Users to see the data unmasked. Below is an example were I have given UNMASKING rights to a user (AppUser) and then we look at an table data and later I have removed the same.

GRANT UNMASK TO AppUser;
EXECUTE AS USER = 'AppUser';
SELECT * FROM tbt_with_Masking;
REVERT; 

-- Removing the UNMASK permission
REVOKE UNMASK TO AppUser;

As for the earlier user query, I had to explain to them some basics. Some users like dbo user, sysadmin user (and some other user roles) have implied permissions to see the data unmasked. These users always have the UNMASK permission, which cannot be taken away from them. So they had to create a user with minimal permissions to connect from Application. But this is an important and yet compelling thing to know about working with Data Masking.

Do let me know if you are excited of using Data Masking in your applications? What are some of these use cases for the same? Would love to learn from you too.