{"id":3084,"date":"2021-03-29T08:00:00","date_gmt":"2021-03-29T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3084"},"modified":"2025-01-15T14:35:27","modified_gmt":"2025-01-15T11:35:27","slug":"ti-einai-dynamic-data-masking-kai-pos-to-energopoioyme","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/ti-einai-dynamic-data-masking-kai-pos-to-energopoioyme\/","title":{"rendered":"What is Dynamic Data Masking and how do we enable it in SQL Server"},"content":{"rendered":"

In this article we will analyze what it is Dynamic Data Masking<\/strong> and what options do we have for its use in SQL Server<\/strong>.<\/p>\n\n\n\n

Dynamic Data Masking is a function whose purpose is to hide sensitive data from the results of a query<\/strong>.<\/p>\n\n\n\n

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 <\/strong><\/em>but it prevents users who shouldn't have access to that data from seeing it.<\/p>\n\n\n\n

To activate it we will have to run Data Definition Language<\/strong> statements that will modify each field of the table by adding the corresponding mask that will hide them.<\/p>\n\n\n\n

Depending on the mask we use we can hide all characters or some.<\/p>\n\n\n\n

We will see detailed examples with the options we have below.<\/p>\n\n\n\n

The example<\/h2>\n\n\n\n

For our example we will make a table with four customers. We will also create a user that will have right select<\/strong> in this table.<\/p>\n\n\n\n

create table pelates(\n\tid int identity(1,1),\n\tonoma varchar(100),\n\tepitheto varchar(100),\n\ttilefono varchar(100),\n\temail varchar(100));\n\n\ninsert into pelates values\n('Stratos','Matzouranis','2101234567','matzouranis@dokimi.com'),\n('Iwanna','Iwannidou','2109852157','iwannidou@dokimi.com'),\n('Giwrgos','Gewrgiou','2251011111','gewrgiou@dokimi.com'),\n('Laertis','Rwmanos','2310124598','rwmanos@dokimi.com');\n\n\ncreate user \"xristis\" without login;\ngrant select on pelates to \"xristis\";<\/pre>\n\n\n\n
Since we have made them if we do select <\/strong><\/em>the table as this user, we will see that it sees all the data normally.<\/p>\n\n\n\n
execute as user = 'xristis';\nselect * from pelates;\nrevert;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The options of masking rules<\/h2>\n\n\n\n
As we mentioned before there are many different masking functions, let's see some examples for our case applying masking to all fields.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Email masking<\/h3>\n\n\n\n
With the email masking <\/strong>only the first character and the domain suffix are displayed.<\/p>\n\n\n\n
alter table pelates\nalter column email add masked with (function='email()');<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Default masking<\/h3>\n\n\n\n
With the default masking<\/strong> all characters in the field are hidden. <\/p>\n\n\n\n
alter table pelates\nalter column tilefono add masked with (function='default()');<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Partial masking<\/h3>\n\n\n\n
By using it partial masking<\/strong> we can choose how many characters will be displayed from the beginning and the end and with which character the rest will be hidden.<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
alter table pelates\nalter column epitheto add masked with (function='partial(2,\"xxxxx\",1)');<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Accordingly, if we wanted only the first character to appear and nothing else, we would run the following:<\/p>\n\n\n\n
alter table pelates\nalter column onoma add masked with (function='partial(1,\"xxxx\",0)');\n<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Random masking<\/h3>\n\n\n\n
With the random masking<\/strong> 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.<\/p>\n\n\n\n
alter table pelates\nalter column id add masked with (function='random(1,99999)');<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
What to watch out for in Dynamic Data Masking<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
We should know that if a user who does not have right goes unmask<\/em><\/strong> to look for records that meet some criteria will show them, simple results will be masked.<\/p>\n\n\n\n
Because of this, a user can guess field values and find out the number of records that meet them.<\/p>\n\n\n\n
execute as user = 'xristis';\nselect * from pelates where onoma = 'Stratos'\nrevert;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
How do we give the right to a user to see the data<\/h2>\n\n\n\n
By right unmask <\/em><\/strong>we can give some user can see the real data.<\/p>\n\n\n\n
grant unmask to \"xristis\";\n--revoke unmask to \"xristis\";<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
How to remove Dynamic Data Masking<\/h2>\n\n\n\n
If e.g. we would like to remove the masking from the email column we should drop the mask as below:<\/p>\n\n\n\n
alter table pelates\nalter column email drop masked;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Finally, in order to be able to add or remove a mask from a field, one must have the right ALTER ANY MASK<\/strong>.<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n