03 September 2023

2044 views

2 0

SQL Server Row Level Security Deep Dive. Part 2 – Setup and Examples

This is part of a series on Row Level Security by Ben Johnston. For the rest of the series, click here

The previous section in this series was an introduction to Row Level Security (RLS) and some use cases. This section focuses on basic setup of RLS, methods for implementing RLS and performance considerations with those implementations. The RLS access predicate is applied to every row returned to a client making performance a big factor in any design. Expensive lookups or other bad designs can hurt not only performance on that table but overall system performance if CPU or IO is heavily impacted.

Basics

Define the strategy based on business requirements

Implementing RLS is as much a business endeavour as technical. Without a business case to implement RLS, there is no reason for the extra effort and testing involved. This is where driving out business requirements and making sure the solution fits the problem is important. Non-technical members of the team or business partners likely won’t know what RLS is or if it should be used. Differential access to the same data, replacing systems (or proposed systems) with multiple reports based on user groups, and multi-tenant access are possible indicators that RLS may be a useful tool. There are always multiple ways to solve a problem. If RLS would simplify the design and make it more robust, that’s when I start to seriously consider it for a design. It does help if the business is aware of RLS and have used it in other projects or databases, but having the business essentially design the system is dangerous too. Use all of the information available during planning sessions and design the system that best fits the need of the business and the skills of the technical team.

The business requirements should include a method to determine the rows allowed based on groups of users. It can be a very broad definition, such as country or business division assigned to a user, or it can much more specific and elaborate. Whatever method is decided, there should be an automated method to enable that strategy in the database for each user. The method can be active directory groups or properties, SQL roles, application database tables, a front-end designed and coded for this purpose (in conjunction with configuration tables), or any other method determined by the business. The strategy needs to be translated to something accessible to the database engine. Several methods are listed in the official documentation and are discussed below, but the actual authorization groups used by the RLS access predicate will likely be stored in the SESSION_CONTEXT, a lookup table, or a SQL role.

An access predicate is defined for each table

The actual implementation of RLS happens with two database objects, an access predicate and a security policy. The access predicate is an inline table-valued function (TVF) and defines the authorization and lookup process. This table-valued function is applied to each row that would be returned to the user. It is evaluated and only allows the row to return if the defined criteria are met. This sounds like a very general description, but that’s because it is very general. The rows returned are based on the business requirements defined for your project. Each RLS implementation is a custom solution and requires the rules to be implemented in the access predicate.

The access predicate defines the security mechanism for each row. Lookup queries and joins can be used, but they have a performance cost. The column used for security is passed in as a parameter and must be available in the table or the system (i.e., @CountryCode). Rows are then filtered (for read operations) or blocked (for write operations), or both. The filtering process is defined by the security policy.

A security policy is defined for each table

The security policy is what ties the access predicate to the table, specifies the column to be used as the parameter in the predicate, indicates how data should be restricted (filter, block, or both), and if RLS is enabled for the table or not via the STATE value. It is the mechanism that enables or disables RLS at a table level. Remember that RLS is table specific and only applies to tables where it is applied. That’s the job of the security policy. Each table can have a maximum of 1 security policy assigned. It can be enabled for both options, filter and block, but only one policy can be on a table.

Examples

The following examples use the WideWorldImporters sample database. They show basic access predicates and security policies.

Example 1 – lookup table

The following example shows a lookup table getting used to perform the authorization process. This example uses a local table in a separate schema, RLS. This is more secure from a procedural standpoint since you can be sure that regular users don’t have access to the table and aren’t able to modify it as long as you don’t use db_datareader or db_datawriter.

CREATE SCHEMA RLS

AUTHORIZATION dbo

--Create the table to hold the RLS rules

CREATE TABLE RLS.UsersSuppliers (

UsersSuppliersID int NOT NULL CONSTRAINT PK_RLSUsersSuppliers PRIMARY KEY CLUSTERED identity

,UserID nvarchar(255) NOT NULL

,SupplierID int NOT NULL

)

CREATE UNIQUE NONCLUSTERED INDEX UNQ_RLSUsersSuppliers_NaturalKey

ON RLS.UsersSuppliers (

UserID

,SupplierID

)

--Test user without a login

CREATE USER RLSLookupUser WITHOUT LOGIN

