Security of your company’s data is crucial and is a concern when you are migrating from on-premises to cloud-based solutions. Azure SQL provides a great deal of effective and exceptional security capabilities for protecting your sensitive data. It includes transparent Data Encryption, Dynamic Data Masking, Always Encrypted, Auditing, Data Discovery and Classification, Advanced Threat Protection, vulnerability assessment, authentication mechanisms, IP firewall rules, etc.
Of all these, we are going to discuss the significant security features:
Let us focus on the Dynamic Data Masking feature in this blog and explore other options in the upcoming blogs.
Business use case
Consider a scenario where you need to prevent unauthorized access to data resulting from a query. For example, your organization’s finance department might need employee’s salary information, credit card information, but other departments need not access this information. What is needed is the ability to mask the salary and credit card columns for those departments who have access to the employee table and provide unmask permission only to the finance department. Dynamic Data Masking allows implementing this without changing the actual data in the Azure SQL Database/Azure Data Warehouse/Azure Synapse Analytics.
Dynamic Data Masking
The Dynamic Data Masking feature is used to protect sensitive data from unauthorized users. By default, database administrators have permissions to view the masked data in the unmasked state. So, you must use this only if administrators can access them. It masks data and provides them in the masked form only at query execution time based on who is executing the query. The data at rest remains unaffected by masking. Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics all support Dynamic Data Masking.
To implement, open the Microsoft Azure SQL portal, select the required database, and under the security section, select Dynamic Data Masking, and the following screen appears.
It provides you a list of recommended sensitive fields to mask. You can click Add mask to those fields or any fields of your choice. Also, you can add SQL users to be excluded from masking besides the administrators.
The mask functions available are listed below. They are based on the data type of column.
Add the finance department to the exclusion list, as discussed before. By doing this, other people get masked salary data on querying except the finance people. The below result is displayed on querying the table as an unauthorized user, which has masked data for dynamic data masked columns.
Transact-SQL also allows to implement Dynamic Data Masking– refer to this to learn more. Please note that Azure Synapse Analytics or SQL Managed Instance does not allow to set this feature using the portal. So, in those cases, you may use PowerShell or REST APIs to configure data masking.
Dynamic Data Masking is a great feature to secure the data when you would like to restrict certain groups of users from accessing sensitive columns in data. Other security features such as transparent data encryption and Always Encrypted in Azure SQL will help secure data at rest. We will focus on Transparent Data Encryption and Always Encrypted security features in our upcoming blogs.
Learn more about Visual BI’s Microsoft Azure offerings here.
