Dynamic data masking by using a dynamic row filter in SSAS Tabular

Question

I am trying to implement a way to use Dynamic Data Masking on a SSAS Tabular Import. By default this is not supported on SQL Server Analysis Services 2016 (as far as I know). I think I have found a way to implement this anyway using a Dynamic Row Level Security. However, my results are not as expected.

My case is an Employee table. It contains roughly 2000 rows, my test user is authorized to see about 50. I have imported the Employee table, added an Security table (EmployeeKey, Username) and set a row filter on the Employee table. More or less in the same way as the sample from Microsoft.

Additionally I have added a table named EmployeeMask which contains the same 2000 rows, with the same EmployeeKeys and the same columns. However, I have 'masked' the values of the remaining columns.

Using an MDX query impersonated as my test user I retrieve 50 employees from my Employee table and 2000 employees from my EmployeeMask table. So far so good. Now comes the part where I thought I was clever. I renamed my Employee table EmployeeAuthorized and added a calculated table Employee as follows:

=UNION(
    EmployeeAuthorized; 
    FILTER(
        EmployeeMask; 
        LOOKUPVALUE(EmployeeAuthorized[EmployeeKey]; EmployeeAuthorized[EmployeeKey]; EmployeeMask[EmployeeKey]) 
            = BLANK()
    )
)

However, using a MDX query against my new calculated table Employee I get all 2000 rows unmasked. My guess is that the calculated table gets processed beforehand, whereas I thought the DAX-formula would be execute on request. Is my guess right? Any way to fit my requirement?

Sample data:

CREATE TABLE [dbo].[Employee] (--Renamed this to EmployeeAuthorized
    [EmployeeKey] INT NOT NULL,
    [EmployeeName] VARCHAR(50) NOT NULL
);

INSERT INTO [dbo].[Employee] VALUES
    (1, 'A A'), (2, 'B B'), (3, 'C C');

CREATE TABLE [dbo].[Fact] (
    [EmployeeKey] INT NOT NULL,
    [Value] INT NOT NULL
);

INSERT INTO [dbo].[Fact] VALUES
    (1, 1), (2, 2), (3, 5), (3, 8);

CREATE TABLE [dbo].[Security] (
    [EmployeeKey] INT NOT NULL,
    [UserName] VARCHAR(50) NOT NULL
);

INSERT INTO [dbo].[Security] VALUES
    (1, 'domain\u1'), (2, 'domain\u1'), (3, 'domain\u1'),
    (1, 'domain\u2');

And if needed:

CREATE TABLE [dbo].[EmployeeMask] (
    [EmployeeKey] INT NOT NULL,
    [EmployeeName] VARCHAR(50) NOT NULL
);

INSERT INTO [dbo].[EmployeeMask] VALUES
    (1, 'masked'), (2, 'masked'), (3, 'masked');

I'm trying to accomplish the following:

vvvv4d vvvv4d · Accepted Answer · 2020-09-08T16:16:55

Ditch UNION it ignores filter context and your RLS. Use LOOKUPVALUE to make sure the user has access to the rows on EmployeeMasked based on if their EmployeeKey exists on EmployeeAuthorized
Edit your EmployeeAuthorized table so it contains a ADUsername column. For each EmployeeKey, store their ADUsername value that would be returned from the dax function USERNAME()
Edit the Role you created and go to the Row Filters tab.
Find the EmployeeMask table
Add the following DAX filter so the Role enforces users who belong to the role can only see rows on EmployeeMask if their EmployeeKey/ADUsername has a corresponding lookup authorization records in EmployeeAuthorizedy.

      =EmployeeMask[EmployeeKey]
        =LOOKUPVALUE(
            EmployeeAuthorizedy[EmployeeKey],
            EmployeeAuthorized[EmployeeKey], EmployeeMask[EmployeeKey],
            EmployeeAuthorized[ADUsername], USERNAME()
        )

Dynamic data masking by using a dynamic row filter in SSAS Tabular

2 Answers