SQL Server 2016 Row Level Security Block and Filter Predicates Example

By: Rajendra Gupta   |   Comments   |   Related: > SQL Server 2016

Problem

In a previous tip (SQL Server 2016 Row Level Security Example) we have seen how Row Level Security works and its practical implications. In this tip we will see some additional enhancements made in SQL 2016 CTP 3.1 and explore these new features.

Solution

As we have previously seen, Row Level Security presents FILTER predicates to restrict the access of data only to authorized personnel. However, if a user has higher permissions (i.e. insert, update or delete) on the table itself we could not restrict the user from modifying data. SQL Server 2016 CTP 3.1 introduces BLOCK predicates to address this security concern.

In this tip we are going to create a sample table with data and code to demonstrate the SESSION_CONTEXT function which is introduced with CTP 3.1. In SQL Server 2016 we can store multiple key and value pairs which are accessible throughout the session. The key and value pairs can be set by the sp_set_session_context system stored procedure and these set values can be retrieved one at a time by using the SESSION_CONTEXT function.

Sample Table and Data for Row Level Security Example

Let's with a sample table and data.

CREATE TABLE CUSTOMER (
	Customerid int identity(1,1) primary key,
	Name nvarchar(64),
	city nvarchar(20),
	Status nvarchar(64),
	EmpID int DEFAULT CAST(SESSION_CONTEXT(N'EmpID') AS int) -- This will automatically set EmpID to the value in SESSION_CONTEXT
)

--Sample Data

Insert into customer(Name,City, Status,Empid ) values('Alex','London','Active',1)
Insert into customer(Name,City, Status,Empid) values('Dirk','Slough','Active',2)
Insert into customer(Name,City, Status,Empid) values('Mark','Slough','Inactive',1) 

SQL Server Row Level Security Function and Security Policy

Suppose in a call center each employee is assigned a few customers, so he should be only authorized to make any changes to specific customer data. Also if a new customer in added, this employee should be able to add the record via their employee id.

Now we will create predicate function with our basic logic above that is based on the session_context and EmpID values stored in the table.

CREATE FUNCTION dbo.CustomerAccesspredicate(@EmpID int)
	RETURNS TABLE
	WITH SCHEMABINDING
AS
	RETURN SELECT 1 AS accessResult
	WHERE CAST(SESSION_CONTEXT(N'EmpID') AS int) = @EmpID
GO

Now we will define the security policy for the Filter predicate and BLOCK predicate to restrict access to the data.

CREATE SECURITY POLICY dbo.CustomerSecurityPolicy
	ADD FILTER PREDICATE dbo.CustomerAccesspredicate(Empid) ON dbo.Customer,
	ADD BLOCK PREDICATE dbo.CustomerAccesspredicate(Empid) ON dbo.Customer AFTER INSERT 
GO

SQL Server User Creation for Testing

In the next script, we have created an App user with permissions to SELECT, INSERT, UPDATE and DELETE on the Customer table.

CREATE USER Apps WITHOUT LOGIN 
Go

GRANT SELECT, INSERT, UPDATE, DELETE ON Customer TO Apps

Here is the sample data as we begin testing:

Testing SQL Server Row Level Security

Now if we run a SELECT statement without using the session_context we get the following results:

Execute as user ='APPS'
SELECT *   FROM [RLSDEMO].[dbo].[CUSTOMER]

Output will be : (0 row(s) affected).

Now if we set the session_context using the sp_set_session_context stored procedure we will get the output below:

Let's test an insert statement:

Execute as user ='APPS'
EXEC sp_set_session_context N'EmpID', 1
Insert into customer(Name,City, Status,Empid ) values('Adam','york','Inactive',1)  --Output 1 Row(s) inserted

As we can see here, EmpID 1 can insert the row into table with their EmpID value. However, if this person tries to insert the rows with a different Empid we get the following error:

Execute as user ='APPS'
EXEC sp_set_session_context N'EmpID', 1
Insert into customer(Name,City, Status,Empid ) values('SIRK','BRACK','active',2)

Let's test two update statement scenarios:

Execute as user ='APPS'
EXEC sp_set_session_context N'EmpID', 1

Update Customer set Status='active' where name='Mark' -- Output 1 Row(s) Updated

Update Customer set Status='Inactive' where name='Dirk' --Output 0 Row(s) updated

In the first case the data is updated. In the second case, the update cannot be completed. It does not return an error, but will return the output as 0 rows updated i.e. indirectly indicating the data was not modified.

The same is the case for a delete statement. The security policy will not permit deleting data for another EmpID. If we look at the Actual Execution plan as shown below, we can see the predicate defined.

Summary - SQL Server 2016 Filter and Block Predicates

Here is what we have demonstrated about Filter and Block Predicates:

• Filter predicates silently filter rows in read operations – SELECT, UPDATE and DELETE
• Block predicates explicitly block write operations – INSERT, UPDATE and DELETE

Now with Row Level Security we can now apply the security restrictions in a much more secure manner.

We can also use the security catalog views to see the applied security predicates and policies as shown below:

SELECT * FROM sys.security_policies
SELECT * FROM sys.security_predicates

Next Steps

• Read more about SQL Server 2016 Row Level Security:
  • SQL Server 2016 Row Level Security Introduction
  • Implement Row Level Security Natively with SQL Server 2016
  • SQL Server 2016 Row Level Security Example
  • SQL Server 2016 Row Level Security Limitations, Performance and Troubleshooting
• Check out all of the SQL Server 2016 tips.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips