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