Today, I worked on a service request that remember me the impact that we could have using row level security afterwards that our databases has been developed.
A customer reported a performance issue after implementing the row level security.
Current situation
Our customer have the following design:
- A table where we have the LogData, that contains data and group code.
[InsertID] [bigint] NOT NULL,
[ID] [varchar](100) NOT NULL,
[groupcode] [varchar](255) NOT NULL,
CONSTRAINT [PK_LogData_current] PRIMARY KEY CLUSTERED
([ID] ASC) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
- I have a table relation with group_user that contains per user that groups that belongs to
create table dbo.group_user ([groupcode] varchar(255),username varchar(128))
INSERT INTO dbo.group_user VALUES('G1',1)
Insert some rows in the table LogData to have some data to play
DECLARE @n as integer = 0
WHILE(@n<=100000)
begin
SET @n=@n+1
insert into LogData ([InsertID], [ID]) values(@N,@N)
end
Query Execution before adding row level security
- When I run the query: SELECT MAX(INSERTID) FROM LogData the execution plan looks like normal based on the index that I have:
Row Level Security Implementation
- I created the function:
CREATE FUNCTION dbo.[fn_securitypredicate](@groupcode AS varchar(255))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @groupcode IN (
SELECT dbo.group_user.[groupcode]
FROM dbo.group_user
WHERE dbo.group_user.username =
CASE
WHEN is_member('G1') = 1 THEN CAST('G1' AS nvarchar(128))
WHEN is_member('G2') = 1 THEN CAST('G2' As nvarchar(128))
WHEN is_member('G3') = 1 THEN CAST('G3' As nvarchar(128))
ELSE user_name()
END);
- I created the security policy:
CREATE SECURITY POLICY dbo.[LogData_Check]
ADD FILTER PREDICATE dbo.[fn_securitypredicate]([groupcode]) ON dbo.Logdata
WITH (STATE = ON, SCHEMABINDING = ON)
GO
- When I executed the query SELECT MAX(INSERTID) FROM LogData , I found a different execution plan.
- I found that SQL Server will execute a Clustered Index Scan instead of Index Scan. Why? because it needs to access the groupcode to check the security predicate.
Workaround to have the same execution plan
- Modify or create an index that contains the groupcode on it, either adding as a sorted column or included column.
create index LogData2 on [dbo].LogData ([InsertiD],[groupcode])
create index LogData3 on [dbo].LogData ([InsertiD]) include ([groupcode])
- When I executed the query SELECT MAX(INSERTID) FROM LogData , I found a very similar execution plan to the first one using an index scan, because the index contains the field to check the security function.
Enjoy!