What is Dynamic Data Masking and how do we enable it in SQL Server

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

In this article we will analyze what it is Dynamic Data Masking and what options do we have for its use in SQL Server.

Dynamic Data Masking is a function whose purpose is to hide sensitive data from the results of a query.

It does not require the change in the application code and does not modify the data stored in the database, which means that it is not an encryption solution but it prevents users who shouldn't have access to that data from seeing it.

To activate it we will have to run Data Definition Language statements that will modify each field of the table by adding the corresponding mask that will hide them.

Depending on the mask we use we can hide all characters or some.

We will see detailed examples with the options we have below.

The example

For our example we will make a table with four customers. We will also create a user that will have right select in this table.

create table pelates(
	id int identity(1,1),
	onoma varchar(100),
	epitheto varchar(100),
	tilefono varchar(100),
	email varchar(100));


insert into pelates values
('Stratos','Matzouranis','2101234567','matzouranis@dokimi.com'),
('Iwanna','Iwannidou','2109852157','iwannidou@dokimi.com'),
('Giwrgos','Gewrgiou','2251011111','gewrgiou@dokimi.com'),
('Laertis','Rwmanos','2310124598','rwmanos@dokimi.com');


create user "xristis" without login;
grant select on pelates to "xristis";

Since we have made them if we do select the table as this user, we will see that it sees all the data normally.

execute as user = 'xristis';
select * from pelates;
revert;

The options of masking rules

As we mentioned before there are many different masking functions, let's see some examples for our case applying masking to all fields.

Email masking

With the email masking only the first character and the domain suffix are displayed.

alter table pelates
alter column email add masked with (function='email()');

Default masking

With the default masking all characters in the field are hidden.

alter table pelates
alter column tilefono add masked with (function='default()');

Partial masking

By using it partial masking we can choose how many characters will be displayed from the beginning and the end and with which character the rest will be hidden.

Let's say that in the last name we want to display the first two characters and the last, while in between there are "xxxx" in place of the real elements, then in this case we would have to run the following:

alter table pelates
alter column epitheto add masked with (function='partial(2,"xxxxx",1)');

Accordingly, if we wanted only the first character to appear and nothing else, we would run the following:

alter table pelates
alter column onoma add masked with (function='partial(1,"xxxx",0)');

Random masking

With the random masking we set a random number to appear in place of the real one. In order for it to work, we must have defined the limits it will have, e.g. from the number 1 to 99,999.

alter table pelates
alter column id add masked with (function='random(1,99999)');

What to watch out for in Dynamic Data Masking

Since, as we said, Dynamic Data Masking is applied to the results of the data and not to the actual data, there are some limitations.

We should know that if a user who does not have right goes unmask to look for records that meet some criteria will show them, simple results will be masked.

Because of this, a user can guess field values and find out the number of records that meet them.

execute as user = 'xristis';
select * from pelates where onoma = 'Stratos'
revert;

How do we give the right to a user to see the data

By right unmask we can give some user can see the real data.

grant unmask to "xristis";
--revoke unmask to "xristis";

How to remove Dynamic Data Masking

If e.g. we would like to remove the masking from the email column we should drop the mask as below:

alter table pelates
alter column email drop masked;

Finally, in order to be able to add or remove a mask from a field, one must have the right ALTER ANY MASK.

Sources:

• Dynamic Data Masking