Skip to Content
0

Row level data authorization control in HANA for third-party BI Clients (MS Power BI)

Feb 14, 2017 at 06:33 PM

127

avatar image
Former Member

We are using MS Power BI (PBI hereafter) for creating analytical reports on top of SAP HANA. The reports are created in PBI desktop, connecting to HANA using SAP HANA client packages, and then exported to the PBI cloud service which uses on-premise PBI gateway to connect to HANA system. Users log-in to PBI cloud using Office 365 account (usually email id) and regardless of the individual user, the PBI gateway uses the stored credential (connection user) to connect and query data from HANA. This all works well.

We want to perform row-level data authorization control in HANA for these PBI queries that are received via PBI gateway (or ODBC drivers, for test purpose) using dynamic SQL analytic privileges. However, the SESSION_USER in the stored procedure always returns the connection user details and not the actual user who is performing the query. Without the actual user details, we cannot filter data dynamically based on authorizations assigned to him.

Please suggest me the best approach or solution for third-party BI clients scenarios, in general, and how to resolve the current problem with PBI service and desktop connection.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
Feb 14, 2017 at 08:05 PM
0

The SESSION_USER of course returns always the user which is used to establish the connection/session. As you wrote that you are using a stored procedure it seems that you have implemented your own logic. Have you checked the APPLICATIONUSER session variable if it is set in your case (it should be) -> e.g. SELECT session_context('APPLICATIONUSER') AS appuser FROM dummy;

Regards,
Florian

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Florian,

Thanks for your prompt response.

I could not yet test that in an end-to-end scenario (i.e. applying analytic privilege to my user and accessing calc. view from PBI desktop to see how it works and check authorization trace to see what is actually captured there) due to some system issue which may take a day more or so

However, when I check the session_context('APPLICATIONUSER'), I always get the OS user id of my laptop and the M_SESSION_CONTEXT (which shows session variables of all open connections) view does not show PBI values when I open a Calc. view from HANA into PBI and a connection is created. Why I am sure that a connection was created because I had applied analytic privilege to that view and it threw insufficient privilege error within PBI as that analytic privilege was not yet assigned to my user id in HANA).

Therefore, I am still not sure that the APPLICATIONUSER session variable will be set in case of a connection from PBI. Are there any other places where all incoming connection information is stored and can be accessed?

0