Row-level security in SQL server

  • 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))
    RETURN (
    SELECT 1 AS Result
    FROM dbo.user
    (@username = USER_NAME() AND @role = 'members' AND username = @username)
    (@username = USER_NAME() AND @role = 'admins' AND (username = @username OR role = 'members'))
    (@username = USER_NAME() AND @role = 'ceos' AND (username = @username OR role = 'admins'))
  • have you created a security policy, cant see code example below, might be worth reading this before you go any further


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

  • This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply