In the first level of this Stairway Series, we setup a simple database and walked through a demonstration of how to setup and use Row-Level Security (RLS). In this next section, we will start to dive deeper into the predicates that are used to control security and look at a few different structures for building these functions.

Note that in Row-Level Security, a security predicate is implemented as a function, but the actual predicate is the application of the function to a table. In this level we will talk about the design of the functions and in the next level we will move into more examination of the predicate itself as it affects queries against a table.

The Security Predicate Function

A security predicate is a function that will determine if the user has access to a row. This is a T-SQL function, implemented inside the database where you are applying Row-Level Security. This function is used by SQL Server as a way of evaluating each row of a table to determine whether or not the row is returned as a part of the query. SQL Server does this automatically, without any user needing to call this function directly.

The security predicate works by linking two things together: the user that calls the query, and a horizontal slice of data in a table. To do this, the predicate must be able to determine who the user is that is executing the query, as well as somehow relate that user to a set of rows in a table. This can be through and valid T-SQL that can be expressed in the function.

The Security Predicate must be an inline table-valued function (iTVF) that contains a single SELECT query and returns rows that determine row access. The return value is typically shown as a 1, with some column name, though the important item to remember is that as long as something is returned, the user has access to the row data. Note that the result does not need to return a 1.

Writing a Predicate Function

To build a security predicate, you need to begin by examining the data that is stored in your system. The goal is to identify a way to segregate your data by users. Once you have this, you can construct a query that will link this data to your users and then bind the policy to a table with a security predicate. This section looks at the ways in which you will make decisions about designing your function.

Our function needs to somehow relate the data in a table to a user, and in order to do that, we need a query in our function that relates to the table to which we will find the function. Let's look at a quick example.

In the WideWorldImporters sample database, there are tables that have to do with orders. One of these is the Sales.Orders table. This table contains a number of columns, one of which is SalespersonPersonID. This is a FK to the Application.People.PeopleID column. I can write a query that joins these two tables and views some orders.

SELECT TOP 10
       p.FullName ,
       p.PersonID ,
       OrderID ,
       SalespersonPersonID
FROM Sales.Orders
    INNER JOIN
    Application.People AS p
        ON p.PersonID = Orders.SalespersonPersonID
ORDER BY OrderID;

Here are the first 10 orders returned.

Let's say that I want to limit the access to orders to only the salesperson for that order. I could do that by writing a function that returns a row when the salesperson matches the value of a user's login. I could do something like:

