Implementing RLS in Dynamic Subscriptions

[THowkins96]

Hi all,

Looking for some guidance here regarding the new dynamic subscriptions. Is there a way that I can implement my existing RLS in the model to my subscriptions? I have a contact list to power the subscriptions which contains an email address. subject and an attachment format (PDF or PowerPoint). As I understand it, the user identification that usually passes for RLS in service does not pass for subscriptions, so how can I enforce my same RLS principals to subscriptions?

Thanks!

[v-nmadadi-msft]

Hi @THowkins96 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[GilbertQ]

Hi @THowkins96 

I was under the impression that when using dynamic subscriptions, the row level security will still kick in because you're specifying the user of whom the email is going to be sent to. I would highly recommend testing this out to see if their functionality does actually work.

[THowkins96]

Hey @GilbertQ 

My understanding was the same, I expected the existing RLS rules in the model to apply automatically within dynamic subscriptions. To test this, I used a separate test account and validated against a rule in my model where the username in my RLS table must match UserPrincipalName(). In the Power BI Service model, I intentionally didn’t assign that security role to any security group, specifically to confirm that the subscription output would return blank if RLS wasn’t being honoured.

Based on the results, it seems like I may need to find a way to enforce impersonation so that the subscription runs as the intended user and therefore applies the correct RLS filtering.

For context, our organisation’s RLS structure is built around a core RLS table containing user emails and the “nodes” they’re associated with. This table has a many-to-many relationship with an RLS Outlet table that maps nodes to their corresponding outlets. From there, the RLS Outlet table links (many-to-one) to the main Outlet table, which ultimately filters what each user is allowed to see. For example: if User X has access to Node A, and Node A contains Outlets 1, 2 and 3, then User X should only see those outlets.

Do you have any guidance for how I could achieve this end result?

Thanks!

[ibarrau]

Hi. I wouldn't assume it works because it doesn't specify anything about rls at microsoft docs: https://learn.microsoft.com/en-us/power-bi/collaborate-share/power-bi-dynamic-report-subscriptions

I think depending on the details of your RLS implementation, you could use the dynamic rls table for sending the subscription. The RLS DAX filters may not be active, but if the table you filter by username will filter downstream tables in the normal way. It's like imaging you have loaded a table with the emails  you need for the subscription without even considering rls, just for configuring the subscription.

I hope that helps, 

[THowkins96]

Hi @ibarrau 

Thanks for your response!

I did try putting a dynamic filter on RLS[Username] = ContactList[Email] but the output was still the same. I'm not sure really how this RLS impersonation can be achieved any other way outside of this?

I guess as a last resort fall back option I can just include the link to the report and not the PDF/PPT attachment so that when the user goes into the report their RLS is applied. This isn't the ideal solution in reality because we are trying to automate what users are currently going in and pulling out manually on a schedule but that's the only thing I can think to do....

[v-nmadadi-msft]

Hi @THowkins96 ,

I think thats a great workaround, also check out Dynamic per recipient subscriptions for reports and verify whether if it matches all your requirements.

Dynamic per recipient subscriptions are designed to simplify distributing a personalized copy of a report to each recipient of an email subscription. You define which view of the report an individual receives by specifying which filters are applied to their version of the report.

Dynamic subscriptions use a dedicated Power BI semantic model to store and manage the filter assignments for each recipient. This model acts as the lookup table that maps every email address to the specific filters they should receive. When the subscription runs, Power BI pulls the most up to date data from this semantic model to determine who gets the report and which filters are applied to each individual attachment.

Reference: Create a Dynamic Subscription for a Power BI Report - Power BI | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[THowkins96]

Hi @v-nmadadi-msft 

Thank you for your response. It is actually the dynamic per recipient subscriptions that I am using, it's just that I am finding that the RLS that lives in the model doesn't activate in subscriptions when I'm setting them up. After a lot of research it seems that if I am to be successful in them being sent out with the correct permissions I need a way to impersonate RLS.

I've been looking at using a shadow filter table that powers a calculation group to enforce the subscription logic as below, to stop the subscription logic pouring into the live version of the report.

Enforce Subscription =

VAR Is_Subscription_Running = ISFILTERED('Subs_Shadow_Filter'[Value])

RETURN

    IF(

        NOT Is_Subscription_Running,

        SELECTEDMEASURE(),

        // NEW LOGIC: Use Intersect instead of TreatAs

        // This calculates the list of Outlets allowed by the Subscription

        VAR SubOutlets = VALUES('Subs_Shadow_Filter'[durable_trading_outlet_skey])

        RETURN

            CALCULATE(

                SELECTEDMEASURE(),

                // KEEPFILTERS ensures we don't overwrite RLS, we just add to it

                KEEPFILTERS(

                    FILTER(

                        ALL('Outlet'[durable_trading_outlet_skey]),

                        'Outlet'[durable_trading_outlet_skey] IN SubOutlets

                    )

                )

            )

    )

Is this an approach you'd recommend?

[v-nmadadi-msft]

Hi @THowkins96 ,

The DAX seems good enough but I would still recommend you to figure out why the RLS that lives in the model doesn't activate in subscriptions, fix that issue so that dynamic subcription works in the intended way.

Some points which may help figure out why its not working correctly:

"Power BI connects to two different semantic models. One contains subscription recipient data, including email addresses of the managers. The other contains the data and defines the mapping between recipients and filters. When it's time to send the report, the latest data available in this second semantic model determines which manager receives a report in their inbox, and what filters are used in that report."
Make sure both the semantic models are correct especially the semantic model which contains recepient data.

Additionally, Build permissions to a Power BI semantic model that contains recipient data. This data includes the email addresses of your recipients and filter values that should be applied for each recipient.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[THowkins96]

Hi @v-nmadadi-msft 

Thanks for this - Can confirm that I'm not actually using 2 separate semantic models, I ingested my subscription list source into the model that I want to use the subscription for. I actually stripped it back and started from square one again of simply adding in the sub list source, leaving the table disconnected from all other tables and then setting up the subscription using the email address, subject and attachment type to define who is sent the email and in what format and then i put a dynamic filter on sub list node = node in my RLS bridge table.

This returned a blank result still so I am at my whits end with it to be honest

[v-nmadadi-msft]

Hi @THowkins96 ,

Please ensure all the prerequisites are being met

Additionally, re check all the steps that are mentioned in the document and check.

I hope this information helps. Please do let us know if you have any further queries.
Thank you