This post is part of the serie 'Microsoft SQL Server 2016'. Be sure to check out the rest of the blog posts of the serie!
- What's new in SQL Server 2016: Json
- Comparing execution plans with SQL Server 2016
- Bootstrap + SASS = ♥
- What's new in SQL Server 2016: Dynamic Data Masking
- What's new in SQL Server 2016: Row-Level Security (this post)
- What's new in SQL Server 2016: Stretch Database
- What's new in SQL Server 2016: Live Query Statistics
- What's new in SQL Server 2016: Temporal Tables
- What's new in SQL Server 2016: Always encrypted
- SQL Server 2016 SP1 - Licensing changes
The final version of SQL Server 2016 has been released recently. So, it's time to take a look at the new features provided by this version. Today we will set up Row-Level Permissions.
This new feature allows you to create implicit filters on tables based on the currently logged on user. For example a salesman will only see his orders while a manager will see all the orders and that with the same query
SELECT * FROM Order. One can also take the case of a multi-tenants application. In this type of application one tenant should not see the lines of another tenant. You will add filters to prevent this.
For an application the current user can be identified in 2 different 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
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_CustomerName = USER_NAME() are returned.
In case the SQL login can not be used, we can use
CONTEXT_INFO. For that, the application defines its value with the identifier of the current user, then executes the desired queries:
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 request, the application starts by calling this stored procedure with the current user identifier as parameter:
EXEC sp_setContextInfo 'FD65DA72-D6FE-4DC9-9AC5-E554CDC8E57F' SELECT * FROM [Order]
This new feature is simple to set up and helps secure the data in the database. Even if the executed queries do not filter the data correctly, these implicit filters will finish the job.