Azure SQL Database — dynamic data masking

When you want to restrict an access to an object like a column. Sometimes a better idea is to mask data and keep the column available. Azure accommodates the expectations and has implemented this mechanism.

Go to the Dynamic Data Masking option. On the right you can see an Recommended fields to mask section and add a chosen by you -> an Add mask button. At the top, there are Masking rules containing selected fields with a masking function applied.

Let’s add a new field.

First example shows how to mask an integer value using a random number.

The second one. How to mask a string value. Now we use a default value which is an xxx in this case.

Select a Custom string option. Where you can set an exposed prefix, a suffix and a padding string.

After an end user is connected to a database, non-administrator accounts see masked data. An administrator privileged user can always look at the unmasked data.

We can exclude some users from a data masking policy in the portal.

We can do this through T-SQL as well. here is an example.

GRANT UNMASK TO [michalmolka1@example.onmicrosoft.com];

Or revoke the granted policy.

REVOKE UNMASK TO [michalmolka1@example.onmicrosoft.com];