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!
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-tenant 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
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.
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:
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 request, the application starts by calling this stored procedure with the current user identifier as a parameter:
SQL
EXEC sp_setContextInfo 'FD65DA72-D6FE-4DC9-9AC5-E554CDC8E57F'
SELECT * FROM [Order]
#Conclusion
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.
Do you have a question or a suggestion about this post? Contact me!
