RLS Does Not Bind Queries to List Partitioned Partitions

0 votes
0 answers
59 views
sql-server partitioning row-level-security
                          Each table in this database is partitioned with a column named SourceId, list partitioned such that SourceId= 'COKE' or 'PEPSI', etc.

RLS(Row Level Security) is being used such that each user will be 'COKE', 'PEPSI, etc. and that the user_name() is used to determine access to data in each table's security predicate.

The thought was that the RLS security predicate would force each query to be bound to a single partition, however, this turned out not to be the case.

All query plans are showing that every partition is being scanned. This seems to be because the RLS function is being applied as a row filter after the data has been fetched from the tables. Or in other cases, it is non-deterministic, therefore the partition can't be determined in the query plan.

    CREATE SECURITY POLICY [dbo].[u_tblGLScheduledTransactionsFilter] 
    ADD FILTER PREDICATE [dbo].[fn_SP_DatabaseName]([SourceID]) ON [dbo].[u_tblGLScheduledTransactions]
    WITH (STATE = ON, SCHEMABINDING = ON)
    GO

TVF used for RLS Security Policies

    CREATE FUNCTION [dbo].[fn_SP_DatabaseName](@SourceID AS Nvarchar(4000))
        RETURNS TABLE
    WITH SCHEMABINDING
    AS
        RETURN SELECT 1 AS fn_SP_DatabaseName_output
        -- Predicate logic
        WHERE(CONVERT(Nvarchar(4000),USER_NAME()) = @SourceID)
    	or  (CONVERT(Nvarchar(4000),USER_NAME()) = 'admin')
    GO

To get around this, the tables have been converted to view in such a way that each physical table has been renamed and a view has been created with the original table name. 

For example:

 1. tblContact is renamed to u_tblContact 
 2. View created named tblContact (see below)

This allows all existing sql calls to function and also binds each table and joined table to a single partition. 

    CREATE VIEW [dbo].[tblContact] AS  
    --This will allow all non admin calls to be bound to a single partition
    SELECT * FROM [u_tblContact] WHERE SourceID = user_name()
    GO

The problem with that is we are losing performance in some cases with queries that return many columns and having many joins (only when using views encapsulating tables though). There is no noticeable time difference between using tables the view encapsulation for smaller queries having just a few columns and joins.

Are there any other ways to force RLS to make use of the associated partition using user_name() or any tricks so we don't have to pass all queries through hacked out views?

    SELECT COUNT(*) FROM u_tblGLScheduledTransactions

    SELECT COUNT(*) FROM tblGLScheduledTransactions

Paste the plan posted. 

The first query calls the physical table and relies on RLS to filter the results. All partitions are read.

The second query calls the view, which places the partition value directly in the where clause. Still uses RLS but only one partition is read.

I think it has something to do with how the seek predicates are formulated. 

Tables structure semantics

    CREATE TABLE [dbo].[u_tblGLScheduledTransactions]
    (
    	  [GLScheduledTransactionID] INT NOT NULL
    	   ...  
    	, [SourceID] NVARCHAR(4000) NOT NULL
    	, CONSTRAINT [PK_tblGLScheduledTransactions] PRIMARY KEY NONCLUSTERED([GLScheduledTransactionID] ASC , [SourceID] ASC) ON psSourceID(SourceID)
    	, CONSTRAINT [CIX_tblGLScheduledTransactions] UNIQUE CLUSTERED ([SourceID] ASC, [GLScheduledTransactionID] ASC) ON psSourceID(SourceID)
    )
    ON psSourceID(SourceID)
    GO
    
    ALTER TABLE [dbo].[u_tblGLScheduledTransactions] SET (LOCK_ESCALATION = AUTO)
    GO
Asked by Ross Bush (683 rep)
Aug 14, 2024, 03:18 PM
Last activity: Aug 15, 2024, 12:32 AM
RLS Does Not Bind Queries to List Partitioned Partitions

Related Questions
