May 23, 2019 at 1:26 pm
...this is a 2nd attempt to post, as I can't seem to find the 1st from the day before...
I'm trying to implement row-level security in SQL Server 2016 but am getting stuck. Below is the code being used for the predicate function, security policy and an intermediate table. From what I can see, everything looks good, but when SSMS is showing the security policy is enabled, users who should have restricted access to the key dimension can see all of the data. A couple weeks ago, one of our users was correctly seeing filtered data, but when we came back to it late last week, the same user could now see all of the data, not just what was supposed to be filtered to them.
Predicate Function
CREATE FUNCTION [dbo].[udf_RLSDimAgentIDPredicate] (@DimAgentId int)
RETURNS table
WITH SchemaBInding
AS
RETURN (select max(fn_securitypredicate_result) fn_securitypredicate_result
from
(
--"Regular" Users: Does the AD Group they belong to have access to the incoming companyID?
SELECT 1 fn_securitypredicate_result
FROM Dim.Agent a
INNER JOIN dbo.AgentGroupMapping arm ON a.MasterAgentId = arm.MasterAgentId
WHERE IS_MEMBER(arm.ADGroupName) = 1 and a.DimAgentId = @DimAgentId
UNION
--"Power" Users and Admins: Do they belong to an AD Group that has access to all companies (designated by -1 in MasterAgentID in dbo.AgentGroupMapping)?
SELECT 1
FROM Dim.Agent a, dbo.AgentGroupMapping arm
WHERE IS_MEMBER(arm.ADGroupName) = 1 and arm.MasterAgentId = -1 and a.DimAgentId = @DimAgentId
) r
WHERE r.fn_securitypredicate_result is not null)
Security Policy
CREATE SECURITY POLICY [dbo].[DimAgentPredicate]
ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Dim].[Agent],
ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Fact].[WorkersCompClaim],
ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Fact].[WorkersCompPremium],
ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Fact].[WorkersCompProducer],
ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Fact].[WorkersCompRegister],
ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Fact].[WorkersCompQuoteSummary],
ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Fact].[WorkersCompExposure]
WITH (STATE = ON, SCHEMABINDING = ON)
The odd part that I can't get over is that when the security policy is disabled, any user who runs the following code gets the correct predicate result. As a power user, 1 is returned for every company in Dim.Agent. A user with restrictions to certain companies has 1's for just those companies with NULL being returned for everything they don't have access to.
SELECT a.MasterAgentID, p.fn_securitypredicate_result
FROM Dim.Agent a
CROSS APPLY dbo.udf_RLSDimAgentIDPredicate (a.DimAgentId) p
A question that regularly runs through my mind is if the security policy is disabled, when I think it should be enabled. One test I've been doing to verify is to run this same query. With the policy enabled, it fails, because the function is referencing itself.
Msg 4429, Level 16, State 1, Procedure udf_RLSDimAgentIDPredicate, Line 5 [Batch Start Line 28]
View or function 'dbo.udf_RLSDimAgentIDPredicate' contains a self-reference. Views or functions cannot reference themselves directly or indirectly.
Msg 4413, Level 16, State 1, Line 30
Could not use view or function 'dbo.udf_RLSDimAgentIDPredicate' because of binding errors.
Does anyone see issues in the code that would be preventing proper filtering? Or, has anyone encountered something like this in the past, and if so, what was the solution?
Thanks,
Erin
May 24, 2019 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 5, 2019 at 3:55 pm
Any ideas out there? I know it's an odd-ball sort of issue, but I have no clue where to go next for this implementation.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply