Row-Level Security: Performance and common patterns
This post demonstrates three common patterns for implementing Row-Level Security (RLS) predicates:
- Rows assigned directly to users
- Row assignments in a lookup table
- Row assignments from a JOIN
In addition, this post shows how RLS has performance comparable to what you’d get with view-based workarounds for row-level filtering. The benefits of using RLS instead of views include:
- RLS reduces code complexity by centralizing access logic in a security policy and eliminating the need for an extra view on top of every base table
- RLS avoids common runtime errors by requiring schemabinding and performing all permission checks when the policy is created, rather than when users query
- RLS simplifies application maintenance by allowing users and applications to query base tables directly
To demonstrate the three common patterns, we’ll use RLS to filter rows in a Sales table based on increasingly complex criteria. To enable reasonable performance comparisons, we've populated this table with 50,000 rows of random data.
Pattern 1: Rows assigned directly to users
The simplest way to use RLS is to assign each row directly to a user ID. A security policy can then ensure that rows can only be accessed by the assigned user. As described in Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security, it is common to use CONTEXT_INFO to store the user ID connecting to the database, and use RLS to filter out rows whose assigned user ID does not match.
In this example, we create a security policy that filters our rows whose SalesRepId does not match CONTEXT_INFO (using the appropriate type conversions):
CREATE FUNCTION rls.staffAccessPredicateA(@SalesRepId int) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS accessResult WHERE CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = @SalesRepId -- @SalesRepId (int) is 4 bytes go CREATE SECURITY POLICY rls.staffPolicyA ADD FILTER PREDICATE rls.staffAccessPredicateA(SalesRepId) ON dbo.Sales go
We could have achieved equivalent functionality using a view:
CREATE VIEW vw_SalesA AS SELECT * FROM Sales WHERE CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = SalesRepId go
Now SELECT * FROM Sales with RLS enabled returns the same results as SELECT * FROM vw_SalesA without RLS enabled. Moreover, if we examine the Actual Execution Plans for both queries using SSMS, we see that the query optimizer has chosen a very similar plan for both. Sometimes the RLS plan will be slightly better, other times it will be slightly worse. The specific plan can depend on a multitude of exogenous factors, but in general the plans for RLS and for views will be very similar. (Note: We’ll ignore the missing index recommendations here, since these queries are artificially simple and the recommendations would have us place an index on every column.)
Pattern 2: Row assignments in a lookup table
A slightly more complex way to use RLS is to filter rows by looking up assignments in a helper table. For instance, we might have a helper table (“RegionAssignments”) mapping users to Regions. In our filtering logic, we can look up whether the current user should have access to each row based on the assignments stored in RegionAssignments. If one or more rows in RegionAssignments match the criteria, the corresponding row in the base table will be visible:
CREATE FUNCTION rls.staffAccessPredicateB(@Region nvarchar(50)) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS accessResult FROM dbo.RegionAssignments WHERE CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = SalesRepId AND Region = @Region go CREATE SECURITY POLICY rls.staffPolicyB ADD FILTER PREDICATE rls.staffAccessPredicateB(Region) ON dbo.Sales go
Or equivalently, with a view:
CREATE VIEW vw_SalesB AS SELECT Sales.* FROM Sales, RegionAssignments WHERE CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = RegionAssignments.SalesRepId AND Sales.Region = RegionAssignments.Region go
Again, selecting from Sales with RLS enabled yields the same rowset as selecting from vw_SalesB without RLS enabled. In this particular case, the query plans are identical:
Pattern 3: Row assignments from a JOIN
A more complicated RLS pattern is to look up row assignments by joining multiple helper tables in the filtering logic. For instance, we might have one helper table (“RegionAssignments”) mapping users to Regions, and another (“DateAssignments”) mapping users to a StartDate and an EndDate. To filter so that users can only see rows in their assigned region and date interval, we could create the following predicate function and policy:
CREATE FUNCTION rls.staffAccessPredicateC(@Region nvarchar(50), @Date date) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS accessResult FROM dbo.RegionAssignments ra INNER JOIN dbo.DateAssignments da ON ra.SalesRepId = da.SalesRepId WHERE CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = ra.SalesRepId AND @Region = ra.Region AND @Date >= da.StartDate AND @Date <= da.EndDate go CREATE SECURITY POLICY rls.staffPolicyC ADD FILTER PREDICATE rls.staffAccessPredicateC(Region, Date) ON dbo.Sales go
Or equivalently, with a view:
CREATE VIEW vw_SalesC AS SELECT Sales.* FROM Sales, RegionAssignments INNER JOIN DateAssignments on RegionAssignments.SalesRepId = DateAssignments.SalesRepId WHERE CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = RegionAssignments.SalesRepId AND Sales.Region = RegionAssignments.Region AND Sales.Date >= DateAssignments.StartDate AND Sales.Date <= DateAssignments.EndDate go
Once again, selecting from the base table with RLS enabled yields the same rowset as selecting from the view without RLS enabled. And once again, in this case the query plans are identical:
RLS allows you to implement filtering logic of arbitrary complexity; however, there are a handful of particularly common patterns as shown in this post. In general, RLS will have performance comparable to what you’d get if using views, while affording a number of benefits around security, maintenance, and convenience.
We’ll have more guidance around performance in future blog posts, but for now we recommend the following best practices:
- Avoid joining too many helper tables in your predicate function: the more joins you have, the worse the performance.
- If your predicate does a lookup in a helper table, try to put an index on the lookup column.
- Avoid using disjunctions (logical OR) in your predicate where possible, as there is a known performance issue described in Row-Level Security for Middle-Tier Apps – Using Disjunctions in the Predicate.