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:
|Default||Automatically 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|
|This 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|
|Random||Assign a random number as a masked value for any numeric type column||[salary] [decimal](10,2) MASKEDWITH (FUNCTION= ‘random()’)NULL|
|Custom String||Allows 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:
User, who has “unmask” privileges, would see the actual data as below
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:
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:
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 ‘firstname.lastname@example.org’, you will get the following result set:
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:
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.