Weird behaviour with row level security policy

• gmauriello

  SSC Rookie

  Points: 32

  More actions

  October 18, 2022 at 1:12 pm

  #4100959

  Hi guys,

  I am trying to figure out what is wrong with this implementation. I have a table called "POSTS" with 32 thousands rows inserted.

  This table has a column called "OWNER" which indicates the company of the user who inserted that post.

  I've created a security row policy which evaluates the rows and returns only the rows of the current logged user's company.

  The security level function I created is structured as follow

  CREATE FUNCTION [RLS].[fn_SecureOwnerData](@ownerId AS int)

  RETURNS TABLE

  with SCHEMABINDING

  AS

  RETURN SELECT 1 AS 'secured_row'

  WHERE (@ownerId = (SELECT TOP 1 owner FROM dbo.users where users.username = CURRENT_USER));

  The problem is that if the current user has an user name which ends with the letter "Z" every query made on the table "posts" hangs for about 3 minutes. If the user name does not end with a "Z" the query works perfectly.

  I don't know what to say, what can it be?

  Thanks

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738140

  More actions

  October 18, 2022 at 3:20 pm

  #4101006

  I have no idea. Can you repro in another db and share some DDL?