August 16, 2021 at 3:27 pm
Here is the use case. We have a table (ctrl_parms) to contain application control parameters. We want to assure all records in the table are returned to system administrators but exclude returning records for non-system administrators.
We have created AD groups which have authorized users in them. The IS_MEMBER is being used to determine if a user is or is not authorized to retrieve records via an AD group. We also filter based on record content (via the ctrl_parm_is_pwd_ctrl_parm column).
The below code sample shows control table’s layout, the filter predicate, application of the security policy and explains the test results. It appears as if the filter predicate is not turn on but the security policy state is set to on.
The logic in the filter predicate has been validated. What is being missed? Assistance would be greatly appreciated. Thank you for our support in advance. Hopefully, we can make the code sample be a good tutorial when a resolution is found.
USE '<a_database_name>'
GO
-- The control parm table layout.
-- -- The is_pdw_ctrl_parm is being added.
CREATE TABLE dbo.ctrl_parms(
ctrl_parm_id int NOT NULL IDENTITY(1,1)
,ctrl_parm_is_pwd_ctrl_parm char(1) NOT NULL
,ctrl_parm_nm nvarchar(128) NOT NULL
,ctrl_parm_value nvarchar(512) NOT NULL
CONSTRAINT pk_ctrl_parms PRIMARY KEY CLUSTERED
(
ctrl_parm_id ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- The default for the is_pwd_ctrl_parm column.
ALTER TABLE [dbo].[ctrl_parms] ADD DEFAULT ('N') FOR [ctrl_parm_is_pwd_ctrl_parm]
GO
-- Load a filler record for test purposes for a control parm which will not contain a password.
INSERT INTO [dbo].[ctrl_parms] (
[ctrl_parm_is_pwd_ctrl_parm]
,[ctrl_parm_nm]
,[ctrl_parm_value]
)
VALUES ('N','a_control_parm_name_1','a_value_for_the_control_parm_that_is_not_password_related')
-- Append a new row to the control parm table which contains a control parm with a password.
-- -- We want RLS to exclude this record from being returned to users which are not system admins.
INSERT INTO [dbo].[ctrl_parms] (
[ctrl_parm_is_pwd_ctrl_parm]
,[ctrl_parm_nm]
,[ctrl_parm_value]
)
VALUES ('Y','a_control_parm_name_2','a_password_in_the_control_parm_value_column')
-- Drop existing policy and function if it exists.
IF EXISTS (
SELECT *
FROM sys.objects
WHERE NAME = 'fn_ctrl_parm_predic' AND type_desc = 'SQL_INLINE_TABLE_VALUED_FUNCTION'
)
BEGIN
DROP SECURITY POLICY IF EXISTS rls.ctrl_parm_access_policy
DROP FUNCTION [rls].[fn_ctrl_parm_predic]
END
GO
-- Create the dedciated security schema ( row level security [rls] ).
CREATE SCHEMA [rls]
GO
-- Create the predicate for the filter and the block.
CREATE FUNCTION [rls].[fn_ctrl_parm_predic]( @is_member varchar(128) )
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS accessResult
FROM dbo.ctrl_parms cp
WHERE
(
IS_MEMBER(@is_member) = 1 -- For qualified member, return all records.
)
OR
(
IS_MEMBER(@is_member) = 0 -- For non-qualified member, return a subset of records where the following condition is met.
AND cp.ctrl_parm_is_pwd_ctrl_parm = 'N'-- Only include records which do not contain a password in the control parm.
GO
-- Drop the security polifcy if it exists.
DROP SECURITY POLICY IF EXISTS rls.ctrl_parm_access_policy
GO
-- Create the security policy. The actual domane was changed to <the_domain>.
CREATE SECURITY POLICY rls.ctrl_parm_access_policy
ADD FILTER PREDICATE rls.fn_ctrl_parm_predic( '<the_doman>\<the_approved_ad_group>' ) ON dbo.ctrl_parms,
ADD BLOCK PREDICATE rls.fn_ctrl_parm_predic( '<the_doman>\<the_approved_ad_group>' ) ON dbo.ctrl_parms
WITH (STATE = ON, SCHEMABINDING = ON)
GO
-- When logged onto SSMS as sysadmin, let us evaluate RLS.
-- -- The first test will evaluate the first portion of the filter clause ( i.e. IS_MEMBER(@is_member) = 1 )
-- -- -- Observation 1
SELECT
[ctrl_parm_id]
,[ctrl_parm_is_pwd_ctrl_parm]
,[ctrl_parm_nm]
,[ctrl_parm_value]
FROM [<a_database_name>].[dbo].[ctrl_parms]
-- All rows returned as expected.
-- When login into test account as a non-system, let us evaluate RLS.
-- -- This time we are going to evaluate the second portion of the filter clause ( i.e. IS_MEMBER(@is_member) = 0 AND cp.ctrl_parm_is_pwd_ctrl_parm = 'N' )
-- -- -- Observation 2
SELECT
[ctrl_parm_id]
,[ctrl_parm_is_pwd_ctrl_parm]
,[ctrl_parm_nm]
,[ctrl_parm_value]
FROM [<a_database_name>].[dbo].[ctrl_parms]
-- All rows returned which is not expected and not desired.
-- -- Why did the fitler predicate / security policy not work successfully?
-- When the same RLS filtering logic is applied to the query and ran as an system admin what happens?
-- -- Observation 3
DECLARE @is_member varchar(128) = '<the_doman>\<the_approved_ad_group>'
SELECT *
FROM <a_database_name>.dbo.ctrl_parms cp
WHERE
(
IS_MEMBER(@is_member) = 1 -- For qualified AD member, return all records.
)
OR
(
IS_MEMBER(@is_member) = 0 -- For non-qualified AD member, return a subset of records where the following condition is met.
AND cp.ctrl_parm_is_pwd_ctrl_parm = 'N'-- Do not include records which are a password control parm.
)
-- The result set returned all records as expected.
-- When the same RLS filtering logic is applied to the query and ran as an non-system admin what happens?
-- -- Observation 4
DECLARE @is_member varchar(128) = '<the_doman>\<the_approved_ad_group>'
SELECT *
FROM dbo.ctrl_parms cp
WHERE
(
IS_MEMBER(@is_member) = 1 -- For qualified member, return all records.
)
OR
(
IS_MEMBER(@is_member) = 0 -- For non-qualified member, return a subset of records where the following condition is met.
AND cp.ctrl_parm_is_pwd_ctrl_parm = 'N'-- Do not include records which are a password control parm.
)
-- The result set runs as expected. As desired, the record with tne password is not returned.
-- The last two test results validate the filtering logic used in the filter predicate.
August 17, 2021 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply