SQL Server RLS feature and GDPR

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.

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

Further reading

SQL Server RLS  and GDPR

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.

About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in GDPR, SQL Server 2017 and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s