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:
- Discover: Identify which data is of personal nature, and technical details about it such as its location and the mode of storage.
- Manage: Classify the data access needs and decide the governance model accordingly.
- Protect: set up security controls to prevent vulnerabilities and also detect and respond to data breaches.
- Report: Document and manage data requests, and provide notifications in case of breaches.
Following are the features in SQL Server that support GDPR compliance:
- Row-Level Security (RLS)
- Dynamic Data Masking (DDM)
- Transparent Data Encryption (TDE)
- Transport Layer Encryption (TLS)
- SQL Server Audit
- Temporal Tables
- Always Encrypted (AE)
- Authentication
- Azure vault
- Azure Active Directory
- SQL Threat detection
GDPR can be further classified into several categories as follow:
- Encryption
-
Pseudonymous data
- Configuring Always Encryption in SQL Server
- Dynamic Data Masking (DDM)
-
Data access, authorization and limitation
- Row-Level Security (to be discussed in detail in this article)
- TDE
- Azure Active Directory
- Always Encrypted
-
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.
|
1 2 |
select ename,job,hiredate,deptno,e.* from emp e where job in ('ANALYST','CLERK','MANAGER','PRESIDENT') |
Now, the view vwEMPDetails is created, which will be used as test data.
|
1 2 3 4 |
CREATE VIEW vwEmpDetails as select ename,job,hiredate,deptno from dbo.emp where job in ('ANALYST','CLERK','MANAGER') |
The dbo is the current user under the current scope of the session context.
|
1 |
SELECT USER_NAME(), CURRENT_USER |
Now, we’re going to create three new users.
- Manager, who has access to the all the rows of the emp table
- Clerk, who has access to clerk-related data, a subset of the emp table
- Analyst, who can see the related data of the analyst job of emp table
|
1 2 3 |
CREATE USER ANALYST without login CREATE USER CLERK without login CREATE USER MANAGER without login |
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.
|
1 2 |
CREATE SCHEMA RLSSecurityFilter; GO |
Create the predicate to filter the data based on the current scope of the database users
|
1 2 3 4 5 6 7 8 9 10 |
CREATE FUNCTION RLSSecurityFilter.fn_getJob(@job AS varchar(20)) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS result WHERE USER_NAME()='MANAGER' OR @job= USER_NAME() GO |
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.
|
1 2 3 4 |
CREATE SECURITY POLICY JobFilter ADD FILTER PREDICATE RLSSecurityFilter.fn_getJob(job) ON dbo.emp WITH (STATE = ON); |
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.
|
1 2 3 4 5 6 7 8 9 10 |
GRANT SELECT ON emp TO ANALYST; GRANT SELECT ON emp TO CLERK; GRANT SELECT ON emp TO MANAGER; GRANT SELECT ON emp TO [vwEmpDetails]; GRANT SELECT ON emp TO [vwEmpDetails]; GRANT SELECT ON emp TO [vwEmpDetails]; As we can see that the select statement as dbo will not yield any rows from the table. The filter is currently being applied to the dbo user. Since dbo doesn’t meet the criteria, the data is not displayed. select USER_NAME() select ename,job,hiredate,deptno from dbo.emp |
Now, run the select statement as manager, clerk, and analyst to validate the output
|
1 2 3 4 5 6 7 8 9 10 11 |
EXECUTE AS USER='CLERK' select USER_NAME() EXEC ('select ename,job,hiredate,deptno from dbo.emp') revert; go EXECUTE AS USER='Analyst' select USER_NAME() EXEC ('select ename,job,hiredate,deptno from dbo.emp') revert; go |
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.
|
1 2 3 4 5 |
EXECUTE AS USER='Manager' select USER_NAME() EXEC ('select ename,job,hiredate,deptno from dbo.emp') revert; go |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
GRANT UPDATE, INSERT ON dbo.emp TO CLERK; GRANT UPDATE, INSERT ON dbo.emp TO Manager; EXECUTE AS USER='CLERK' select USER_NAME() INSERT INTO EMP VALUES (7362, 'SQL', 'CLERK', 7902, '02-MAR-1983', 9000, NULL, 20) REVERT GO EXECUTE AS USER='CLERK' SELECT USER_NAME() SELECT * FROM EMP REVERT; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE SCHEMA RLSSecurityblock; GO Create the inline TVF (Table Valued Function) to create the Block predicate. CREATE FUNCTION RLSSecurityblock.fn_getJob(@job AS varchar(20)) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS result -- Predicate logic WHERE USER_NAME()='MANAGER' --Only Manager should have DML access to all rows GO |
Modify the existing security policy by adding the block predicate portion to prevent any other users from inserting into the emp table
|
1 2 3 |
ALTER SECURITY POLICY jobfilter ADD BLOCK PREDICATE RLSSecurityblock.fn_getJob(job) ON dbo.emp AFTER INSERT; |
Now, let’s validate if the block predicate functionality works as expected.
|
1 2 3 4 |
EXECUTE AS USER='CLERK' select USER_NAME() INSERT INTO EMP VALUES (7362, 'SQL', 'CLERK', 7902, '02-MAR-1983', 9000, NULL, 20) |
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.
|
1 2 3 4 5 6 7 8 9 |
EXECUTE AS USER='MANAGER' select USER_NAME() INSERT INTO EMP VALUES (7300, 'SQLShack', 'CLERK', 7900, '02-MAR-1984', 9000, NULL, 20) REVERT; EXECUTE AS USER='MANAGER' SELECT USER_NAME() SELECT * FROM EMP REVERT; |
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.
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
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021