sql server - Secure table using row level security using data from the same table? - Stack Overflow

admin管理员组

文章数量:1336331

There is a current database which has two tables which map user names to users and contains flags for user rights. I'm skipping here all checks, constraints and similar stuff, leaving just bare bone tables:

CREATE TABLE [dbo].[User]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](50) NOT NULL,

    CONSTRAINT [PK_User] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserRight]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [User] [int] NOT NULL,
    [Subsystem] [int] NOT NULL,
    [Rights] [bigint] NOT NULL,

    CONSTRAINT [PK_UserRight] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]

These tables are then used within user defined function chain:

ALTER FUNCTION [dbo].[EffectiveUserRights](@UserId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN 
        (SELECT Subsystem, Rights 
         FROM dbo.UserRight 
         WHERE [User] = @UserId)

ALTER FUNCTION [dbo].[CurrentUserId]()
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Id int

    SELECT @Id = Id 
    FROM dbo.[User] 
    WHERE UserName = ORIGINAL_LOGIN()

    RETURN @Id
END

ALTER FUNCTION [dbo].[HasEffectiveUserRole](@Subsystem int, @Role int)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Result bit

    SELECT 
        @Result = CASE 
                      WHEN (Rights & @Role) = @Role 
                          THEN 1 
                          ELSE 0 
                  END 
    FROM 
        dbo.EffectiveUserRights(dbo.CurrentUserId()) 
    WHERE 
        Subsystem = @Subsystem

    RETURN COALESCE(@Result, 0)
END

ALTER FUNCTION [RLS].[UserSameOrEditor](@User int, @Subsystem int)
RETURNS TABLE 
WITH SCHEMABINDING
AS
     RETURN 
         (SELECT 1 AS 'Allow' 
          WHERE @User = dbo.CurrentUserId() 
             OR dbo.HasEffectiveUserRole(@Subsystem, 8) = 1)

Then this function is used in a row level security predicate (for more tables, but I left only the relevant ones):

CREATE SECURITY POLICY [RLS].[UserSecurity] 
ADD FILTER PREDICATE [RLS].[UserSameOrEditor]([Id],(0)) ON [dbo].[User],
ADD FILTER PREDICATE [RLS].[UserSameOrEditor]([User],(0)) ON [dbo].[UserRight],
WITH (STATE = ON, SCHEMABINDING = ON)
GO

This setup worked nicely for many years on SQL Server 2019 Standard without any issue, but recently we tried to upgrade it to SQL Server 2022 (for testing we used Express edition) and here the problems began. We are randomly getting an error

View or function 'dbo.EffectiveUserRights' contains a self-reference. Views or functions cannot reference themselves directly or indirectly.

It works most of the time, but seems to appear more often the longer SQL Server is left running.

I can understand that User and UserRights tables are secured using predicate which uses same tables, but I can't think of the way to fix this without leaving data in those table open for reading to everyone. What would be the proper way to secure these tables or whole database security needs to be redesigned somehow?

There is a current database which has two tables which map user names to users and contains flags for user rights. I'm skipping here all checks, constraints and similar stuff, leaving just bare bone tables:

CREATE TABLE [dbo].[User]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](50) NOT NULL,

    CONSTRAINT [PK_User] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserRight]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [User] [int] NOT NULL,
    [Subsystem] [int] NOT NULL,
    [Rights] [bigint] NOT NULL,

    CONSTRAINT [PK_UserRight] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]

These tables are then used within user defined function chain:

ALTER FUNCTION [dbo].[EffectiveUserRights](@UserId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN 
        (SELECT Subsystem, Rights 
         FROM dbo.UserRight 
         WHERE [User] = @UserId)

ALTER FUNCTION [dbo].[CurrentUserId]()
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Id int

    SELECT @Id = Id 
    FROM dbo.[User] 
    WHERE UserName = ORIGINAL_LOGIN()

    RETURN @Id
END

ALTER FUNCTION [dbo].[HasEffectiveUserRole](@Subsystem int, @Role int)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Result bit

    SELECT 
        @Result = CASE 
                      WHEN (Rights & @Role) = @Role 
                          THEN 1 
                          ELSE 0 
                  END 
    FROM 
        dbo.EffectiveUserRights(dbo.CurrentUserId()) 
    WHERE 
        Subsystem = @Subsystem

    RETURN COALESCE(@Result, 0)
END

ALTER FUNCTION [RLS].[UserSameOrEditor](@User int, @Subsystem int)
RETURNS TABLE 
WITH SCHEMABINDING
AS
     RETURN 
         (SELECT 1 AS 'Allow' 
          WHERE @User = dbo.CurrentUserId() 
             OR dbo.HasEffectiveUserRole(@Subsystem, 8) = 1)

Then this function is used in a row level security predicate (for more tables, but I left only the relevant ones):

CREATE SECURITY POLICY [RLS].[UserSecurity] 
ADD FILTER PREDICATE [RLS].[UserSameOrEditor]([Id],(0)) ON [dbo].[User],
ADD FILTER PREDICATE [RLS].[UserSameOrEditor]([User],(0)) ON [dbo].[UserRight],
WITH (STATE = ON, SCHEMABINDING = ON)
GO

This setup worked nicely for many years on SQL Server 2019 Standard without any issue, but recently we tried to upgrade it to SQL Server 2022 (for testing we used Express edition) and here the problems began. We are randomly getting an error

View or function 'dbo.EffectiveUserRights' contains a self-reference. Views or functions cannot reference themselves directly or indirectly.

It works most of the time, but seems to appear more often the longer SQL Server is left running.

I can understand that User and UserRights tables are secured using predicate which uses same tables, but I can't think of the way to fix this without leaving data in those table open for reading to everyone. What would be the proper way to secure these tables or whole database security needs to be redesigned somehow?

• sql-server
• sql-server-2022

Share Improve this question Follow edited Nov 19, 2024 at 19:33 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Nov 19, 2024 at 18:49 NodeNode 111 bronze badge 3

• Best I can tell, there is no self referencing. Have you added RLS to the USER or UserRight table or something? If so why? Why do the users need permission to SELECT from the object at all? Why not use permission chaining? – Thom A Commented Nov 19, 2024 at 19:04
• Why do you have a right setting on the UserRight table? It does seem a bit weird – siggemannen Commented Nov 19, 2024 at 19:45
• I would guess it's something to do with scalar UDF inlining. I would strongly suggest (anyway for performance) to change CurrentUserId and HasEffectiveUserRole to inline Table Valued Functions. – Charlieface Commented Nov 19, 2024 at 23:27

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

This trouble can be the result of the INLIGNING of the function that's by default. Try to create your function with a WITH clause that contains INLINE = OFF and Test it.

本文标签： sql serverSecure table using row level security using data from the same tableStack Overflow