SQL Server, how to improve privacy (and be more gdpr complaiant) at no cost with the "dynamic data masking" feature

Hi guys,

Welcome back mates! 

Today we will talk about privacy because, at least here in Europe, GDPR laws are not a joke.

For this reason I decided to tell you how to be more compliant using a SQL Server function, all at no cost.

We will use the dynamic data masking (DDM) feature introduced with the SQL server 2016 edition.

Let's see today how to use it in practice!

The dynamic data masking feature

The dinamic data masked is a feature that allows you to mask data that we consider sensitive.
By performing a select on the database, the columns we have chosen, will be shown in a masked way.

Personally, I strongly recommend it to all companies that develop software.

Let's see how it works!

Well, this time for the demo we will use a localized demo databases available for SAP Business One, its name is SBODemoUS.

From this database we read the AADM table by extracting a few fields, a couple of which we think are sensitive.

The T-SQL command we run is this:

SELECT DISTINCT [COMPNYNAME],[COMPNYADDR],[COUNTRY],[PHONE1],[E_MAIL] FROM AADM

While this is the result obtained

In our opinion, the two fields telephone and email are sensitive fields and we would not want them to be viewed by those who do not have the right already querying the database.
 

How to do it?

First of all we need to create a new sql server user. we call it UserWithMaskedData:

USE SBODemoUS

CREATE USER UserWithMaskedData WITHOUT Login;

Then we give to this new user the permit to execute a select command:

GRANT SELECT ON OBJECT::dbo.AADM TO UserWithMaskedData;

Now it is the time to specify wich fields mask.

The field Phone1 will be masked with a function called default, while the field E_Mail will be masked with another function called Email:

ALTER TABLE AADM ALTER COLUMN [Phone1] varchar(20) MASKED WITH (FUNCTION = 'default()');  
ALTER TABLE AADM ALTER COLUMN [E_Mail] varchar(20) MASKED WITH (FUNCTION = 'Email()');

We already done! 

In fact, if we execute the select on the AADM table...

EXECUTE AS USER = 'UserWithMaskedData';
SELECT DISTINCT [CompnyName],[CompnyAddr],[Country],[Phone1],[E_Mail] from AADM
REVERT;

We see that the indicated fields are really masked:

Wow! Easy and clear!

 

Of course you can also the mask  with the command:

GRANT UNMASK TO UserWithMaskedData

And you can also apply different mask type functions... but have fun discovering them!
 

 

That's all for today guys, i wish a great weekend ahead! 

 

 

 

 

 Affiliated with VMWARE take alook to the special offers

 

Next post:

Previous post: SQL Server, SQL Server, fast data reading with table partitioning! ...Vamonos!