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

localized demo databases available for SAP Business One

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!


Comments

Post a Comment

I Post più popolari

SQL Server, execution plan and the lazy spool (clearly explained)

Image
Hi Guys, Welcome back! In one of the last posts we talked about Table Spool . We had explained what Spool are for by telling the somewhat curious story of the halloween problem . Here we specifically talked about a type of spool called Eager Spool   You can look to the execution plan below where the eager Spool is used to avoid the halloween problem in classic query of the "salary increase".. i suggest Well, if the story of the eager spool intrigued you, today instead we will speak about another type of Spool, the Lazy Spool . The Lazy spool Let's take a small step back. So, what is a Spool operation?  As we have already understood a spool operation is simply a temporary storage of data . Data are read from a source table and stored inside a worktable in the Tempdb database . While an Eager Spool is used to prevent the Halloween problem, the Lazy Spool is instead used to store data that will later be needed again when running a query . So, when a Lazy spool is used?  ...
Read more

SQL Server, datetime vs. datetime2

Image
Hi guys, This time, in this post we will speak about datetime and datetime2 datatypes. We will talk about the pros and cons. We will talk about all the differences between the two types of data. Write me in the comments which type of data do you prefer between the two. Enjoy the reading!     Datetime Vs. Datetime2 First of all, it must be said that the datatype datetime is certainly the best known native format for storing a date in SQL Server. However, Microsoft has also been introducing a new type of data for many years now, also used to manage dates. It is in fact from SQL server 2008 that it is present in datetime2 data type. This "new" type of data was born primarily to have higher accuracy . Let's in fact talk about ... precision ! Datetime Certainly the datetime data type is not very precise and certainly there are many cases in which its precision is simply not sufficient. Let's see it with an example. Open the SQL Management studio (SSMS) and write the fo...
Read more

La clausola NOLOCK. Approfondiamo e facciamo chiarezza!

Image
Ehi ragazzi! La domanda è di quelle importanti! Siete pronti per scoprire tutto ma proprio tutto sulla clausola NOLOCK?   Se la riposta è si, allora seguitemi! Oggi ne spiegheremo il funzionamento in maniera approfondita. Faremo una introduzione al concetto di tipi di LOCK Ed infine vedremo come mai una SELECT ne può bloccare un'altra. Non vi nascondo che il motivo di questo articolo è fare chiarezza. Su questo argomento si sentono spesso affermazioni “curiose” ma tra le tante questa è la prima: “ uso la SELECT con l’opzione WITH(LOCK) così non blocco le altre tabelle ”.   Ma, è proprio vero quanto affermato? Beh oggi facciamo il punto!   Buona lettura!     La clausola NOLOCK Diciamo subito per iniziare che cos’è la NOLOCK. NOLOCK è il nome di una clausola che è possibile specificare all’interno di uno statement di tipo SELECT utilizzando la seguente sintassi: SELECT * FROM TABELLA WITH (NOLOCK) Si noti che non possiamo ...
Read more