Row-level security in SQL server

Question

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'.

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'))
);

When I log in with the admin account, I can only see its own row, and cannot see rows with role='members'

I'm a bit confused: do you want to implement RLS for the user table, or for another table but based on the user table? Please show definitions for all relevant tables, also please show your CREATE SECURITY POLICY — Charlieface
– Charlieface, Commented Apr 25, 2024 at 18:54

Charlieface · Accepted Answer · 2024-04-25 19:08:34Z

You seem to be a bit confused: @username and @role represent the row that is being attempted to be viewed, whereas dbo.user in the function represents the row of the currently logged-in user.

So you need to remove @username = USER_NAME() as a predicate from the second and third branches, and shift around the rest into a set of OR predicates.

username = @username can be hoisted out as a single predicate. This will limit the query inside the function to just the single row for the current user, so we can determine their role.

CREATE OR ALTER 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] u
    WHERE u.username = @username
      AND (
         @username = USER_NAME()
         OR
         u.role = 'admins' AND @role = 'members'
         OR
         u.role = 'ceos'   AND @role = 'admins'
      )
;

Collectives™ on Stack Overflow

Row-level security in SQL server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related