Preventing Nonadministrative Users from Seeing Full Email Addresses in Azure Synapse | Implementing an Azure Data Solution

How to Mask Email Addresses in Azure Synapse's SQL Pool

Prev Question Next Question

Question

You have a SQL pool in Azure Synapse that contains a table named dbo.Customers. The table contains a column name Email.

You need to prevent nonadministrative users from seeing the full email addresses in the Email column. The users must see values in a format of aXXX@XXXX.com instead.

What should you do?

Answers

Explanations

Click on the arrows to vote for the correct answer

A. B. C. D.

B

https://docs.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview

The correct answer is D. From the Azure portal, set a sensitivity classification of Confidential for the Email column.

Explanation: Azure Synapse Analytics provides a feature called Sensitivity Classification that allows you to label columns in your databases with a sensitivity label. Sensitivity labels define the level of sensitivity of the information contained within a column, allowing you to control access to it.

To prevent non-administrative users from seeing the full email addresses in the Email column, you can set a sensitivity classification of Confidential for that column. This will ensure that only users who have been granted explicit permissions can view the full email addresses.

Setting a mask on the column or granting select permission to all the columns except Email will not prevent users from seeing the full email addresses.

To set a sensitivity classification of Confidential for the Email column, follow these steps:

  1. Open the Azure Synapse Analytics workspace in the Azure portal.
  2. Navigate to the SQL pool that contains the dbo.Customers table.
  3. Open the SQL script editor and run the following SQL statement to create a sensitivity label named "Confidential":

CREATE SENSITIVITY CLASSIFICATION LABEL 'Confidential' WITH (NAME = 'Confidential', INFORMATION_TYPE = 'PII', RANK = 'High')

  1. Next, run the following SQL statement to apply the Confidential label to the Email column:

ALTER TABLE dbo.Customers ADD SENSITIVITY CLASSIFICATION TO COLUMN Email WITH (LABEL = 'Confidential')

After completing these steps, only users with explicit permissions will be able to view the full email addresses in the Email column. All other users will see the masked values in the format of aXXX@XXXX.com.

Prev Question Next Question