In some scenarios, like single-database multi-tenant or soft delete ones, you could want to restrict access to your rows based on a value stored in one or more values stored in your columns in example IsDeleted flag or some kind of TenantId column. Since SQL Server 2016 we have a wonderful tool for that called Row Level Security policy.
Before we move to the main topic let’s talk about the origin of this post for a while. It’s no secret I’m an enthusiast of SQL and while I’m using ORMs on the daily basis I’m quite fond of using SQL features and it pains me deeply that some of us don’t know about a lot of good stuff that SQL Server has out of the box and that are just not easily available with popular ORMs.
And because of that I’m currently working on new talk titled ‘ORM – the tip of an iceberg’ in which I’ll talk about some fun features of SQL Server that are not complicated and/or hard to implement and could be unknown to some of us that haven’t gone too far outside to beautiful land of relations outside of ORMs. My goal isn’t to tell you that ORMs sucks and every query should be raw SQL or otherwise, I believe that those two could really complement each other but for that, you should know both of them. This and few next posts are companion posts for my talk, I’ll make them as independent content and hope that I’ll guide someone from my audience here for some details that I could not have included on slides.
So let’s leave boring foreword behind and focus on the topic.
First of all. What cases could benefit from this approach? When could contents of our rows or should mean that data can or cannot be displayed or modified? Two cases that almost immediately came to my head was multi-tenant scenario or table with a soft delete flag. During this post, we’ll focus on the first scenario but I’m sure after reading it you’ll be able to implement second one or any other easily. So let’s create our database with one simple table where CompanyId is our tenant id.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE [dbo].[Employees] ( [Id] bigint IDENTITY(1,1) NOT NULL, [CompanyId] bigint NOT NULL, [FirstName] nvarchar(200) NOT NULL, [LastName] nvarchar(200) NOT NULL, [Email] nvarchar(260), CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ([ID] ASC) ); GO INSERT INTO [dbo].[Employees] ([CompanyId], [FirstName], [LastName], [Email]) VALUES (1 , 'Rafal', 'Hryniewski' , 'my@email.com'), (1, 'John' , 'Smith', 'notmy@email.com'), (1, 'Some', 'Guy', 'someguy@email.com'), (2, 'Other', 'Guy', 'otherguy@mail.net'), (2, 'That', 'Guy', 'thatguy@mail.net') GO |
Row Level Security in MS SQL is available in Azure SQL Databases (Compatibility level 130 or higher) and SQL Server 2016. It consists of two parts – table-valued function, containing predicate on which will define access and security policy that attaches it to table with an indication to a specific column(s) on which our predicate will depend.
So let’s create our predicate. Good practice for doing so is creating new schema first. You should also know that predicate must be a table-valued function and that means it must return table. Inside this function, you should specify a statement that will return 1 for a row that should be accessed. In our case, we’ll use a common pattern with SQL Session Context. We’ll fetch previously inserted into session CompanyId value and compare it with function input which would be the value that we’ll pass later from one our columns when defining Security Policy. If CompanyId in session is equal to CompanyId in a row – access is granted.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE SCHEMA [Security] GO CREATE FUNCTION [Security].[FN_SecurityPredicate](@CompanyId bigint) RETURNS TABLE WITH SCHEMABINDING AS RETURN --Access is granted for row only when this predicate returns 1 SELECT 1 AS Result WHERE CAST(SESSION_CONTEXT(N'CompanyId') AS int) = @CompanyId; GO |
It’s simple. And if we’re going to query through this table a lot it really should be simplest as possible to minimize performance overhead.
So now we’re going to attach this predicate function to our table using Security Policy. There are two kinds of those filter and block predicates. A filter will operate silently and just don show anything that doesn’t match the predicate. That means the filter will work only on SELECT, UPDATE and DELETE statement.
The second kind is block predicate. It could be AFTER INSERT or AFTER UPDATE that will block those operations if inserted/updated value doesn’t meet predicate. There are also BEFORE DELETE and BEFORE UPDATE filters which will execute against current value in a row, not the one that we’re updating with (in case of UPDATE operation). So let’s attach our function to security policy, note how we’re indicating CompanyId as column that is passed into function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE SECURITY POLICY [Security].[SalesFilter] ADD FILTER PREDICATE [Security].[FN_SecurityPredicate](CompanyId) ON [dbo].[Employees], ADD BLOCK PREDICATE [Security].[FN_SecurityPredicate](CompanyId) ON [dbo].[Employees] AFTER INSERT, ADD BLOCK PREDICATE [Security].[FN_SecurityPredicate](CompanyId) ON [dbo].[Employees] AFTER UPDATE, ADD BLOCK PREDICATE [Security].[FN_SecurityPredicate](CompanyId) ON [dbo].[Employees] BEFORE DELETE, ADD BLOCK PREDICATE [Security].[FN_SecurityPredicate](CompanyId) ON [dbo].[Employees] BEFORE UPDATE WITH (STATE = ON); GO |
So what’ll happen if we query for all employees?
|
1 |
SELECT * FROM [dbo].[Employees] |
We’ll get an empty result as we don’t have any value stored in SQL Session. How about insert statement?
|
1 2 3 4 |
INSERT INTO [dbo].[Employees] ([CompanyId], [FirstName], [LastName], [Email]) VALUES (4 , 'Should', 'Be Blocked' , 'blocked@email.com') GO |
This time we’re going to get error.
|
1 2 3 |
The attempted operation failed because the target object 'dbo.Employees' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate. |
What should we do to gain access to any row? We should set CompanyId value to SQL Session Context. We’re going to use built-in Stored Procedure to do that.
|
1 2 3 |
EXEC SP_SET_SESSION_CONTEXT @key=N'CompanyId' ,@value=2 |
After doing that, and querying for all employees in the same session we’ll get only employees with CompanyId equal to 2 without specifying any WHERE clause. We can also execute other commands as long as those will touch only rows with CompanyId equal to 2. Simple as that.
Of course, you could get the same effect with Entity Framework or other ORM and simple.Where() method but RLS is security layered on database layer and that means even if someone will forget about filtering in some query, other tenants data will not leak where they shouldn’t. In my opinion single line of defence like that is more secure and durable than relying on applying most important filtering in every query developers would write in code.
Row Level Security is simple and it’s easy to understand even by intermediate SQL users. However, there is one problem with this approach – how the hell we’re supposed to set SQL Session in Entity Framework? Well, it’s possible and it’s not hard. And in a few days, I’ll show you how you can do it.