SQLServer Row level security - Enforce from within database

Have a multi-tenant app running on a single SQL Server 2016 db. I have a RLS (row level security ) predicate on customer_id (unique for each tenant) and the column exists in all tables.

CREATE FUNCTION dbo.[RLSPredicate] (@CustomerId bigint)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS RLSPredicateResult WHERE (@CustomerId = 
CAST(SESSION_CONTEXT(N'customer_id') AS bigint))

GO

 -- two table example
 CREATE SECURITY POLICY [dbo].[RLSPolicy] 
 ADD FILTER PREDICATE [dbo].[RLSPredicate] ([customer_id]) ON [dbo].[user],  
 ADD FILTER PREDICATE [dbo].[RLSPredicate] ([customer_id]) ON [dbo]. 
  [user_email]
  WITH (STATE = ON, SCHEMABINDING = ON)
  GO

  -- Sample Query 
  EXEC sp_set_session_context @key=N'customer_id', @value='1231312'
  select top 1000 * from [user] where customer_id = 1231312

With the above design, EXEC sp_set_session_context always needs to be executed before any user query hits the database, however we have had cases where the application team forgot to add code EXEC sp_set_session_context in new projects.

The problem is that the there is no way to guarantee that queries coming to the db always have EXEC sp_set_session_context in them. The queries come in all forms, EXEC, sp_executesql, adhoc etc.

I would like to know if there is any way to redesign the filter predicate so that RLS is enforced from within the database and remove dependency of running EXEC sp_set_session_context before every query.

There are more than 500K customer_id values.

I think you should use a login trigger and SET CONTEXT_INFO which quoting BoL


Associates up to 128 bytes of binary information with the current session or connection.

Then you can retrieve the data by calling the function with a select

i.e. SELECT CONTEXT_INFO()

You can then use the data recalled from the connection with Row Level Security to implement the filtering you desire.

If you are running SQL Server 2016 or later then you should use the more modern session_context() function instead - see Phase out CONTEXT_INFO() in SQL Server 2016 with SESSION_CONTEXT() by Aaron Bertrand.

StackExchange Database Administrators Q#223401, answer score: 5