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.