This post is part of the series 'Microsoft SQL Server 2016'. Be sure to check out the rest of the blog posts of the series!
SQL Server 2016 was recently released, so it's a good time to explore its new features. In this post, we look at Row-Level Security.
This feature lets you create implicit filters on tables based on the currently logged-in user. For example, a salesman will only see his own orders while a manager sees all orders – both using the same query SELECT * FROM Order. Another common use case is multi-tenant applications, where one tenant must not see rows belonging to another tenant. Row-Level Security lets you enforce this automatically with filters.
For an application, the current user can be identified in two ways:
- The SQL login in case the user connects directly to the database,
- An identifier defined by the application
The principle of Row-Level Security is very simple:
- You create a function that returns the filtered rows
- You associate this function with the desired table
SQL
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@employeeName AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @employeeName = USER_NAME(); -- use your own logic
GO
CREATE SECURITY POLICY OrderFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(Order_EmployeeName)
ON [dbo].[Order]
WITH (STATE = ON);
GO
If you execute the SELECT * FROM [Order] query, only rows that match Order_EmployeeName = USER_NAME() are returned.
When the SQL login cannot be used, you can use CONTEXT_INFO instead. The application sets it to the current user's identifier before executing queries:
SQL
CREATE PROCEDURE sp_setContextInfo(@currentUserId uniqueidentifier)
AS
SET CONTEXT_INFO @currentUserId;
GO
CREATE FUNCTION Security.fn_securitypredicate(@userId uniqueidentifier)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE CONVERT(uniqueidentifier, CONTEXT_INFO()) = @userId; -- use your own logic
GO
CREATE SECURITY POLICY OrderFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(Order_Customer_Id)
ON dbo.[Order]
WITH (STATE = ON);
GO
Before executing a query, the application calls this stored procedure with the current user's identifier:
SQL
EXEC sp_setContextInfo 'FD65DA72-D6FE-4DC9-9AC5-E554CDC8E57F'
SELECT * FROM [Order]
#Conclusion
Row-Level Security is straightforward to set up and adds a reliable layer of data protection at the database level. Even if application queries do not filter data correctly, these implicit filters act as a safety net.
Do you have a question or a suggestion about this post? Contact me!
