RLS - Analyze in Excel connection

[jdusek92]

Hello,

I have successfully set up row level security using this Table filter DAX expression:

SEARCH( userprincipalname(), [Email], 1, 0 ) > 0

I am now experimenting with "analyze in Excel" feature. I would like to use the RLS to load RLS filtered table (NOT to actually analyze the OLAP Cube with pivot table)

After creating a dummy Measure=1 I was able to double click ("drill through") the Pivot table (by double clicking on the measure value in the pivot table). This gave me a linked table with the following command text:

DRILLTHROUGH MAXROWS 1000
SELECT FROM [Model] WHERE
(([Measures].[Measure],[Table1].[email].&[xxx@xxx.xxx],[Table1].[ID].&[4],[Table1].[X].&[654]))

After removing the filter part =>

DRILLTHROUGH MAXROWS 1000
SELECT FROM [Model]
WHERE (([Measures].[Measure]))

=> I was able to get a linked table with all the columns and rows. Also the RLS works: when I refresh from a different account I see only the allowed/limited rows. - Amazing! (Using Office 365, signed-in account)

My goal was to set up a linked EXCEL table with working RLS (without using MS SQL server or other) - this seems to work now!

My question is:

1. Will the connection obtained via "Analyze in Excel" be working PERMANENTLY or is it only TEMPORARY?
2. Can I modify the "SQL" command text even more to return the whole table/data set? Something like "Select * From ... " - (of course while respecting the RLS)
3. Is this approach reliable? Can you think of any downsides?
4. Could someone exploit the connection to ignore the RLS?

Warm regards

Jakub Dušek

[v-lili6-msft]

HI, @jdusek92

Excel is Connected Live to the Power BI Model in the Service.

and current RLS filter effect only works for read permission users, if you are dataset owner or you have edit permission, then RLS not works.

http://radacad.com/power-bi-and-excel-more-than-just-an-integration

https://community.powerbi.com/t5/Service/RLS-Analyze-in-Excel/td-p/222826

Best Regards,

Lin

[jdusek92]

Hello @v-lili6-msft thank you for your reply. 

what you say is completely clear to me and is not a part of my question - RLS works as expected - owner sees all and other users assigned to RLS role sees filtered data.

My question is about thr linked data table obtained via drilling through OLAP pivot table (created by Analyze in Excel)

Warm regards

Jakub

[jdusek92]

This is how I get the linked table I have questions about:

[v-lili6-msft]

hi, @jdusek92

thr linked data table obtained via drilling through OLAP pivot table (created by Analyze in Excel) is couldn't achieve like "Select * From ... ". for you need to sign in Office 365 account and it also will be verified by RLS.

Best Regards,

Lin

[jdusek92]

Hello @v-lili6-msft,

thank you for this information.

What about the other questions?

1. Will the connection obtained via "Analyze in Excel" be working PERMANENTLY or is it only TEMPORARY?
2. Can I modify the "SQL" command text even more to return the whole table/data set? Something like "Select * From ... " - (of course while respecting the RLS)
3. Is this approach reliable? Can you think of any downsides?
4. Could someone exploit the connection to ignore the RLS?

I want to use this linked data table to create personalised reports in Excel - users will refresh the connection themselves and are supposed to get only "their" rows. (Power BI solution is also planned for later). Therefore I need the connection of this linked data table to be permanent and to ensure RLS filtering.

Warm regards,

Jakub

[v-lili6-msft · Accepted solution]

hi , @jdusek92

1. It is PERMANENTLY, You can use it next time you open it, also it will verify your account.

3. reliable, although I can't find the relevant documents for you, Unreliable Microsoft won't use it.

4. Impossible, Security has always been a major concern for Microsoft.

Best Regards,

Lin