What is Dynamic Data Masking and how do we enable it in SQL Server
Certifications:
- How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
- How do we find what permissions a user has on an Oracle database - 1 November 2024
- How we enable Unified Auditing in Oracle Database - 7 October 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.
