SQL Server 2016: Dynamic Data Masking

Author by Concurrency Blog

One of the new security features in SQL Server 2016 is Dynamic Data Masking. This feature allows to you define how much data in a column users can see when they query it. It can hide sensitive data from prying eyes.

Set-up is fairly simple. You choose a table and column, determine how much of the data you want the users to see, and apply a function to mask it. If there is a group that needs to see all the data, you can apply permissions for them to do so.

I’m going to use the WideWorldImporters database. I’ll query, mask, and unmask the SupplierID column from the Purchasing.PurchaseOrders table. I’ll demonstrate how the data appears using two users – a-jes, an account with sa permissions, and PurchaseOrdersApp, a user with read/write permissions in the database.

Before Masking

I’m going to run one query:

SELECT PO.PurchaseOrderID, PO.OrderDate, PO.SupplierID, POL.PurchaseOrderLineID, POL.OrderedOuters, POL.ReceivedOuters, POL.StockItemID, POL.Description
FROM Purchasing.PurchaseOrders AS PO
	INNER JOIN Purchasing.PurchaseOrderLines AS POL ON POL.PurchaseOrderID = PO.PurchaseOrderID;

How this appears in SSMS as a-jes:

Before-SSMS-a-jes.PNG

How this appears in SSMS as PurchaseOrdersApp:

Before-SSMS-App.PNG

How this appears in an application as PurchaseOrdersApp:

Before-webapp-App.PNG

Applying a Mask

The mask gets applied by issuing an ALTER TABLE…ALTER COLUMN…ADD MASKED WITH command.

The options for masking functions are:

Function

Before mask

After mask

Default() – masks all the data

Number: 487

Text: Jes

(FUNCTION = 'default()')

Number: 000

Text: XXX

Email() – masks everything but first letter, @, and suffix

jes@email.com

(FUNCTION = 'email()')

jXX@XXXXX.com

Partial() – custom mask in which you determine how much will be shown

985-744-5238

(FUNCTION = 'partial(1,"XXXXXXX",0)')

9XXXXXXXXX

 

(FUNCTION = 'partial(3,"XXXXXXX",0)')

985XXXXXXX

Random() – mask a number within a range

487

(FUNCTION = 'random(1,1000)')

198, or perhaps 633, or maybe 1000

 

I'm applying the default mask to the SupplierID column.

ALTER TABLE Purchasing.PurchaseOrders
ALTER COLUMN SupplierID ADD MASKED WITH (FUNCTION = 'default()');

How this appears in SSMS as a-jes:

After-SSMS-a-jes.PNG

How this appears in SSMS as PurchaseOrdersApp:

After-SSMS-App.PNG

How this appears in an application as PurchaseOrdersApp:

After-webapp-App.PNG

Giving users rights to see the masked data

There may be some groups of users that should be able to view the data in an unmasked state. In that case, you can grant them permission to see unmasked data.

GRANT UNMASK TO PurchaseOrdersApp;
GO

If the user or group should go back to seeing masked data, you can revoke the permission.

REVOKE UNMASK FROM PurchaseOrdersApp;
GO

Remove data masking

At a future point, you may choose to remove data masking. This is easily accomplished with the DROP MASKED command.

ALTER TABLE Purchasing.PurchaseOrders
ALTER COLUMN SupplierID DROP MASKED;

After running that, the application shows the data.

Unmasked-webapp-App.PNG

You can query the sys.masked_columns view to determine which columns of which tables in your database are masked.

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

Protecting sensitive data

Dynamic data masking is a great tool to use in combination with other security features (such as encryption) to protect sensitive data.

Author

Concurrency Blog

The latest about Concurrency