--Grant SELECT access to the entire Purchasing schema

--Deny SELECT on the RLS schema

GRANT SELECT ON SCHEMA::Purchasing TO RLSLookupUser

DENY SELECT ON SCHEMA::RLS TO RLSLookupUser

--Grant the test user access to a single supplier ID

INSERT INTO RLS.UsersSuppliers (

UserID

,SupplierID

)

VALUES (

'RLSLookupUser'

)

--Simple access predicate with a lookup

CREATE FUNCTION RLS.AccessPredicate_SupplierID_PurchasingSuppliers(@SupplierID int)

RETURNS TABLE

WITH SCHEMABINDING

RETURN

SELECT

1 AccessResult

FROM RLS.UsersSuppliers US

INNER JOIN Purchasing.Suppliers PS

ON US.SupplierID = PS.SupplierID

WHERE US.SupplierID = @SupplierID

AND US.UserID = USER_NAME()

--Enforce the access predicate defined above

--Note that the STATE = ON

CREATE SECURITY POLICY RLS.SecurityPolicy_SupplierID_PurchasingSuppliers

ADD FILTER PREDICATE RLS.AccessPredicate_SupplierID_PurchasingSuppliers(SupplierID) ON Purchasing.Suppliers

,ADD BLOCK PREDICATE RLS.AccessPredicate_SupplierID_PurchasingSuppliers(SupplierID) ON Purchasing.Suppliers AFTER UPDATE

WITH (STATE = ON, SCHEMABINDING = ON)

Be sure to validate your RLS configuration. The test user above, RLSLookupUser, is shown doing this below. Note that it was executed in the context of a dbo account and no rows are returned.

--Show the current user name

SELECT USER_NAME()

--Rows returned with RLS applied

SELECT *

FROM Purchasing.Suppliers

--Change the user running the SELECT

EXECUTE AS USER = 'RLSLookupUser'

SELECT *

FROM Purchasing.Suppliers

--Revert back to the logged in user

REVERT

Notice that the SupplierID matches the ID in the script populating the RLS.UsersSuppliers table, 4.

Example 2 – lookup table with dbo override

This example differs in that it checks for the db_owner role. If found, all rows are returned. This is useful if it fits your use case. It makes it easier for DBAs to perform maintenance on the RLS tables and run ad-hoc statements to fix issues.

--Drop the security policy so the old access predicate has no references

DROP SECURITY POLICY RLS.SecurityPolicy_SupplierID_PurchasingSuppliers

--WITH (SCHEMABINDING=OFF)

--Drop the old access predicate so the new one can be created

DROP FUNCTION RLS.AccessPredicate_SupplierID_PurchasingSuppliers

CREATE FUNCTION RLS.AccessPredicate_SupplierID_PurchasingSuppliers(@SupplierID int)

RETURNS TABLE

WITH SCHEMABINDING

RETURN

SELECT 1 AccessResult

WHERE IS_MEMBER('db_owner') = 1

UNION

SELECT

1 AccessResult

FROM RLS.UsersSuppliers US

INNER JOIN Purchasing.Suppliers PS

ON US.SupplierID = PS.SupplierID

WHERE US.SupplierID = @SupplierID

AND US.UserID = USER_NAME()

--Enforce the access predicate defined above

--The same security policy is used

CREATE SECURITY POLICY RLS.SecurityPolicy_SupplierID_PurchasingSuppliers

ADD FILTER PREDICATE RLS.AccessPredicate_SupplierID_PurchasingSuppliers(SupplierID) ON Purchasing.Suppliers

,ADD BLOCK PREDICATE RLS.AccessPredicate_SupplierID_PurchasingSuppliers(SupplierID) ON Purchasing.Suppliers AFTER UPDATE

WITH (STATE = ON, SCHEMABINDING = ON)

Note that a UNION is used rather than an OR in the WHERE clause. This is a general tuning guideline and usually works better for all queries, not just RLS access predicates. Notice how running the SELECT as a dbo returns all rows, but the first user is still restricted.

--Show the current user name

SELECT USER_NAME()

--Rows returned with RLS applied

SELECT *

FROM Purchasing.Suppliers

--Change the user running the SELECT

EXECUTE AS USER = 'RLSLookupUser'

SELECT USER_NAME()

SELECT *

FROM Purchasing.Suppliers

