I agree that this would probably work if a central group such as IT were doing all of the development. However, we expect that we may have a number of business data stewards / power users who will be building data models for their team workspaces.
For databases like Oracle, there is only one database connection allowed per database server. This means that the service account for this one connection must have read access to all tables that might be used in any Power BI workspace that needs to pull data from that server.
Let me expand on the scenario a bit....
Assume Jill is a data steward in our production reporting group developing reports and data models around daily gas and oil production. This data is not particually secure since it becomes public at the end of each month anyway. These reports and data models are published into a Production reporting sandbox workspace that has wide visibility since this information is used by almost everyone. Jill is an admin on this workspace.
Jill is also a member of a team that compares actual production to projected production based on estimated reserves and field development plans. This comparison is used to refine our reserves calculations and tune field production plans to maximize total recovery. As part of this team Jill has security access to the reserves and production plan information. This information is restricted since is has a high competive value. Reports and data models including the reserves information and production estimates are published into a reserves workspace with limited access. Jill is not an admin on this workspace.
Since both the reserves data and the daily production data reside in the same Oracle database, the service account entered into the enterprise data gateway must have access to both.
Jill inadvertantly creates a data model that includes both production and reserves data and publishes it to the production reporting workspace. Since there is only one enterprise gateway connection, and that connection has access to all data in the database, it happily refreshes both the "public" production data and the restricted data every month.
Ideally, the gateway connection used by the production reporting workspace would have a service account that only gave it access to the public production data, so that even if a data model were inadvertantly published into that workspace, it would not refresh. Jill would have be a member of the User group allowed to publish to the gateway connection, but could not publish to the connection that included restricted data.
I would be nice if the uniqueness check for a data source connection in the gateway included the userid of the service account so that you could have multiple connections to the same database with different sets of data behind each account. Currently the only way I can see to do this would be to have a multiple enterprise gateways.
Thx, Mike
