This post is the continuation of Azure SQL security recommendations. In the earlier posts, we visited the concept of Row-level security for limiting user access to related data. Today, we will look at how to mask sensitive data using Dynamic Data Masking feature.

 

Dynamic Data Masking (DDM) abstracts sensitive data from non-privileged users. Since DDM is applied at query execution time, we can restrict data exposure to application layer with minimal impact on the underlying data source. There are multiple masking options available such as default value, partial and random number. This feature is currently available in SQL-Server 2016 (from 13.x) and Azure SQL Database. This feature is yet to be released for Azure SQL Data Warehouse and Parallel Data Warehouse.

Here are functions available in Dynamic Data Masking:

FunctionDescriptionSnippets
DefaultAutomatically masks the values with regards to the datatype

For string, it is usually masked by XXXX.

For decimal, the default value is 0

For Datetime, the default value is low date (1900-01-01 00:00:00.000000)

For binary datatypes (binary, varbinary, Image), the default values are ASCII values of 0

[salary] [decimal](10,2) MASKEDWITH (FUNCTION= ‘default()’)NULL
EmailThis function is just dedicated to mask email data. It exposes the first letter and the email suffix (.com, .in)[email] [varchar](50)MASKEDWITH (FUNCTION= ’email()’)NULL
RandomAssign a random number as a masked value for any numeric type column[salary] [decimal](10,2) MASKEDWITH (FUNCTION= ‘random()’)NULL
Custom StringAllows custom masking option by giving the first, last and padding letter as parameter. (prefix, padding, suffix)[phonenumber] [varchar](18)MASKEDWITH (FUNCTION= ‘partial(1, “XXXX”, 1)’)NULL

 

One of the common use cases of DDM is to mask phone numbers, email addresses, salary, bank details, etc. Unauthorized individuals having access to such privileged data could result in the misuse of your personal information. This is where DDM could be your savior.

 

Here I have created an employee table as seen below and masked salary, phone number and email columns:

dynamic-data-masking

 

User, who has “unmask” privileges, would see the actual data as below

dynamic-data-masking

 

We have now created “test” user and granted them with select privileges on the employee table. So, when this user queries the same table he would see the data as:

dynamic-data-masking

 

Even if you select this data into another table or export into a file, the result will always be masked. So, any application which would be accessing the employee table will always see the masked data, unless the user has the “unmask” privilege. You can grant and revoke access to users as shown below:

dynamic-data-masking

 

Even though sensitive data is masked, you will be able to apply where clause on the masked columns. For example, if you query for records has an email as ‘harry@hogwarts.com’, you will get the following result set:

dynamic-data-masking

 

This feature could be used by an unprivileged user to derive sensitive data using brute force methods. For instance, you could derive the employee salary by running queries as seen below:

dynamic-data-masking

 

This shows that DDM is vulnerable to users with malicious intent trying to access underlying data. So, it is important to properly manage access and only provide access to vetted individuals. Also, it is advisable to enable database auditing to identify such illicit access to data.

With this, I am concluding my Azure Security recommendations. I plan to keep writing on security recommendations in my future blogs. Stay tuned!

 

Click here to take a look at our Key Microsoft Analytics Offerings.

Subscribe to our Newsletter

5920 Windhaven Pkwy
Plano TX 75093.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com