Once we have discovered and classified our data, we enter the second step on our way to comply with the GDPR, now it is the turn to manage access to the data and control the use that is made of them.

Why is this important for the GDPR?

The GDPR specifically addresses the need for mechanisms which limit access to data, requiring “measures [that] shall ensure that by default personal data are not made accessible without the individual’s intervention to an indefinite number of natural persons.”

GDPR Article 25(2)—“Data protection by design and by default.”

SQL Server has several tools for access control, but it is the SQL Server administrator who makes use of them, and in many cases for convenience, it grants unnecessary permissions to certain users, giving access to data that this user should not be able to see or confidential data.

To help with the management of these accesses and control the data that can be viewed, SQL Server offers the following:

  • Authentication mechanisms to ensure that only users with valid credentials can access the database server. SQL Server supports SQL Server authentication and authentication with integrated Windows security, and in Azure environment, for Azure SQL Database and Azure SQL Data Warehouse, access control by Azure Active Directory roles.
  • Access control based on roles, granting users different roles (access levels) depending on their role within the team.
  • Row-Level security to avoid access to specific rows of a table based on the characteristics of the user who tries to access the data, for example that in the employee table you can only see your data and not those of other employees.
  • Use of data masking, for example for bank account numbers of type XXXX-XXXX-XXXX-1321, to hide data to users who do not have permissions using Dynamic Data Masking.
  • Verify the changes that occur in a table using SQL Server Audit or for Azure SQL Database using Azure SQL Server Auditing.

In this post we will focus on the newest features, available from SQL Server 2016, such as Row-Level Security and Dynamic Data Masking.

Row-Level Security

For our example, we are going to use the AdventureWorks2016 database, specifically the [Sales]. [SalesTerritory] and [Sales]. [Customer] tables.

When executing the queries you can see how each Customer in the [Sales].[Customer] belongs to a Territory.



In our example we are going to use Row-Level Security to control that only the users that belong to a Territory can see the Customers that corresponds to them.

We proceed to create the users:

We grant these users permissions:

And the next step would be to create the Row Level Security, in our case we will first create a schema to have it more organized:

The next thing is to create a function on [Sales]. [SalesTerritory] that we will later use to filter [Sales]. [Customer].

This function is responsible for validating whether or not the user will be able to see a certain row based on the TerritoryId and later on the CountryRegionCode

Once we have created the function, we will create a Security Policy that will use this function to filter the table [Sales]. [Customer]

Now that we have everything created, we will perform a series of tests to better understand how it works.

DBO User

First, we try to execute it with the user with whom we have created everything, in our case it is a user with dbo permissions

It does not return any results, and it is correct because our user is not included in the function.

USCountryManager User

Now we are going to perform the same test but using the USCountryManager user that is in the function, we will use “EXECUTE AS USER” for it:

We see that there are results, and that several TerritoryId appear, this is normal since “US” has several territories:

GBCountryManager User

The last test will be done with the GBCountryManager user, who only has one Territory

Finally and if we wanted to delete everything and leave it as in the beginning, we would only have to execute the following:

Once we have understood how Row-Level Security works and how the data is filtered, we will move on to use the Dynamic Data Masking functionality to mask data.

Dynamic Data Masking

The first thing to know about Dynamic Data Masking is that the masking is done at execution time, that is, the data in the table is not masked.

To control whether a user can see a masked data or not, SQL Server uses the “UNMASK” permission.

In this example we are going to show the masking types DEFAULT, EMAIL and PARTIAL.

We are going to use two users, the DBA user who will have UNMASK permissions, and the Developer user who will not have UNMASK permissions

We start by creating the users

We give these users permissions:

And we grant the UNMASK permission to DBA user

Now that we have the users with the appropriate permissions, we can see the types of masking and how they apply to the data, for this we will use again the AdventureWorks2016 database

DEFAULT masking

This function replaces the columns of type string with the string “XXXX” and those of the numeric type with “0”.

Let’s use it on the PhoneNumber column of the Person.PersonPhone table

And we’re going to run a TOP 10 on the table using the DBA and Developer users to see the difference in the visualization

DBA user (with UNMASK permission)

Developer User

EMAIL masking

This function replaces the part before the @ with the first letter and “XXXX” and puts “@ XXXX.com” at the end

We’re going to use it on the EmailAddress column of the Person.EmailAddress table

And we’re going to run a TOP 10 on the table using the DBA and Developer users to see the difference in the visualization

DBA user (with UNMASK permission)

Developer User

PARTIAL masking

This function defines a number of characters at the beginning and at the end that will remain unmasked, and in the middle will use the string that is chosen.

We are going to use it on the column LastName of the Person.Person table, we will leave a character at the beginning and two at the end without masking and in the middle we will put the text “GDPR”.

And we’re going to run a TOP 10 on the table using the DBA and Developer users to see the difference in the visualization

DBA user (with UNMASK permission)

Developer User

To finish we will see how quickly we can reverse all the changes made.

We revoke the UNMASK permission to the DBA user

And we cleared the masking in the three tables that we used

Now that we know how to control the access and visibility of the data, it is time to go to the next point of the GDPR and see how to Protect this data.

GDPR Series:

  1. How to adapt SQL Server to the GDPR
  2. Discovering and classifying personal data with SQL Server
  3. Managing access and use of data with SQL Server

If you want us to help your business or company to comply with the GDPR contact us at info@aleson-itc.com or call us at +34 962 681 242

Recommended Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.