SQLServer Row level security - Enforce from within database

Question

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.

Answer 1

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.

Answer 2

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.

This is an Application [Code] Testing issue.

Checking that queries cannot access data to which they are not entitled should part of the Testing Plan (or, if the Developers claim to be "Agile", their "Definition of Done") and should be completed and signed off before the code is allowed to go Live.

Alternatively, is it possible to initially set the context to a meaningless value, say, at connection time? It's more likely for Developers will notice if their queries return no data at all when they've forgotten to set a proper context value.