Prashanth Jayaram

SQL Server data security feature RLS (Row-Level Security) and GDPR

March 9, 2018 by

Of late, there’s been a lot of noise around the term, GDPR. Chances are, some of us even had to go through learning sessions targeted at IT professionals to learn about what this new standard of data protection means. GDPR is primarily a European privacy law which sets a new bar, globally, on privacy rights, compliance, and security. GDPR is mainly about protecting the rights of every individual, providing the individual with more control over his personal data. It dictates how data should be handled, managed and protected going forward, the individual’s choice being the prime focus.

Today, data is widespread; many corporations handle part of the data on the cloud and part of it on premises. Our focus being SQL Server, we shall talk about what capabilities Microsoft gives us in order to be compliant with these laws that come into effect on the 25th of May, 2018. We would have to modify our data handling procedures keeping the focus on the security of the data processing.

There are several built-in security capabilities in SQL Server to help in reducing risk and an overall improvement in managing data at the database level or otherwise.

But where do we start?

Before we start discussing on the nitty-gritty of implementing GDPR principles in SQL Server, I would recommend reading the article Using production data for testing in a post GDPR world by Brian Lockwood, the CEO of ApexSQL.

Now that we have a basic idea on GDPR, let’s now dive a little deeper. Here are a few points to keep in mind:

  1. Discover: Identify which data is of personal nature, and technical details about it such as its location and the mode of storage.
  2. Manage: Classify the data access needs and decide the governance model accordingly.
  3. Protect: set up security controls to prevent vulnerabilities and also detect and respond to data breaches.
  4. Report: Document and manage data requests, and provide notifications in case of breaches.

Following are the features in SQL Server that support GDPR compliance:

  1. Row-Level Security (RLS)
  2. Dynamic Data Masking (DDM)
  3. Transparent Data Encryption (TDE)
  4. Transport Layer Encryption (TLS)
  5. SQL Server Audit
  6. Temporal Tables
  7. Always Encrypted (AE)
  8. Authentication
  9. Azure vault
  10. Azure Active Directory
  11. SQL Threat detection

GDPR can be further classified into several categories as follow:

  1. Encryption
  2. Pseudonymous data
  3. Data access, authorization and limitation
    • Row-Level Security (to be discussed in detail in this article)
    • TDE
    • Azure Active Directory
    • Always Encrypted
  4. Assessment, reporting and notification
    • SQL Server Audit
    • SQL Threat Detection

In this article we discuss the Row Level Security feature. This security feature is available from SQL Server 2016. With SQL Server 2016 has row-level security, fine-grained access control, completely transparent to client applications available in all editions. Row level security is the feature available to filter content based on a user’s specific need, thus reducing the database exposure to unauthorized disclosure of personal data.

Row-Level security defines the security policy to restrict access to objects based on specific entitlements. RLS defines the database user’s access restriction to specific rows by filtering the users using predicate clause.

Row-level security is applied at the table level, without having to implement custom stored procedures to make it work for insert, update, and delete SQL statements. It greatly simplifies the design and coding required since it’s managing the data in the database tier. In previous designs, sensitive data used to be handled at the application tier. The process was very cumbersome and it required a lot of coding and design changes.

Let’s dive in to the concepts of RLS and see how the security policy can be built on the data table. It covers the filter and block predicate mechanism to show how the row-level security handles the input at the run time.

For the purpose of the demo, I’m going to refer Graph Database article to sample data for the “emp” table. I’m going to create a table called “emp” and query it based on the designation. The table has various designated employees such as ANALYST, MANAGER, CLERK, etc. The employee designated as manager is highest in the hierarchy and will be able to access the all of the rows of the table, however, clerk and analyst will be limited to viewing the related subset of the emp table.

We have an idea of a table-valued function, which will determine which rows should be visible to which users. And it’s entirely up to us to define the predicate, the filter that determines who sees what. You can also apply a block to insert, update, and delete. I’m going to show you the filter and block mechanism, which is row-level security, based on the input provided during the query execution. So starting at the top here, let’s look at our data for a moment. We have a table with a lot of locations in it.

Let’s query the emp table to retrieve the data subsets based on the designated job role.

Now, the view vwEMPDetails is created, which will be used as test data.

The dbo is the current user under the current scope of the session context. 

Now, we’re going to create three new users. 

  1. Manager, who has access to the all the rows of the emp table
  2. Clerk, who has access to clerk-related data, a subset of the emp table
  3. Analyst, who can see the related data of the analyst job of emp table

To define the security policies, first create a schema, the Row-Level Security (RLS) filter

To define the security classifier function, one has to clearly define the filter clause since it determines how the filter works on the actual table.

Create the predicate to filter the data based on the current scope of the database users

Now, the function has been created. Let’s define the security policy using the classifier function. Under the Security tab, expand Security policies to view the newly-created security policy, JobFilter.

At first, the newly-created three users have no rights to access the table or the view. Let’s grant select permission on the table emp and view vwEmpDetails to all the three users.

Now, run the select statement as manager, clerk, and analyst to validate the output

As we can see, the subset of emp table is projected as resultsets based on the current scope of the user

Now, it’s a time to validate the access the database user, Manager, has.

We can see that the database user Manager is able to view all the rows of emp table.

Now, perform the DML operations on the emp table. Let’s grant the required permission to update or insert the data into the emp table.

We can see in the following output that the 7,362 records were inserted into the table.

In this section, we will discuss how the security policy can be defined to block the users from updating or inserting values into the table.

Create a schema RLSSecurityblock using the create schema syntax.

Modify the existing security policy by adding the block predicate portion to prevent any other users from inserting into the emp table

Now, let’s validate if the block predicate functionality works as expected.

On implementing the block predicate, we can see that the insert operation fails for the user clerk

Let’s test the block predicate by inserting a row into the emp table as Manager.

We can see that the row 19 was inserted into the table.

Wrapping up

In this article, we walked through the filter and block predicates. We went step by step to provide the required access to users and also, isolate the data operations from various users. This feature greatly simplifies the data security design and helps go closer to implementing GDPR, by enabling us to manage the application access model effectively.

Prashanth Jayaram

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram