2

I am trying to create an index on a view that references a table with a masked column (SQL Server 2016). The masked column is not the only one in that table, and it's not used in the view.

create unique clustered index [IX_Name]
on dbo.vw_ViewName(SomeUniqueId)

I get this error:

The index on view 'dbo.vw_ViewName' cannot be created because the view is referencing table 'dbo.TableName' with masked columns.

In another environment, where masking is not enabled, the index is created successfully.

I looked through about four pages of Google results and haven't found any sensible description of the error. I would appreciate any knowledge transfer on the error and why it is not possible to create such index.

Here is some SQL to reproduce the issue:

drop view if exists dbo.vw_Aggregate
drop table if exists dbo.MainTable, dbo.SecondaryTable
go

create table dbo.MainTable
(
    MainTableId uniqueidentifier primary key,
    SomeExternalId uniqueidentifier,
    SecondaryTableId uniqueidentifier
)
go

create table dbo.SecondaryTable
(
    SecondaryTableId uniqueidentifier primary key,
    CreatedOn datetime,
    Amount decimal(19, 8),
    -- the below column produces error,
    -- if commented out - there is no error
    [Description] nvarchar(max) masked with (function = 'default()'),
    Dummy int
)
go

create view dbo.vw_Aggregate with schemabinding
as
    select AggregateId = m.MainTableId,
           m.SomeExternalId,
           s.CreatedOn,
           s.Amount
    from dbo.MainTable m
    inner join dbo.SecondaryTable s on s.SecondaryTableId = m.SecondaryTableId
go

create unique clustered index [IX_dbo.vw_Aggregate(AggregateId)]
on dbo.vw_Aggregate(AggregateId)
go
1
  • 1
    Speculation: this interferes with view expansion, where the optimizer might decide to use an indexed view rather than the table for better performance. With masked columns, it would also be required to check that this optimization is safe, for every query, based on the user's permissions, or "copy" the mask restrictions from the base table to the view before the other logic, or disable view expansion altogether. Rather than pay the cost for any of these, the engineers opted to ensure these features never interact by forbidding any combination of indexed views with dynamic security and masking. Jan 29, 2018 at 12:44

2 Answers 2

6

As far as I know, this is not supported, and you can only use tables in this case.

As found in following article which is using Row level security which is not the same as Dynamic Data masking, but it might be linked to each other.

https://www.mssqltips.com/sqlservertip/4005/sql-server-2016-row-level-security-limitations-performance-and-troubleshooting/ by Aaron Bertrand:

Indexed Views

Row-Level Security is not compatible with tables that have indexed views. If you try to create an indexed view against a table with a security policy, you will get the following error:

Msg 33266, Level 16 The index on the view 'view' cannot be created because the view is referencing table 'table' that is referenced by a security policy.

And no, you can't chicken-and-egg this to work around it; if a table is referenced by an indexed view, a security policy cannot be applied:

Msg 33265, Level 16 The security policy 'policy' cannot have a predicate on table 'table' because this table is referenced by the indexed view 'view'.

Note: You may come across the same types of problems if trying to apply a security policy to a table that is reference by a partitioned view.

As Dynamic Data Masking & Row Level Security were released in the same release maybe the limitations are linked to each other.

The Error

The error also clearly says that it is not allowed to have an index on a view linked to a table with a masked column. In the documentation however nothing can be found about it.

So my guess is that it is just not supported.

6
  • 1
    Hey, I resemble those comments! :-) Jan 29, 2018 at 12:42
  • Let me just quickly edit the author of that Article :) Jan 29, 2018 at 12:44
  • Thank you for the answer! I will think how to work around this. Will likely have to duplicate some data. Jan 29, 2018 at 12:56
  • 1
    @DmitriTrofimov Could you force read access to the table (or at least to that column) through a view (not the indexed view)? You could mask the column yourself in the view, defeats the purpose of dynamic data masking, but allows you to continue using your indexed view without adding the complexity of making copies of your data. Perhaps you could also add a computed column that has some kind of masking applied, and deny access to the real column to those without masking privilege (or however you want to apply perms). I think there are several workarounds that are viable (but not perfect). Jan 29, 2018 at 13:03
  • 1
    @StijnWynants Haha, I wasn't looking for credit, I just didn't notice it was my blurb until I got to "chicken-and-egg." I thought, hey, that's something I would say... oh, wait a minute... Jan 29, 2018 at 13:04
3

While the docs are light on this, I suspect Stijn is right, that the limitations on Dynamic Data Masking are the same as Row-Level Security (and probably Always Encrypted, too). Sounds like they were applied like a blanket, and I'd have to think more on why certain combinations of features are not supported.

In the meantime, a couple of potential workarounds:

  1. Force read access by <whoever can't read the unmasked data> of the table (or at least that column) through a view, separate from the indexed view. The view can handle the masking, but you'll have to implement this instead of relying on the built-in feature.

  2. Create a computed column that handles the masking (again, you'll have to implement this part), and deny read access to the source column, again by <whoever can't read the unmasked data>.

I haven't tried these, but on the surface, they sound simpler than making copies of your data (just to mask them) and then maintaining those...

1
  • 1
    Than you for suggestion! :) My team and I will discuss possible solutions. As far as I know there going to be a lot more masked columns in our system. But the number of places where we would need an indexed view is rather limited. So we will likely crutch up some indexed view replacement rather than replace masked columns. Jan 29, 2018 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.