For an instructor lead, in-depth look at learning SQL click below.
With the increasing level of data breaches, ensuring sensitive data’s security has become a concern in most businesses. This is even more significant when dealing with databases handling Personally Identifiable Information (PII). One of the effective ways offering this level of data protection is Data Masking. In this post, we delve into how to implement data masking using SQL server.
What is Data Masking?
Data Masking is a process that obscures specific data within a database table to ensure that the data remains confidential. This is especially useful when sharing data with third parties or even within different departments within the same organization. With data masking, one can protect sensitive data while still being able to use the database for various operations such as testing and analysis.
SQL Server Dynamic Data Masking
SQL Server supports Dynamic Data Masking (DDM) feature. DDM changes the way data appears to the users but it does not change underlying stored data. To users without the UNMASK privilege, the data in the table is obscured. For users with the UNMASK privilege, the actual data is visible as it is stored in the table.
Implementing Data Masking
Let us take an example of a ‘Users’ table, which contains sensitive fields like Email and Phone Number. We would like to mask these fields whenever the table is queried.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Users( UserID int IDENTITY PRIMARY KEY, UserName nvarchar(50) NOT NULL, Email nvarchar(100) MASKED WITH (FUNCTION = 'email()') NULL, PhoneNumber nvarchar(12) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL ) |
In the above SQL code, we have specified the masking function for Email as ’email()’, which will replace email addresses with ‘XXXX@XXXX.COM’. The PhoneNumber function ‘partial(1,”XXXXXXX”,0)’ replaces the phone number with “XXX-XXX-XXXX”.
Viewing Masked Data
Let’s add some data to the Users table:
|
1 2 3 4 |
INSERT INTO Users(UserName, Email, PhoneNumber) VALUES ('John Doe', <a href="mailto:'john.doe@example.com'" >'john.doe@example.com'</a>, '123-456-7890') |
If you query the Users table without the UNMASK privilege, here’s how the result looks like:
|
1 2 3 |
SELECT * FROM Users |
| UserID | UserName | PhoneNumber | |
|---|---|---|---|
| 1 | John Doe | XXXX@XXXX.COM | XXX-XXX-XXXX |
As you can see, the original sensitive data is shielded from unauthorized users.
Conclusion
SQL Server Dynamic Data Masking is a powerful feature that helps protect the most sensitive data in your database. Proper application of DDM goes a long way, ensuring compliance with privacy laws, and helping instill trust with your users in the way their personal information is handled.
It’s important to remember, however, that while data masking helps improve security, it should not be the only defense mechanism. A robust approach to data security should also involve encryption, user access control, and constant monitoring against potential security threats.