Row Level Security and Indexed Views

I was giving my Row Level Security session last weekend for the Richmond User Group (@RichmondSQL http://rva.pass.org/) and a question came up (ok, so I technically asked the question). How does the feature work with indexed views?

Generally speaking, the answer turned out to be obvious. You can apply a RLS Security Policy to a view that has an index on it, but not to a table that is referenced by an indexed view.

Example code:

Create a table

CREATE SCHEMA Demo;
GO
CREATE TABLE Demo.SaleItem
(
    SaleItemId    int CONSTRAINT PKSaleIitem PRIMARY KEY,
    ManagedByRole nvarchar(15), --more typically would be sysname, but nvarchar(15) is easier to format for testing
    SaleItemType  varchar(10)
)
GO

And a very simple predicate function

CREATE OR ALTER FUNCTION rowLevelSecurity.ManagedByRole$SecurityPredicate_RLSView (@ManagedByRole AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING --if schemabound, users needn't have rights to the function
AS
    RETURN (SELECT 1 AS ManagedByRole$SecurityPredicate) ; --Works no matter what, for simplicity sake
GO

Next, create a view. Must be schemabound for RLS, and for an indexed view.

CREATE OR ALTER VIEW Demo.SaleItem_RLSView
WITH SCHEMABINDING
AS
    SELECT SaleItemId, ManagedByRole, SaleItemType
    FROM   Demo.SaleItem
WITH CHECK OPTION;
GO

Then add an index to the view:

CREATE UNIQUE CLUSTERED INDEX IndexedView ON Demo.SaleItem_RLSView (SaleItemId);

Now, attempt to add to table will fail:

CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
    ADD FILTER PREDICATE rowLevelSecurity.ManagedByRole$SecurityPredicate_RLSView(ManagedByRole)
            ON Demo.SaleItem
    WITH (STATE = ON); --go ahead and make it apply
GO

Msg 33265, Level 16, State 1, Line 42
The security policy 'rowLevelSecurity.Demo_SaleItem_SecurityPolicy' cannot have a predicate on table 'Demo.SaleItem' because this table is referenced by the indexed view 'Demo.SaleItem_RLSView'.

But you can still add to the view:

CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
    ADD FILTER PREDICATE rowLevelSecurity.ManagedByRole$SecurityPredicate(ManagedByRole)
             ON Demo.SaleItem_RLSView
    WITH (STATE = ON); --go ahead and make it apply
GO

Now the filter predicate will be applied to usage of the view, exactly like it is for a table (you cannot have a BLOCK predicates on any kind of view). This and a few more tweaks have been made to the Latest Version of the presentation code which can be accessed from the Dropbox folder link you can find on my webpage: http://www.drsql.org/presentations