CREATE FUNCTION RLS.CheckSalesPersonForOrder
    (@PersonID AS INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN ( SELECT 1 as Result
         FROM Sales.Orders o
             INNER JOIN
             Application.People AS p
                 ON p.PersonID = o.SalespersonPersonID
         WHERE o.SalespersonPersonID = @PersonID
 AND p.LogonName = ORIGINAL_LOGIN()
         )
GO

In this code, I'm letting RLS pass in an ID, which I'll specify in the security policy. I can add any of the columns from the table on which this will be bound as parameters. In this case, I can choose to pass in SalespersonPersonID into the function, and it will take the place of the @PersonID parameter.

This means that I can write complex queries that might examine multiple columns. In this case, perhaps I want to ensure that a customer can also view their orders. That might mean that I do something like this:

CREATE FUNCTION RLS.CheckSalesPersonForOrder
(
    @PersonID AS INT ,
    @customerID AS INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT result = 1
    FROM Sales.Orders AS o
        INNER JOIN
        Sales.Customers AS c
            ON o.CustomerID = c.CustomerID
        INNER JOIN
        Application.People AS p
            ON ( p.PersonID = o.SalespersonPersonID
  OR p.PersonID = c.PrimaryContactPersonID 
  )
    WHERE (
              o.SalespersonPersonID = @PersonID
              OR o.CustomerID = @customerID
          )
          AND p.LogonName = ORIGINAL_LOGIN()
);
GO

In this code I'm passing in two columns from the Sales.Orders table, SalespersonPersonID and CustomerID. I'm using those to relate each row to some column in PersonID and then joining that to a logon stored in Application.Person. This is just an example, based on WideWorldImporters, but it's possible that you might also choose to do something like limit access to orders that have some status, or are of some age, using the columns in the target table to limit access. An example of that might be this function:

ALTER FUNCTION RLS.CheckSalesPersonForOrder
(
    @PersonID AS INT ,
    @DeliveryDate AS DATE
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT result = 1
    FROM Sales.Orders AS o
        INNER JOIN
        Application.People AS p
            ON p.PersonID = o.SalespersonPersonID
    WHERE o.SalespersonPersonID = @PersonID
          AND o.ExpectedDeliveryDate < SYSDATETIME()
          AND p.LogonName = ORIGINAL_LOGIN()
);
GO

These are examples and not necessarily practical, but let's return to our first example. We want to limit rows to the salesperson that is assigned to that order. I am passing in the value from the Sales.Orders table, but I don't really need to join that to the Application.People table. I could just query the single table like this:

ALTER FUNCTION RLS.CheckSalesPersonForOrder
(
    @PersonID AS INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT result = 1
    FROM Application.People AS p
    WHERE p.PersonID = @PersonID
          AND p.LogonName = ORIGINAL_LOGIN()
);
GO

Here I've altered the code to just query the Application.People table. If I use this code with a filter predicate, I get the rows shown below. Each of these is for SalespersonPersonID = 2.

Since the predicate will be joined with the rows in Sales.Orders to the Application.PersonID through the predicate, I can minimize the query resources required in the predicate by wisely writing my query. This becomes more and more important as the size of your table grows. Remember, this is still a query that must be evaluated by the query processor. We'll discuss performance and function best practices in a later level.

In the examples above, I've been using the ORIGINAL_LOGIN() function to link to data stored in a table. This is one way that the predicate can be written to determine row access, but there are plenty of other methods. We'll discuss these and their advantages and disadvantages in a future level.

Required Results

The examples that I've all seen return a 1 for each row that is valid, under some column name. The column name is a requirement of the iTVF itself. We can't build one without including a name. If I were to run code like this, I'll get an error:

The name of the column returned in the predicate isn't important. It just needs to be a valid SQL identifier. I've built functions with names like "x", "result", "rls", and "[Can I See the Row]". All of these compile into functions and work fine with the RLS security system. While you can choose anything, I'd make sure that you stick to some standard, just to ensure the code is clean. I might go with "Result" as the column name.

The other part of the result is the value. All the examples I've seen use "1" as the scalar being returned. However, this isn't required. I could choose 0 and this would work fine. As you can see below, there is the definition of the function and the results when querying the Sales.Orders table as Application.PersonID = 2. I haven't shown the security policy or query, but this is the predicate.

I get the same results if I return a string, such as "Access Granted" instead of the 0. I can even return one of the columns from the query, such as Application.FullName. As long as I return a value, the RLS system will grant access to the row. I even tried this value, which worked:

ALTER FUNCTION RLS.CheckSalesPersonForOrder
(
    @PersonID AS INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT [Result] = null
    FROM Application.People AS p
    WHERE p.PersonID = @PersonID
          AND p.LogonName = ORIGINAL_LOGIN()
);

Schemabinding

One quick note is that the predicate function must be schemabound. If I remove the WITH SCHEMABINDING, I'll get this error when I try to use this predicate in the policy. You can see that below:

Conclusion

The security predicate is the first step towards implementing an RLS system. In this level we've looked at the requirements and structure of the predicate and given you a basic understanding of how to write one. You need to ensure this is schemabound and design this function in a way that will tie together data in a target table with the user, though your function does not need to actually query the target table.

In future levels we'll examine the different ways that you can design these predicates to meet your needs and discuss the best practices that can help ensure your system performs well under load.

In the next level we'll start to take a look at Security Policies and how they are used to link your predicate to a target table.