AZ-500 Question 178
Single answerYou are a security engineer for an organization that stores customer PII in an Azure SQL Database. The 'CustomerDetails' table contains a 'PhoneNumber' column, and you must ensure that only the last four digits of phone numbers are visible to most users, while authorized roles (such as compliance officers) can view the entire phone number. Additionally, you need to prevent non-privileged roles from bypassing the mask. Which configuration should you implement to meet these requirements?
- A
Create a User-Defined Function that replaces the first characters of the phone number with X’s, and grant SELECT permission to the support staff only on that function.
- B
Configure dynamic data masking on the 'PhoneNumber' column using a partial mask that reveals only the last four digits, and ensure that non-privileged roles do not have the UNMASK permission.
- C
Enable Azure Transparent Data Encryption (TDE) on the 'CustomerDetails' table and grant SELECT permission to authorized users while restricting it for support staff.
- D
Use Always Encrypted with a partial encryption setting to reveal only the last four digits of the phone number for non-privileged accounts, while fully decrypting for authorized roles.
Show answer and explanation
Correct answer: B
Explanation
To implement dynamic masking in Azure SQL Database, you define masking rules at the column level and manage privileges that could reveal underlying data. A partial mask shows only the configured portion of the data (e.g., last four digits). Users with UNMASK or higher-level permissions can view the unmasked data. Refer to Microsoft documentation on Dynamic Data Masking (https://learn.microsoft.com/azure/azure-sql/database/dynamic-data-masking-overview) for details on using T-SQL to configure masking and managing UNMASK privileges.
- A. Incorrect.
Incorrect. While a User-Defined Function could obscure the phone number in a custom query, it does not enforce masking at the database level for all queries. Users could still retrieve unmasked data through direct SELECT statements unless more complex security measures are applied.
- B. Correct.
Correct. Dynamic data masking in Azure SQL Database allows you to define a mask that reveals only certain parts of the data (e.g., last four digits). Ensuring non-privileged roles lack UNMASK permission prevents them from bypassing the mask. Authorized roles or accounts with UNMASK permission (or higher privileges, such as sysadmin) can see the full data.
- C. Incorrect.
Incorrect. Transparent Data Encryption (TDE) only encrypts data at rest and does not control how much data is revealed in a SELECT statement. It does not provide fine-grained masking capabilities for columns.
- D. Incorrect.
Incorrect. Always Encrypted does not support partial encryption in the sense of revealing only partial plaintext to some users. Data is either fully encrypted or fully decrypted based on key access. It is not used for partial masking scenarios.