All about Dynamic Data Masking security

If you are not aware about Dynamic Data Masking, I would recommend you to first read Dynamic Data Masking Intro, How to configure Dynamic Data Masking using Azure SQL Database portal or Using T-SQL. Reading these links will help you understand this feature better. let’s see All about Dynamic Data Masking security.

 

All about Dynamic Data Masking security

Q.) First of all let’s see who all can create \ unmask the sensitive data?

A.) Dynamic data masking can be configured by the SysAdmin (Azure Database admin), Server Admin or person who has Security Officer Roles.

 

Q.) Do we have any granular set of permissions for Dynamic Data Masking if we don’t want to provide role membership to a user?

A.) Yes, any person who has Create\Alter Table permissions can create a table with dynamic data masking. However, adding, replacing or removing the mask from a column requires Alter Table and Alter Any Mask permission.

 

Q.) Any specific permission which enables a user\login to see un-masked sensitive data?

A.) A normal user with select permission can only see masked data until and unless he has UNMASK permission. This is a granular permission. It is worth mentioning that the control permission on the database includes both Alter Any Mask & UNMASK permission.

 

Q.) Is it enough to configure Dynamic data masking on sensitive data related columns?

A.) No, this is just a method of masking the data in the result set. The actual data never gets encrypted and if your user has direct access to your database (access to connect to database using SSMS, query window SQLCMD etc.) the user can do some guess work to defeat the purpose. Hence, you must apply other kind of mechanisms also if the data is so much super critical and user has direct access.

 

Q.) Why the data is not considered to be safe if Dynamic data masking is enabled?

A.) Dynamic data masking is to represent the data but if the user has direct access & DML rights he can guess some values based on filter predicates or play around with the data by executing DML operation against these columns. You must put other measures in place to properly audit & use as much granular permissions as possible to safeguard your data.

 

Q.) Is there anything i need to be aware about before granting UNMASK permission to a user\Role?

A.) Yes, please be aware that the Unmask permission is a global permission applicable for the entire database, so once you provide that unmask permission the user will be able to unmask everything for that database. You may don’t want to do it specially when users are not using any application and have direct query control over database.

 

Q.) Give me an example where the user can easily guess the data even if dynamic data masking is enabled?

A.) Let’s say the user doesn’t have unmask permission & he is getting masked data but if he joins smart filters he can easily guess data because filters are applied even before representing the data to end user & applying mask. So if this is the data.

And the user is smart enough, user can easily put couple of filters like shows below to get an idea what is the actual value behind creditrating.

Or

 

Hope you got to know All about Dynamic Data Masking security. Happy Learning!

feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLChamp
Follow @Sarab_SQLGeek