Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Row Level Security Block and Filter Predicates Example


By:   |   Last Updated: 2016-01-26   |   Comments   |   Related Tips: More > 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:

RowLevelSecurityBlockpredicates

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:

Session_Contex

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)
Execute_as_user

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.

Actual_Execution_plan

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
sys.security_policies
Next Steps


Last Updated: 2016-01-26


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools