Dynamic data mask is a very interesting security feature allowing us to mask critical fields such as e-mail, phone number, credit card and so on. We can decide what users will be able to see the value of these features or not.
This feature faced many flaws when it was released, but I believe it’s stable now, although It’s not the main security feature you should care about, it can still be very useful.
However, until very recently, this feature was not very useful. If you mask many fields in many different tables, the fields may require different permission levels in order to be unmasked.
The only permission control provided for Mask and Unmask fields where this: See everything masked or everything unmasked. In this way, the feature was not useful, because there is not doubt that phone number and credit card should have different permission levels.
Finally, the granular permission control for Data Mask is available in Azure SQL Databases. The feature became way more useful and with so many news around these weeks, not many people noticed.
Test Environment
Let’s test this feature using the AdventureWorksLT. We can provision this database in Azure SQL by choosing to provision a sample database.
Applying the Data Mask
alter column EmailAddress nvarchar(50)
masked with (function=’email()’)
alter column Phone nvarchar(25)
masked with(function=‘partial(3,”XXXXXXXXX”,0)’)
Create Roles to control Mask Permissions
Control permissions in a field level is something complex. A good practice to do this is using database roles. Let’s create database roles for this and set the permission of these roles as data reader to make the example easier.
Grant the unmask peremission
On our example, we will grant the unmask permission over each field for the different database roles. The statements will be like this:
go
Grant UnMask on SalesLT.Customer(EmailAddress) to Emailview
go
The granular unmask permission also would allow to grant permission schema level:
Or also on table level:
Anyway, now making the data mask feature really useful.
Test the users and UnMask Feature
This first user can read the phone, but not the e-mail:
This 2nd user can read the e-mail but not the phone:
Conclusion
We have a new powerful security feature on our hands to work with and I hope this feature to be in SQL Server 2022 as well
Load comments