For an instructor lead, in-depth look at learning SQL click below.
Data masking is a method used to obscure sensitive data within your database by replacing it with obfuscated and untraceable information. This way, users with access may get the information they need, but not understand the sensitive values behind it. SQL Server introduced a feature, known as Dynamic Data Masking, to provide an infrastructure for this practice.
Setting up Data Masks
To apply data masking on your database, the first step is to define masking rules. Let’s take an example of an Employees table, where we want to mask the employees’ personal Email ID and Phone Numbers.
|
1 2 3 4 5 |
ALTER TABLE Employees ADD MASKED WITH (FUNCTION = 'email()') FOR Email, ADD MASKED WITH (FUNCTION = 'partial(1,"xxxxxx",0)') FOR PhoneNumber; |
This command applies data masking to the specified columns. The ’email()’ function replaces the email characters with ‘xxxx@xxxxx.com’ while the ‘partial’ function replaces the phone number with ‘x’s except for the last 4 digits.
Unmasking Permission
SQL Server also allows us to grant ‘UNMASK’ privilege to a particular user. This way, when sm1User queries the table, they see the actual data, not the masked data.
|
1 2 3 |
GRANT UNMASK TO sm1User; |
Observing Data Masking
Now, when a user with UNMASK privilege retrieves data, they will see the actual data. However, if a user lacks the UNMASK privilege, SQL Server will replace the sensitive data with masked data.
|
1 2 3 4 5 6 7 |
-- Query to see data by User with UNMASK privilege SELECT Email, PhoneNumber FROM Employees; -- Query to see data by User without UNMASK privilege SELECT Email, PhoneNumber FROM Employees; |
In these examples, the first User will see the original sensitive data. However, the second User (without UNMASK access) will observe masked data, ensuring data security.
Conclusion
SQL Server’s Dynamic Data Masking feature is a valuable tool for maintaining data security and privacy in any organization. It’s simple to implement and flexible, allowing for tailored data visibility based on user access levels. As always, exercise caution in granting UNMASK privileges to ensure data sensitivity is maintained.