I'm trying to implement Row Level Security in SQL Server 2016.
The problem is, I can have multiple users that should have read permissions over given rows, and when I write some complex condition in the predicate the performance gets like very very very bad.
I tried to keep all usernames in one column of the table and in the predicate to search through them for the SYSTEM_USER
with % LIKE %
but performance is low.
Example of the values in the Usernames column in my controlled table for one row:
domain\john.wick;domain\red.eagle;domain\spartak.something....
Here is my function:
CREATE FUNCTION fn_securitypredicate(@Usernames AS nvarchar(4000))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 as Result
WHERE @Usernames LIKE '%' + SYSTEM_USER + '%'
With this execution time from 2 sec became 50 sec. Any suggestions for improvement.
CREATE SECURITY POLICY [Policy]
ADD FILTER PREDICATE [fn_securitypredicate]([Usernames])
ON [dbo].[Products];