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

Answer 1

Role should probably not be a parameter to the security-predicate function, but be take from the User table for the user. And you probably don't need @username as parameter either, as you have USER_NAME().

But it could help if you posted the complete scenario with CREATE TABLE + INSERT of sample data + CREATE SECURITY POLICY + the function.

Answer 2

Hi @Lộc Nguyễn

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

OR (@username = USER_NAME() AND @role = 'admins' AND (username = @username OR role = 'members'))

The logic of the filter condition is kind weird. How could the role be both 'admins' and 'members'?

You could convert the roles to role_levels (like 1,2,3) for better filtering. Try this:

CREATE FUNCTION Security.fn_securitypredicate3
         (@username AS nvarchar(50), @role AS varchar(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @Role_lvl INT
  SET @Role_lvl = CASE @role WHEN 'members' THEN 1 
                             WHEN 'admins' THEN 2 
							 WHEN 'ceos' THEN 3 END 
  RETURN 
(
    SELECT 1 AS Result
    FROM (SELECT *,CASE role WHEN 'members' THEN 1 WHEN 'admins' THEN 2 WHEN 'ceos' THEN 3 END AS Row_Level FROM dbo.user)T
    WHERE @username = USER_NAME()
      AND (T.username = @username OR Row_Level < @Role_lvl)
) 
END

This query might not work because I do not know your tables like. Hope this point you in the right direction.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".