Row Level Security update block predicate not working

0
votes

Consider having the following tables:

Client table with columns:

  • client_guid(uniqueidentifier - primary key)
  • client_description

Ticket table with columns:

  • ticket_guid(uniqueidentifier - primary key)
  • ticket_client_guid(uniqueidentifier - foreign key to client table)
  • ticket_description.

Consider having the following update predicate function which is supposed to allow clients to update only their own tickets:

CREATE FUNCTION [Security].[fn_ticket_edit](@ticket_guid AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT 1 AS fn_securitypredicate_result
WHERE CAST(SESSION_CONTEXT(N'client_guid') AS uniqueidentifier) =
    (SELECT ticket_client_guid
     FROM dbo.ticket
     WHERE ticket_guid = @ticket_guid)

Then we have the security policy applying the predicate fn:

CREATE SECURITY POLICY [Security].[ticket_security] 
ADD BLOCK PREDICATE [Security].[fn_ticket_edit]([ticket_guid]) ON [dbo].[ticket] BEFORE UPDATE 
WITH (STATE = ON, SCHEMABINDING = ON)

Problem: Client being able to reassign a ticket (change ticket_client_guid) to him which belongs to somebody else. Note: The security works perfectly fine and as expected if some of the other ticket fields is updated.

Question: Is this behavior expected and is there a workaround for this problem ?

Note: I tried playing with both before and after update statements and none of them worked out as expected when trying to update the column referenced in the predicate fn (ticket_client_guid).

1
sql-serverrow-level-security

1 Answers

1
votes

From https://docs.microsoft.com/en-us/sql/t-sql/statements/create-security-policy-transact-sql?view=sql-server-ver15#permissions

Block predicates are evaluated after the corresponding DML operation is executed. Therefore, a READ UNCOMMITTED query can see transient values that will be rolled back.

If anybody updates the ticket_client_guid to their id, the predicate will use that new value for the security check if you query the table for it.

I think you need to use the value passed in to the function, so you might have to use the other guid so it can be passed with the original value. Maybe

CREATE FUNCTION [Security].[fn_ticket_edit](@ticket_client_guid AS uniqueidentifier) 
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT 1 AS fn_securitypredicate_result
WHERE CAST(SESSION_CONTEXT(N'client_guid') AS uniqueidentifier) = @ticket_client_guid

CREATE SECURITY POLICY [Security].[ticket_security] 
ADD BLOCK PREDICATE [Security].[fn_ticket_edit]([ticket_client_guid]) ON [dbo].[ticket] BEFORE UPDATE 
WITH (STATE = ON, SCHEMABINDING = ON)