In Part 1 of this series, we introduced Row Level Security (RLS) and the table-valued function (TVF) that will be used to filter data, based on a specific user. The sample queries filtered data by cross-applying the table with the function. Manually filtering data is great, but RLS allows us to formalize the filter and be used for every query that uses the table.

Defining the Security Policy

In SQL Server, Security Policies are used to bind the TVF to one or more tables. Below is an example of a security policy in our Endless Timekeeping database.

CREATE SECURITY POLICY [App].[FilterUsersPolicy] 
ADD FILTER PREDICATE [App].[udf_UsersFilter]([UserID]) ON [App].[Users]
WITH (STATE = ON, SCHEMABINDING = ON)

Let’s create a template of the statement and break it down.

CREATE SECURITY POLICY <schema>.<name>
ADD <FILTER OR BLOCK> PREDICATE <Table-Valued Function>(<Field from table>) ON <schema>.<table>
WITH (STATE = <ON OR OFF>, SCHEMABINDING = <ON OR OFF>)

As with any SQL Server object, it needs to be named, and added to a schema, even if it’s in dbo. Next, define what type of policy is being implemented, either a filtering policy or a blocking policy. Filtering policies are used to filter SELECT statements, while blocking policies are used for INSERT/UPDATE/DELETE statements. In our case, we’re currently working with filtering SELECT statements. We’ll use the function created in Part 1 of this series, App.udf_UsersFilter, and “bind” it to the intended table, App.Users.

Finally, we need to decide whether or not to use schema binding. It is recommended to use SCHEMABINDING = ON, but it is not required. Should you choose not to use SCHEMABINDING, remember every user (or a group and/or role) will need to be given access to the predicate function, in addition to the table(s). If SCHEMABINDING is enabled, then users who have access to the table will have permission to the predicate function. For my database, I have no reasons to disable it, so I won’t and will leave it on. Once the security policy has been created in the database, our test queries from the previous blog can be modified and re-tested.

The image above has the relevant list of employees in our company, with each employee’s department name and the ID of their direct supervisor. As a refresher, or if you haven’t read Part 1 of this series, below is the predicate function we’ve defined for the App.Users table.

CREATE FUNCTION [App].[udf_UsersFilter]
(
    @UserID INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    -- Allow access to the user's own record
    SELECT 1 FilterResult
    FROM App.Users u
    WHERE u.UserID = @UserID
          AND USER_NAME() = u.Username
    UNION
    -- Allow access to the user's direct reports' records 
    SELECT 1 FilterResult
    FROM App.Users u
        LEFT JOIN App.Users m
            ON u.ManagerID = m.UserID
    WHERE u.UserID = @UserID
          AND USER_NAME() = m.Username
    UNION
    -- Allow access to all users for members of the HumanResources role
    SELECT 1
    FROM App.Users
    WHERE UserID = @UserID
          AND IS_ROLEMEMBER('HumanResources') = 1
);

Our first test is with our office manager, Peggy. Her user is able to see just her record, as she is not a manager, nor is she a member of the HumanResources database role (cases 2 and 3 of the predicate function defined in Part 1).

To query the App.Users table, we can just call the table and not have to worry about the predicate function or having a WHERE clause that filters records appropriately.

EXECUTE AS USER = 'PeggyM'

SELECT u.*
FROM App.Users u

The second test case is with my database user, ErinD. In this example, my record and my direct reports’ records are returned (cases 1 and 2 in the predicate function).

EXECUTE AS USER = 'ErinD'

SELECT u.*
FROM App.Users u

From here on out, anyone who queries the table will receive a filtered list of data from the App.Users table. IMPORTANTLY, this includes database administrators. DBAs are not able to view all of the data in the table, unless they disable the security policy (not recommended) or they belong to the HumanResources database role. The “god” role of sysadmin does not take priority over RLS.

The examples shown so far have been developed after quite a bit of trial and error. In the next blog in this series, we’ll discuss the DOs and DON’Ts when it comes to Row Level Security. We will then investigate improvements to our design to better handle real-world, enterprise implementations of RLS.