What's new in SQL Server 2016: Row-Level Security

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!

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]

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.

Leave a reply