Safe query not editable by user RRS feed

  • Question

  • Hi, I am publishing a sales revenue table by salesperson in a webservice (from ERP Microsoft Dynamics NAV).

    From Excel, I connect to that webservice and make a pivot table where I can filter on Salesperson, Item sold, Date... It works like charm.

    Now, I am asked to provide that same excel pivot table to each one of our salesperson but with the constrain that under no circunstances they can see any data from other salesperson different than him/her.

    I do not want salespersons to mess with the query to be able to switch to a different salesperson, or even break the "WHERE salesperson=" part of the query and see all salesperson.

    How would you design this?


    Thursday, November 2, 2017 9:36 AM


  • Hola Antonio,

    The recommended way to implement this type of security is to use Power BI and RLS. More info about that here.

    If you want to use Power Query, then each user must have its own set of credentials to get the data from that Web Service and the Web Service will be the one actually handling the security so the person querying the data is only getting the data that they need to see. Whenever you share your workbook with someone else, you're not actually sharing your credentials hence why every user MUST have their own credentials in order to refresh the query.

    Again, the recommended way would be to use Power BI + RLS, otherwise the data source must handle its own security layer with unique credentials for each user.

    Truthfully, no workaround would be 100% secure or even close to it, so I won't mention any.


    Thursday, November 2, 2017 12:00 PM