For an instructor lead, in-depth look at learning SQL click below.
In today’s digital world, data security is paramount. It is a necessity to restrict unauthorized access to your sensitive data and ensure only needed data is exposed to users. One way to achieve this is by using SQL Server’s feature, ‘Dynamic Data Masking.’ It is a tool designed to limit exposure of sensitive data by masking it to non-privileged users.
What is Dynamic Data Masking (DDM)?
Dynamic Data Masking (DDM) is a security feature introduced in SQL Server 2016 that limits sensitive data exposure by masking the data to a non-privileged user. In simpler terms, DDM changes the content of the data in a way that it remains useful but not comprehensible, without changing data on disk.
Implementing Dynamic Data Masking
Let us dive into the SQL Server to understand how to implement Dynamic Data Masking. We start by creating a sample database and a table.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE DATABASE TestDB; USE TestDB; CREATE TABLE Employee ( Id INT NOT NULL, Name VARCHAR(100) NOT NULL, Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NULL, Password VARCHAR(100) MASKED WITH (FUNCTION = 'partial(1,"XXXX",0,2)') NULL, PRIMARY KEY (Id) ); |
The above piece of code will create a new database named ‘TestDB’ and a table called ‘Employee’. In this table, ‘Email’ and ‘Password’ are the two columns where we applied DDM. Here, the ’email()’ function will mask the Email column and the ‘partial()’ function will mask the password column.
Inserting Data
Now that we have the table ready, let’s insert some data:
|
1 2 3 4 5 |
INSERT INTO Employee VALUES (1, 'John Doe', <a href="mailto:'johndoe@email.com'" >'johndoe@email.com'</a>, 'admin'), (2, 'Jane Doe', <a href="mailto:'janedoe@email.com'" >'janedoe@email.com'</a>, 'password'); |
Once you’ve added data, the DDM feature gets activated. If a non-privileged user tries to access this data, they will only see the masked values, therefore, preventing unauthorized access to sensitive data.
Selecting Data
Now, let’s run a SELECT statement to see how a non-privileged user would see the data:
|
1 2 3 |
SELECT * FROM Employee; |
The fetched data will be along the lines of the masked functions applied, as below:
1, ‘John Doe’, ‘j*****@email.com’, ‘a****’
2, ‘Jane Doe’, ‘j*****@email.com’, ‘p****’
Conclusion
With increasing data flows and stringent regulatory norms concerning data, SQL Server’s Dynamic Data Masking is a handy feature for companies dealing with sensitive data. It not just reduces the risk of data exposure but also ensures data security to a great extent. Now that you are aware of this feature and know how to implement it, make sure to use it and keep your sensitive data safe.