Implementing Data Masking for Azure SQL Database | Exam DP-200 Solution

Implementing Data Masking for Azure SQL Database

Prev Question Next Question

Question

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have an Azure SQL database named DB1 that contains a table named Table1. Table1 has a field named Customer_ID that is varchar(22).

You need to implement masking for the Customer_ID field to meet the following requirements:

-> The first two prefix characters must be exposed.

-> The last four suffix characters must be exposed.

-> All other characters must be masked.

Solution: You implement data masking and use a custom string function mask.

Does this meet the goal?

Answers

A. Yes

B. No

Show Answer

Explanations

Click on the arrows to vote for the correct answer

A. B.

Must use Custom Text data masking, which exposes the first and last characters and adds a custom padding string in the middle.

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

The proposed solution of implementing data masking and using a custom string function mask can meet the stated goal of exposing the first two prefix characters and the last four suffix characters of the Customer_ID field while masking all other characters.

Azure SQL Database provides a data masking feature that allows you to limit sensitive data exposure by obfuscating the data. Data masking does not modify the actual data stored in the database but instead creates a masked copy of the data that is returned to users who do not have permission to see the actual data. Data masking can be used to hide sensitive data, such as social security numbers, credit card numbers, and other types of personal or confidential information.

To implement masking for the Customer_ID field as per the requirements stated, a custom string function mask can be used. The function can be created using Transact-SQL and applied to the Customer_ID field. The function can be designed to return a masked string that exposes the first two prefix characters and the last four suffix characters while masking all other characters.

For example, the custom string function mask can be implemented as follows:

less
CREATE FUNCTION dbo.MaskCustomerID (@CustomerID varchar(22)) RETURNS varchar(22) WITH SCHEMABINDING AS BEGIN     RETURN LEFT(@CustomerID, 2) + REPLICATE('*', 16) + RIGHT(@CustomerID, 4) END;

The function takes the Customer_ID field as input and returns a masked string that exposes the first two prefix characters and the last four suffix characters while masking all other characters with an asterisk (*) symbol.

To apply the masking to the Customer_ID field in Table1, the following Transact-SQL statement can be used:

sql
ALTER TABLE Table1 ALTER COLUMN Customer_ID varchar(22) MASKED WITH (FUNCTION = dbo.MaskCustomerID)

This statement alters the column definition of the Customer_ID field to apply the masking function dbo.MaskCustomerID to the field.

Therefore, the proposed solution of implementing data masking and using a custom string function mask can meet the stated goal of exposing the first two prefix characters and the last four suffix characters of the Customer_ID field while masking all other characters. The answer is A. Yes.

Prev Question Next Question