THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

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

Published Sunday, August 13, 2017 6:12 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement