1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power query and security

Discussion in 'Power Pivot, Power Map etc' started by Lolo, May 19, 2017.

  1. Lolo

    Lolo Member


    I post again on power query and security because I really don't understand how it works and what it is possible to do or not and how it is useful.

    Each workbook and DB connction can have a privacy level (none/public/organisational/private)
    I have an excel file that connect to orcale via power query. And I put a privacy level = private, the same for the workbook, in order to see what is the behavior.
    The db user/password is recorded in power query.

    Basically, I would want that a user that receive the dashboard would not be able to open execute the oracle query via power query, or at least he would need to have the DB password to do that.

    But the user that receive the file, is able to open power query, update the request, refresh the oracle query without any DB password prompt !?. He just receives warning or message to run the query.
    But what is the goal of the privacy settings in this case ??

    Moreover, it can at any moment change the privacy level of the file, this way, no warning, nothing, and access to the query change it, run it(so no crednetials to input) and do some unexpected things.

    All this privacy system seems useless, and more annoying than useful, since the user can at any moment disable it, and change what he wants.

    I have probably miss something, but I don't understand how it works, and how I can use security in power query, and the impact on the deployment of excel files.

    It someone can enlight me to understand how this kind of file can be deployed with a miminum of security... :(

    Thank you

    NB1: I know that by protecting the workbook, we can disable power query button, and connection refreshs, this way we would avoid to refresh queries, but seems there is a bug and when we use also power pivot (and it is of course my case), there are some issues see Ken plus post (not tested yet myself however)


  2. Chihiro

    Chihiro Excel Ninja

    Yep, there's no simple solution and there's open ticket with MS about this. Even the workaround can easily be bypassed by user with minimal amount of skill/will.

    For this reason, I've shifted many of my dashboard from Excel to Power BI.
  3. Lolo

    Lolo Member

    Thank you for the answer.

    Since I don't want that users refresh queries, I will protect the workbook and also sheets (to avoid a refresh of pivot table so a refresh of queries). I fully know that this is not perfect, but , it is better than nothing.

    For now I cannot use power BI desktop unfortunately.
    however how the tool differs from excel in term of deployment ?
    - Are the file always shared via a portal ? Or can we sned th pbix files to a user tat will be able to use it.
    - in term of security, what is the difference ?

    Thank you for your information
  4. Chihiro

    Chihiro Excel Ninja

    Depends on your set up. I typically publish dashboard created in Power BI Desktop to Power BI cloud service. However, you can also share pbix files (stored in network drive). Note that depending on data source setting, you can fine tune control and access.

    Ex: Import vs Direct Query - Import will have all tables/columns and data populated in data set (i.e. you need refresh to see most current data), direct query will have only tables/columns are copied. You will always see most current data.

    If you use cloud service you can utilize row level security. Meaning that you can set to show employee x only items that pertains to him/her and do same for employee y, all from same data set.

    Refresh is controlled by the creator and you can schedule refresh or do manual refresh when you want (when import is used).

    You can find many resources and articles in links below.
  5. Lolo

    Lolo Member

    Chihiro likes this.

Share This Page