SQL Server : row-level security on table not enforced within another predicate function

Question

Consider the following: there are two tables, table A and table B. Implement RLS on table A.

Then, on table B implement RLS by linking table B to table A thru an inner join. Guess what, within the predicate function for table B the RLS for table A is ignored.

1. It appears, you need to reapply the RLS for table A in the predicate function for table B. Is this by design?

2. Is there some flag or "with" clause that can be set to enforce existing RLS when linking table B to table A within predicate function?

3. Am I doing something wrong?

SQL script to create & populate the tables.

CREATE TABLE [dbo].[Securities]
(
    [SecurityId] [int] NOT NULL,
    [Security] [varchar](50) NOT NULL,
    [IssueCurrency] [varchar](3) NOT NULL,

    CONSTRAINT [PK_Securities] 
        PRIMARY KEY CLUSTERED ([SecurityId] 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

CREATE TABLE [dbo].[Prices]
(
    [SecurityId] [int] NOT NULL,
    [PriceDate] [date] NOT NULL,
    [Price] [numeric](18, 4) NULL,

    CONSTRAINT [PK_Prices] 
        PRIMARY KEY CLUSTERED ([SecurityId] ASC, [PriceDate] 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

ALTER TABLE [dbo].[Prices] WITH CHECK 
    ADD CONSTRAINT [FK_Prices_Securities] 
        FOREIGN KEY([SecurityId]) REFERENCES [dbo].[Securities] ([SecurityId])
GO

ALTER TABLE [dbo].[Prices] CHECK CONSTRAINT [FK_Prices_Securities]
GO

INSERT INTO [dbo].[Securities] ([SecurityId], [Security], [IssueCurrency])
VALUES (1, 'Project Power', 'CAD'), (2, 'Fractured Ltd', 'AUD'),
       (5, 'Eddy Security', 'USD'), (6, 'Foxtrot', 'USD')
GO

INSERT INTO Prices ([SecurityId], [PriceDate], [Price])
VALUES (1, '2020-08-05', 13.2500), (1, '2020-08-06', 13.3500), (1, '2020-08-07', 13.0500),
       (2, '2020-08-05', 23.9500), (2, '2020-08-06', 24.1500), (2, '2020-08-07', 22.0500),
       (5, '2020-08-05', 105.1500), (5, '2020-08-06', 106.3500), (5, '2020-08-07', 105.0500),
       (6, '2020-08-05', 36.2500), (6, '2020-08-06', 36.3500), (6, '2020-08-07', 35.0500)

SQL Script to create predicate function & security policy.

/*
drop security policy if exists Policy_Securities
go 

drop function if exists dbo.rlsSecurities
go
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION rlsSecurities (@IssueCurrency varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN 
    SELECT 1 AS UserAccess
        WHERE
            (SESSION_CONTEXT(N'UserGroup') = 'Internal')  or
            (
                @IssueCurrency = 'USD'      --non-internal group can only view securities where IssueCurrency='USD'         
            )
go

CREATE SECURITY POLICY Policy_Securities
ADD FILTER PREDICATE dbo.rlsSecurities(IssueCurrency) ON dbo.Securities
WITH(STATE = ON)
go

EXEC sys.sp_set_session_context @key=N'UserGroup', @value='XYZ'

SELECT SESSION_CONTEXT(N'UserGroup')

SELECT * FROM Securities

SELECT s.Security, p.*
FROM Securities s
INNER JOIN Prices p ON s.SecurityId = p.SecurityId  --RLS on table Prices is enforced thru inner join.

SELECT * FROM Prices  --Currently no RLS on tblComp.

--Now I want to implement RLS on Prices as well.
drop security policy if exists Policy_Prices
go 

drop function if exists dbo.rlsPrices
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION rlsPrices (
    @SecurityId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN 

    SELECT 1 as UserAccess
        where @SecurityId in (select SecurityId from dbo.Securities)  --This doesn't work even though RLS on table Securities already exists.
        
        --where   --This works but it requires reapplying RLS on table Securities.
        --  (SESSION_CONTEXT(N'UserGroup') = 'Internal')  or
        --  (
        --      @SecurityId In (select SecurityId from dbo.Securities where IssueCurrency = 'USD')      --non-internal group can only view securities where IssueCurrency='USD'         
        --  )
go

CREATE SECURITY POLICY Policy_Prices
ADD FILTER PREDICATE dbo.rlsPrices(SecurityId) ON dbo.Prices
WITH(STATE = ON)

go
exec sys.sp_set_session_context @key=N'UserGroup', @value='XYZ'
select SESSION_CONTEXT(N'UserGroup')
select * from Prices    --RLS not enforced

Answer 1

You are not doing anything wrong.

It seems RLS does not take into consideration any existing policies on the tables referenced in the predicate table-valued function when the tvf is executed in the scope/enforcement of a policy. The tvf works fine when executed in a normal query (the policy of referenced table {ie. Securities} in the tvf is enforced):

--standalone...check the execution plan, predicate of Securities.PK_Securities index scan is the function/policy on currency='USD'
select p.* 
from Prices as p
cross apply dbo.rlsPrices(p.SecurityId)

If you check the execution plan for:

--filter policy on Prices
select * from Prices

You will only see a clustered index scan on Prices, since there is a FK from Prices to Securities (SecurityId) the securities table is not checked at all. Even if you remove the FK, the join between Prices & Securities misses any RLS predicate on Securities.

In short, filter policies are not nested/inherited/cascaded, whether by design or not .. it is not clear.