Dynamic Data Masking is a security feature in SQL Server that limits the exposure of sensitive data to non-privileged users. It masks the data by partly or fully concealing a defined set of data elements. In Microsoft Azure, you can easily configure dynamic data masking for your SQL workloads to further enhance the security in your environment.
Dynamic data masking is a policy-based security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results.
I. Understanding the types of Dynamic Data Masks
Before you configure dynamic data masking, it is important to understand the different types of masks and their functions:
- Default: For string data type, a xxxx is shown, for numeric and binary data types a zero value is exposed, and for date and time data types a zero value of the corresponding type (e.g., 1900-01-01) is revealed.
- Email: This mask exposes the first letter of an email address and the constant suffix “.com”, in the form of aXXX@XXXX.com.
- Custom string: This mask exposes the first and last letters and adds a custom padding string in the middle.
- Random: This mask replaces any numeric value with a random number within a specified range.
II. How to Configure Dynamic Data Masking
Let’s take a look at how to configure Dynamic Data Masking in an Azure SQL Database.
- Go to the Azure portal and navigate to your SQL database.
- Under the Security settings, select Dynamic Data Masking.
- Here you’ll find all recommendations for columns to mask based on Azure’s pre-built rules. To add a mask, select ‘+ Add mask’.
- Select the schema, table, column, and the type of mask and then click Add to add the mask.
Here’s an example of configuring dynamic data masking for a fictional Patients table:
USE [HospitalDatabase];
GO
CREATE TABLE Patients (
PatientID int NOT NULL,
Name varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NOT NULL,
BirthDate date MASKED WITH (FUNCTION = 'default()') NOT NULL,
Email varchar(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
MedicalRecord varbinary(MAX) MASKED WITH (FUNCTION = 'default()') NOT NULL
);
GO
In this example, the Name, BirthDate, Email, and MedicalRecord fields are masked with the partial, default, email, and default functions, respectively.
III. Summary
Dynamic data masking is an excellent way to enhance the security of your SQL workloads in Microsoft Azure. It helps prevent unauthorized access to sensitive data by masking it to non-privileged users. It’s a relatively straightforward process to configure, and Azure offers several types of masks to cater to different needs.
Remember, this feature does not prevent the data from being saved in plain text in the database, it just prevents non-privileged users from reading it. Always check that other security measures are in place to ensure database security, such as encryption, correct privileges, and access controls.
To get a better understanding of how to apply the knowledge in this article, you can consider taking up the AZ-500 Microsoft Azure Security Technologies exam that covers Dynamic Data Masking as part of its curriculum.
Practice Test
True or False: Dynamic masking in SQL limits the exposure of sensitive data in a database.
- True
- False
Answer: True
Explanation: Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to specify how much sensitive data to reveal with minimal impact on the application layer.
What does SQL Server Dynamic Data Masking do?
- a) Masks data in motion
- b) Masks data at rest
- c) Masks data in use
- d) All of the above
Answer: b) Masks data at rest
Explanation: SQL Server Dynamic Data Masking primarily obscures sensitive data in a database (at rest) while still leaving it available for applications and analytics.
True or False: Dynamic data masking in SQL is implemented with encryption.
- True
- False
Answer: False
Explanation: Dynamic data masking differs from encryption. It does not change the data in the database but only how it appears when it is retrieved or viewed.
Which of the following statement is correct regarding dynamic data masking?
- a) It helps to enhance SQL Server security
- b) It does not allow administrators to hide data
- c) It cannot be configured for specific database users
- d) It provides encryption for the data
Answer: a) It helps to enhance SQL Server security
Explanation: Dynamic Data Masking (DDM) enhances SQL Server security by allowing database administrators to hide data for non-privileged users.
Which of the following are use cases for SQL Server Dynamic Data Masking?
- a) Protecting credit card information in a retail application
- b) Masking personally identifiable information (PII) in a HR application
- c) Preventing exposure of sensitive data in a test development environment
- d) Store data in an encrypted way
Answer: a) Protecting credit card information in a retail application, b) Masking personally identifiable information (PII) in a HR application, c) Preventing exposure of sensitive data in a test development environment
Explanation: Uses of SQL Server Dynamic Data Masking include protecting sensitive data from being exposed to non-privileged users in various applications.
True or False: Dynamic data masking in Azure SQL Database allows privileged users to see unmasked data.
- True
- False
Answer: True
Explanation: Privileged users in Azure SQL Database will see the original unmasked data.
Which of the following is not a masking function in Dynamic Data Masking?
- a) Default
- b) Credit Card
- c) Email
- d) Privileged User
Answer: d) Privileged User
Explanation: Privileged User is a role, not a function. Default, Credit Card, and Email are types of masking functions in Dynamic Data Masking.
Can dynamic data masking be used along with Always Encrypted in Azure SQL Database?
- a) Yes
- b) No
Answer: a) Yes
Explanation: Always Encrypted and Dynamic Data Masking can be used together to provide a more comprehensive security solution.
True or False: Dynamic data masking policies can be set in Azure Portal or using T-SQL.
- True
- False
Answer: True
Explanation: We can set dynamic data masking policies directly in the Azure portal or by using T-SQL scripts.
Which of the following statement is incorrect about dynamic data masking?
- a) It is a passive method of preventing unauthorized access to data.
- b) It is a method of real-time data encryption.
- c) It can help keep data safe at presentation time.
- d) It does not affect database operation and data gain.
Answer: b) It is a method of real-time data encryption.
Explanation: Dynamic data masking is not a method of real-time data encryption; instead, it conceals data at the presentation level without affecting the underlying data.
Interview Questions
What is dynamic data masking in SQL workloads?
Dynamic data masking in SQL workloads is a security feature that hides sensitive data in the result set of a query over designated database fields, while the data in the database is not changed.
Where should you configure dynamic data masking in Azure?
You configure dynamic data masking in the Azure portal, under the SQL databases blade.
Who can define a dynamic data masking policy?
Only the database administrator or a user with the necessary permissions can define a dynamic data masking policy.
Do updates or inserts reveal masked data?
No, the masking is applied only in the query result set, not affecting the actual data stored, thus updates or inserts don’t reveal masked data.
What are the types of maskable data for Azure SQL Database dynamic data masking?
The types of maskable data include: Default (full masking according to the data types), Credit Card (masking digits of a credit card number), Email (masking an email address), and Custom String (masking text base on custom rules).
How does dynamic data masking address security?
Dynamic data masking reduces the exposure of sensitive data to non-privileged users, by masking the data in the result set of a query.
Is it possible to mask data for certain users only?
Yes, dynamic data masking allows you to define masking rules that apply to all users, except those that you explicitly exclude in the ‘Unmask’ column.
Can a dynamic data masking policy be turned on and off?
Yes, dynamic data masking policies can be turned on and off by users with appropriate permissions.
Are dynamic data masking policies immediately effective after creation?
Yes, the masking rules take effect immediately after the dynamic data masking policy is created or modified.
What is the purpose of the “Unmask” column when configuring Dynamic Data Masking?
The “Unmask” column allows specific users or roles to see the unmasked data, offering them full access to the sensitive information.
Can custom masking functions be created in SQL workloads?
No, the Azure Dynamic Data Masking feature does not support creating custom masking functions.
Can Dynamic Data Masking be used with On-Premises SQL Servers?
It depends on the version; Dynamic Data Masking is a feature of SQL Server 2016 and later.
Can you mask existing data on an Azure SQL Database?
Yes, you can add a masking rule to an existing column with data, the masked data is returned when non-privileged users query.
Can dynamic data masking protect against SQL injection attacks?
No, dynamic data masking is not designed to prevent SQL injection attacks. It is recommended to use other security measures in addition to dynamic data masking.
Can you use Azure dynamic data masking with other Azure products?
Yes, it can be used in combination with other Azure products like Azure Data Factory and Power BI, subject to the data source compatibility and scenarios.
extutorials.com