Hello,
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)
http://www.excelguru.ca/blog/2017/05/02/protect-power-queries/

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)
http://www.excelguru.ca/blog/2017/05/02/protect-power-queries/