--Revert back to the logged in user

REVERT

In contrast to the first example, all rows are returned for the dbo account.

Example 3 – Role member lookup

The next example is similar to the method used in the base implementation of the WideWorldImporters sample database. It has a dbo override and also checks for role membership.

--Create a role for each customer type

CREATE ROLE [CustomerType_Agent]

CREATE ROLE [CustomerType_Wholesaler]

CREATE ROLE [CustomerType_Novelty Shop]

CREATE ROLE [CustomerType_Supermarket]

CREATE ROLE [CustomerType_Computer Store]

CREATE ROLE [CustomerType_Gift Store]

CREATE ROLE [CustomerType_Corporate]

CREATE ROLE [CustomerType_General Retailer]

--The Gift Store type has associated records so it is used for this example

--All other types would have an associated user or you could

--assign a group or specific users to the various roles

CREATE USER RLSGiftStore WITHOUT LOGIN

ALTER ROLE [CustomerType_Gift Store] ADD MEMBER RLSGiftStore

ALTER ROLE db_datareader ADD MEMBER RLSGiftStore

--Add the access predicate.

--This shows two things

--1. The Lookup based on role member using the IS_ROLEMEMBER function

--2. The JOIN to the CustomerCategories table. This is not a direct lookup for the base RLS table

CREATE FUNCTION RLS.AccessPredicate_CustomerID_SalesCustomers(@CustomerID int)

RETURNS TABLE

WITH SCHEMABINDING

RETURN

SELECT 1 AccessResult

WHERE IS_MEMBER('db_owner') = 1

UNION

SELECT ISNULL(AccessResult,0)

FROM (

SELECT

1 AS AccessResult

FROM Sales.Customers C

INNER JOIN Sales.CustomerCategories CC

ON C.CustomerCategoryID = CC.CustomerCategoryID

WHERE C.CustomerID = @CustomerID

AND IS_ROLEMEMBER(N'CustomerType_' + CC.CustomerCategoryName,USER_NAME()) = 1) AccessPredicate

--Add the security policy

--This is straight forward, but also note that it uses the CustomerID

--and not the CustomerCategoryName. The name is a lookup in CustomerCategories

CREATE SECURITY POLICY RLS.SecurityPolicy_CustomerID_SalesCustomers

ADD FILTER PREDICATE RLS.AccessPredicate_CustomerID_SalesCustomers(CustomerID) ON Sales.Customers

,ADD BLOCK PREDICATE RLS.AccessPredicate_CustomerID_SalesCustomers(CustomerID) ON Sales.Customers AFTER UPDATE

WITH (STATE = ON, SCHEMABINDING = ON)

This only returns 50 rows out of the 663, which is correct based on RLS rules.

EXECUTE AS USER = 'RLSGiftStore'

SELECT *

FROM Sales.Customers

REVERT

As you can see, implementing RLS is relatively simple once you understand how everything is connected. Keeping it simple is important for maintenance and performance.

Authorization methods in RLS

The authorization method for RLS involves storing user information in a table or using another lookup strategy. The examples above show a lookup table and role membership for granting access, but any lookup can be used. This needs to defined after the business rules are determined and it is directly tied to that analysis. This storage or lookup strategy will determine how the access predicate functions. Complex methods will likely benefit with being stored in a table. This is also useful when users are allowed to directly query data themselves. When all access is managed through an application, SESSION_CONTEXT is a viable solution. It all depends on the business need, the current technical solutions in place, and the method that will be used to access the RLS protected data.

CONTEXT_INFO()

It is possible to use CONTEXT_INFO for RLS, but this is definitely not recommended. It is a clear security risk since any user can override their own information. It is also not possible to lock these values when they are set. In simple terms, it means a user can change or set RLS security if it uses CONTEXT_INFO. Do not use CONTEXT_INFO for RLS.

SESSION_CONTEXT()

SESSION_CONTEXT can be used for RLS and it is a secure solution when implemented correctly. SESSION_CONTEXT works with all versions of SQL, including Azure SQL Database and with Microsoft Fabric. The key to making SESSION_CONTEXT secure is setting the value to read only when it is created.

The following has a potential security gap:

1	EXEC sp_set_session_context 'RLSRegion', 'Central'

That gap is closed by using the syntax below. The last parameter, 1, is for the @read_only parameter:

