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:
- Check out all of the SQL Server 2016 tips.
