SAP HANA and Row Level Security

[mattswindon]

Is it possible to implement a row level security with SAP HANA and Power BI? 

I’ve reviewed the article below regarding UPN’s and Mapping, can this be implemented with SAP HANA by mapping to its SESSION_USER?

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-manage-ssas/

[samuelmanseau]

Hello, 

We succed in our organisation to deploy SSO between HANA & PowerBI on DirectQuery mode allowing to read the SAP Roles and getting the correct Data (without RLS) in Azure AD.

1/Action on the SAP Hana Base (Admin/Basis tasks) : Microsoft Docs : https://docs.microsoft.com/fr-fr/power-bi/connect-data/service-gateway-sso-saml : deploy & install on different machine (server Linux, Virtual Machine, pc,..) you may need specific tool (FileZilla, Putty,...)

2/ Action on the Gateway Server (Admin/Basis tasks) : Microsoft Docs : https://docs.microsoft.com/fr-fr/power-bi/connect-data/service-gateway-sso-saml 

2bis/ Action on the Admin Portal : Tenant->Integration-> Allows Azure SSO

3/ Action on SAP : create 2 Analytic Privilège on the dedicated view (calculation view) with SQL Script ("BU" = 1 & "BU" = 2) & SESSION_USER. You may need to modiy your view to add the Session_user field.

4/ Action on SAP : create User "Test_SSO_1" with dedicated Roles & Privilèges (Paskcge, Object,..) add the Analytic Privilège BU1, do the same for "Test_SSO_2"

5/ User "Test_SSO_1" must have a SAML External connections : no Login/Password but check the SAML Configuration : Add the Identity Provider & UserPrincipalName (follow the previous Microsoft doc) do the same with "Test_SSO_2" with a different UPN.

6/ Dedicated PBI Gateway "SSO" : create gateway, add Test user (UPN) & test the user on the Gateway "Advanced setting"

7/Test : with a Generic user "BI_ALL" connect via the Desktop to SAP : credential "Database" (not windows), in DirectQuery mode, create a report "SSO_HANA" with axis on which apply Analytical : "BU". The generic user see all the data.

8/Publish this report "SSO_HANA" on dedicated workspace, be sure to use the SSOGateway, give acces to your Test user, they should see only BU=1 & BU=2

It could take half a day for Admin tasks (with appropriate right on the machine) to install a set the SSO & a day to developp a view, analytical privilege, & Roles.

Good luck !

[v-yuezhe-msft · Accepted solution]

Hi @mattswindon,

After connecting to SAP HANA database in Power BI Desktop, you can define roles and rules to implement row level security for your dataset, and you will need to add members to defined roles in Service after publishing the Power BI Desktop file.

Moreover, you can take advantage of the DAX function username() within your dataset. You can use it within expressions in Power BI Desktop. When you publish your model, it will be used within the Power BI service. You can take a look at the following article to get more details about row level security in Power BI: https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/.

In addition, the article you reviewed is specific to SSAS data source, I am afraid that you are not able to use same technology to map user with SAP HANA.

Thanks,
Lydia Zhang

[mattswindon]

Thanks Lydia. I'm having to revisit this after a couple of weeks away. Perhaps I' was over complicating things by talking about SAP SESSION_USER!

From what now I understand, the SAP HANA conenction cannot utilise any row level secutiry already implemented in HANA but we can build a seperate secuirty model in Power BI when accessing HANA via Direct Query.

[Noel]

Are there any plans to leverage row level security in HANA the way Power BI works with SSAS?   Duplicating row level security thru Power BI Roles is far from ideal if there already is a security model in place in HANA.

[Anonymous]

I’m getting ready to tackle RLS. I’m curious how far you’ve gotten. I’m crossing my fingers I don’t lose the performance I’ve been enjoying since adopting HANA.

[joel_gibby_csc]

How far did you get? Has anyone tried using a composite model?

https://docs.microsoft.com/en-us/power-bi/desktop-composite-models

https://radacad.com/composite-model-directquery-and-import-data-combined-evolution-begins-in-power-b...

Evaluate USERNAME() DAX function to determine current report viewer and add a directquery lookup on a BW authorization table for the objects named in the import query? 

---

@Anonymous wrote:

I’m getting ready to tackle RLS. I’m curious how far you’ve gotten. I’m crossing my fingers I don’t lose the performance I’ve been enjoying since adopting HANA.

---

[Anonymous]

I've conquered dynamic analytic privleges for sap hana - we do not use BW.  Dynamic procedure passes the rule for the user viewing the data.  We use direct query - the performance is awesome.  I couldn't be happier with this as a solution.  We need paramters to be accessible from powerbi service but I'm not slowing down for that. 

SSO was a little painful to seutp sense the MS support didn't realize their product did not support sso in desktop (saml).

[jochenj]

hi dabunn, i'm trying to do the same, defining the security in hana for calculation view and leverage that one in PBI.... could you explain more in detail what you mean by "dynamic analytic privilege" and "Dynamic procedure passes the rule for the user viewing the data." ? thanks ahead JJ

[Anonymous]

How much detail do you need ?

I have some brief documentation but I've not polished anything up.  Here is a summary for now - 

Calculation view 

   - Run with - definers rights

   - SQL Analytic Privlege

Analytic Privlege

   - Dynamic privlege

          - Build procedure that can run a select statement that contains rule logic or do it all in the procedure.  I tend to modularize          things so I would recommend a view that looks at a user table, and user rule table, and controlled columns within the organization.  View needs in the where clause a filter for session_user = to user rule being built for.

Our database users are mapped to the UPN thats passed by the gateway server.

[tcbuck]

I am also curious to see if anyone had any luck tackling RLS on a PowerBI -> HANA On prem setup