Restricting Data Access with Row Level Security – Part 1

When starting a new company, and you’ve added at least one other staff member, one of the most important applications you can have is a time tracking system. Time tracking is even more important for a consulting company, so customers are billed appropriately and we get paid. Depending on the number of features of this system, there is likely to be personal information, such as hourly or monthly salary, perhaps a Social Security Number (in the U.S.), or other information that needs to be kept private for each employee.

Of course, the time tracking application will filter this data, as we build it, but we will have a lot of smart developers, who will know where the SQL server is and will likely have some level of access to it. As a result, we need to lock down the data in the database itself, not just in how the application queries and displays the data. As of SQL Server 2016, Microsoft has implemented Row Level Security, allowing DBAs to secure data, by limiting who can see specific records in a protected table.

Getting the hang of RLS took a little bit of time and experimenting. One of my sources of inspiration came from the great Steve Jones with one of his stairway articles on Row Level Security.

Components for Row Level Security

To implement Row Level Security, you will need the following objects in your database.

• Table needing to be secured
• Predicate function – needs to determine if the user has access to a record
• Security Policy – ties the table(s) to a predicate function

Predicate Function

The first step is to identify a table for which to enable RLS. You may have a table identified already, as you’re thinking about RLS. In this blog, we’ll be working with the Users table, shown in the diagram above. Other blogs will work with the TimeEntries table for RLS, as that table will need to be locked down.

The next step is to create a predicate function. This is a table-valued function that needs to return 1 value to show the user has access to the requested record in the table. Through the use of the security policy, which we’ll cover in a later blog, this function will be applied automatically to every query that calls the table…regardless of user or level of access to the database.

To filter the Users table the way we want, at least initially, the table-valued function below will fit the bill. The function has 3 union’d queries, each returning the value “1” for records a particular user has access to.

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
);

We’ll test each section of the function, running them as different users. Currently, we have three users we’ll test. In the first case, Peggy is our “office Mom,” or office manager, who keeps everyone in line but she doesn’t have any direct reports. The query returns just her record User record.

EXECUTE AS USER = 'PeggyM'

SELECT u.* FROM App.Users u
CROSS APPLY App.udf_UsersFilter(u.UserID) p

REVERT

I’m the President of the company, and Peggy and Jill report directly to me. Running the query with my login, three records are returned, my own record, as well as Peggy and Jill’s records. Sections 1 and 2 of the function are being tested.

EXECUTE AS USER = 'ErinD'

SELECT u.* FROM App.Users u
CROSS APPLY App.udf_UsersFilter(u.UserID) p

REVERT

The final test is for our Human Resources team, and Sarah Jones in particular. As a member of HR, she has the ability to see all employees, because she’s a member of the HumanResources database role.

EXECUTE AS USER = 'SarahJ'

SELECT u.*
FROM App.Users u
CROSS APPLY App.udf_UsersFilter(u.UserID) p

REVERT

To this point, we’ve written our filtering predicate function for the App.Users table. This is great, but we shouldn’t have to worry about this function and applying it each and every query that hits the Users table. Besides myself, as the developer of the function, any other developers don’t need to be aware of the function, but we need to filter the table each and every time.

Tying the function to the table will be in the next blog. Stay tuned…