DbUpdateConcurrency Exception Thrown for RLS Filtered User on Insert

user_16100742 0
Viewed 40

I am running into an issue where users that are enforced by RLS can do everything except insert new records and they receive a DbUpdateConcurrencyException.

If the user is a part of a group not filtered by RLS, no issue occurs. This can be replicated by moving myself into a db role that gets successfully filtered, but I am unable to add new records to the table.

Here is my TVF:

ALTER FUNCTION [RLS].[tvf_AssignmentsSecurityPredicate](@RlsArea INT, @BrandID SMALLINT, @ASM INT)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securityPredicate_result
            FROM RLS.AssignmentsRLS AS rls
            WHERE (rls.Email = SUSER_SNAME() AND
            (rls.AreaID = @RlsArea OR rls.BrandID = @BrandID OR rls.ASM = @ASM))
            OR (IS_MEMBER('all_admin') = 1
            OR IS_MEMBER('all_leadership') = 1
            OR IS_MEMBER('concierge') = 1
            OR IS_MEMBER('db_owner') = 1);

And the security policy:

ALTER SECURITY POLICY [RLS].[SecurityPolicies] 
ADD FILTER PREDICATE [RLS].[tvf_AssignmentsSecurityPredicate]([RlsArea],[BrandID],[Retailer_ASM]) ON [dbo].[tblPairings]
WITH (STATE = ON, SCHEMABINDING = ON)

Any ideas?

row-level-security azure-sql-database
1 Answers

After searching on SO, I found that it was a table trigger not returning scope_identity causing the issue.

I just added this to the end of my trigger to make it work. Still need to alter the full trigger to accept multi-row inserts, but this is the specific answer to my posted question.

SELECT RouteID
FROM tblPairings
WHERE @@ROWCOUNT > 0 AND RouteID = SCOPE_IDENTITY();

Answering in case anyone else runs into this and I hope it saves them some time!

user_161007420
Related