Using Row-Level Security with HierarchyID

When I first heard about Row-Level Security (RSL), one of the first use cases I though of, was to satisfy queries against a hierarchical table. For example, imagine that you had a table of employees details; you could use RSL to limit the rows that each employee could view, to include only those employees who report to them.

So lets have a look at how you could achieve this, by using the HumanResources.Employee table in the AdventureWorks2016CTP3 database.

In order to implement RSL, we will need two objects. The first is a Security Predicate. This consists of an inline table-valued function, which determines if a row should be accessible. The second, is a Security Policy. The Security Policy is a new artifact type in SQL Server 2016, and binds the Security Predicate to a table.

The script below creates the Security Predicate in a schema named Security.

CREATEFUNCTION Security.fn_securitypredicate(@OrganizationNode HIERARCHYID)
    RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
FROM HumanResources.Employee e1
WHERE @OrganizationNode.IsDescendantOf(OrganizationNode) = 1
AND LoginID = 'adventure-works\' + SUSER_SNAME() ;
GO

The function accepts a single parameter, of the type HIERARCHYID. This is a complex data type, implemented through SQLCLR, which provides a number of methods, which can be used to determine position within a hierarchy, as well as hierarchy ancestry. A full method reference for the data type can be found at  https://msdn.microsoft.com/en-us/library/bb677193.aspx.

Using SCHEMABINDING on the function means that columns refferenced by the Security Predicate cannot be altered, but simplifies security administration, as the user who implicitly calls the function, will not need permissions to any tables or functions that are referenced by the Security Predicate.

The query uses the IsDecendantOf method, against the @OrganizationNode parameter (which will represent the OrganizationNode column of each row within the Employees table, to find all descendants of the row, where the LoginID column corresponds with the User ID of the user that has run the query. The concatenation of 'adventure-works\' is used to make the value returned by the SUSER_SNAME() function match the values stored in the table, where the domain of the user is also recorded. 1 is returned, for each row that matches the criteria, which tells the Security Policy that the row can be accessed.

The script below creates the Security Policy.

CREATE SECURITY POLICY Security.EmployeeSecurityPolicy
ADD FILTER PREDICATE Security.fn_securitypredicate(OrganizationNode) ON HumanResources.Employee
WITH (STATE=ON, SCHEMABINDING=ON) ;

The Security Policy is also created in the Security schema. Creating RSL objects in a separate schema is a best practice, as it simplifies security administration. The ADD FILTER PREDICATE syntax performs several functions.  

Firstly, it specifies that the predicate should be used to silently filter rows, as opposed to a BLOCK predicate, which will stop DML statements beling issued against rows, and return an error message.

Secondly, it binds the Security Predicate to the HumanResources.Employee table.

Thirdly, it passes the OrganizationNode column, from the HumanResources.Employee table, to the Security Predicate function.

The WITH statement specifies that both STATE and SCHEMABINDING are ON. STATE will determine if the Policy is enabled on creation. SCHEMABINDING will determine if a Security Predicate MUST use SCHEMABINDING, or if it is optional.

I talk more about RSL, in my upcoming book, Securing SQL Server, which will be published by Apress, in early 2017.