April 24, 2024 at 12:00 pm
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'))
);
April 24, 2024 at 6:09 pm
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" 😉
April 30, 2024 at 12:51 pm
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