0

Have a multi-tenant app running on a single sql 2016 db, I have a RLS (row level security ) predicate on customer_id (which is 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 queries etc.

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.

Also there are more than 500K customer_id values.

Thanks!

  • If you cannot differentiate them by the session context, then how can you differentiate between them? If you expect SQL Server to be able to differentiate between your users, you need to have some way to express that, so that SQL Server have something to work with. Then it is just a matter of expressing that inside your function. – Tibor Karaszi Nov 26 '18 at 15:14
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.