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,
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