1	EXEC sp_set_session_context 'RLSRegion', 'Central', 1

If a user tries to override the value after it has been set, they will get the following error. If the user opens a new session, SESSION_CONTEXT is not set, meaning a user could set it to any value.

The other crucial part is ensuring that the SESSION_CONTEXT is set for each user. If access is only allowed through an application, it is relatively easy to be sure it is set. If the SESSION_CONTEXT is not set, the user won’t have access. This is also a security gap if the user can run commands since they will be able to set it themselves to any value.

A logon trigger can be used for full instances of SQL Server, on-prem and managed instances in Azure. If implemented, it needs to be very lightweight without a complicated lookup. This will be run for every connection to the server, so it may not work well for servers supporting multiple applications and databases. Thorough testing is recommended.

Local lookup table / Synapse lookup table

Storing authorization groups in a table works well for very complex authorization groups, such as multiple tiers of access (i.e., city, state, country, region). Complex RLS schemes don’t work well during the query process unless they are simplified. In my example of city, state, country, region, it works better to store each city allowed by the user, even if they are assigned access at a higher level (i.e., country). This might result in thousands of rows for a single user in the RLS table, but a very fast lookup process and it eliminates multiple joins in the access predicate. This assumes proper indexes on both the tables protected by RLS and the authorization table. Even with this general recommendation, it is always good to test your specific scenario. It is always possible to take things to an extreme level that doesn’t work well. If it is difficult or not possible to expand a hierarchy, care must be taken in the access predicate to ensure performance is acceptable. Normal query performance guidelines should be followed. It is usually better to include a UNION statement, or multiple UNION statements, rather than using OR statements in a JOIN.

Storing the authorization groups in a table also works well when users are allowed to query data directly or when multiple tools or applications are allowed to hit a warehouse and it isn’t feasible to create separate views and stored procedures for each group. In other words, ad-hoc queries from the perspective of the query engine. It is much more difficult to force the SESSION_CONTEXT to be set for every connection in an ad-hoc scenario. Setting the SESSION_CONTEXT can be done with SQL Server or in an Azure managed instance with a logon trigger, but this is not supported in Azure SQL Database. If that is your environment or in your upgrade path, it isn’t an option and you will want to look at a table-based solution instead.

This will be revisited during the section dedicated to security mitigations, but there are a few things to keep in mind if storing RLS authorization groups in tables. Put the lookup table and any tables used to support the lookup table in a separate scheme inaccessible to regular users (the RLS schema in my examples above). Update rights should be very limited. This also means that built-in database roles shouldn’t be used for regular users either, including the usual db_datareader and db_datawriter.

SQL Roles

SQL Roles can be used as a lookup mechanism in access predicates. Simple lookups would work well with this pattern. Since active directory groups can also be members of roles, that makes a good pattern and is easy to maintain. The sample in WideWorldImporters uses this as one of several methods for lookups.

1 2	SELECT 1 AS AccessResult WHERE IS_ROLEMEMBER(N'db_owner') <> 0

Specific users

Specific users can also be targeted by access predicates. This may be a good method to ensure that your administrator account always has access. It can also be used in conjunction with other lookups. This method is also demonstrated in the WideWorldImporters sample.

1	ORIGINAL_LOGIN() = N'Website'

Staging tables

A common pattern for warehouses is to create staging tables where raw or partially processed data can be placed before it is fully curated and made generally available to end users. If this pattern is used, be sure to put the staging tables in a different database or separate schema. This segregated space should not be accessible by regular users, even for SELECT access. Consider using the DENY permission on the ETL schema in addition to not granting permission to the schema if the tables are in the same database as the processed tables. In large organizations it is easy for a security request to be granted inadvertently if it follows patterns for standard databases but shouldn’t be followed in your RLS enabled database.

Summary

Creating and setting up RLS is relatively easy once the business rules are clearly understood. It may be necessary to create and regularly populate configuration tables for this process or add other structures to the database, such as user roles. A single access point and security predicate is then added to a table for RLS to be enabled. There are a number of methods to perform the lookup, including local tables, SESSION_CONTEXT() or any other method that meets the business need. Keep security and performance in mind at each stage of design. Each method implementing RLS has potential security holes and potential performance issues. Testing and validation of each method is key to any RLS setup.