Row-level security in SQL server

• dzukik0711

  Valued Member

  Points: 62

  More actions

  April 24, 2024 at 12:00 pm

  #4414970

  In this scenario, I seek assistance in implementing row-level permissions for a table named 'user' with columns 'username' and 'role'. The objective is to establish a security model where access to individual rows is controlled based on the role assigned to each user. Specifically, the permissions should adhere to the following criteria: role='members' can only view 1 row when @username=USER_NAME(), while role='admins' can view their own row when @username=USER_NAME() and rows with role='members'. For role='ceos', they can view their own row and rows with role='admins'.  When I log in with the admin account, I can only see its own row, and cannot see rows with role='members' . The same applies to the CEO account

  CREATE FUNCTION Security.fn_securitypredicate3
           (@username AS nvarchar(50), @role AS varchar(10))
  RETURNS TABLE
  WITH SCHEMABINDING
  AS
  RETURN (
      SELECT 1 AS Result
      FROM dbo.user
      WHERE 
          (@username = USER_NAME() AND @role = 'members' AND username = @username)
          OR 
          (@username = USER_NAME() AND @role = 'admins' AND (username = @username OR role = 'members'))
          OR 
          (@username = USER_NAME() AND @role = 'ceos' AND (username = @username OR role = 'admins'))
  );

• Perry Whittle

  SSC Guru

  Points: 234040

  More actions

  April 24, 2024 at 6:09 pm

  #4415105

  have you created a security policy, cant see code example below, might be worth reading this before you go any further

  https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs"