Sample Header Ad - 728x90

How to By-Pass sysadmins or DB-owners from RLS policy

0 votes
0 answers
61 views
I have written RLS policies which will filter the users(impersonated from Tableau) logged in against a USER_ACCOUNT table inside the database, so in short if the logged in user have an entry in USER_ACCOUNT table only then the he\she can see their respective rows. All good as of now, but there is new requirement for one user to bypass RLS. When I tried adding condition to bypass db owners from RLS using below option, the dbowner is able to bypass but the other user queries are not just running in loop or not completing. Script:
CREATE FUNCTION colleague.fn_Security_user_accounts_table(@UserName AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN

SELECT 1 AS fn_Security_user_accounts_table -- Logic for filter predicate
WHERE @UserName = USER_NAME()
   or IS_ROLEMEMBER ('db_owner') = 1
Asked by vikranth reddy (1 rep)
Apr 18, 2024, 09:19 AM
Last activity: Apr 21, 2024, 11:43 AM