Securing Views in HANA

[Former Member]

Dear All,

We have followed the following steps given in here

Roles - PUBLIC

SQL Privileges - Execute and SELECT on _SYS_BIC, _SYS_BI. Also Execute on REPOSITORY_REST procedure

Analytic Privileges - The one that I created with just one view in it

System - None

Packages - My package that also has a few other analytic, attribute & calc views

However, what it does is give access to all other views as well. We want users in Role A to see views in Package A only. Role A should not be able to execute views in Package B. Following question in blog by Jonathan says that you can restrict within _SYS_BIC but there is not technical steps given to do that. Is this a valid option?

**************************************************

Questions: How secure are the analytical views?

Answer: Analytic views can be secured in a few ways. First, you can secure their parent package. This will allow you to limit developers from modifying packages that they do not own. Secondly, you can secure them within the _SYS_BIC schema all the way down to the individual components of the model.  For example, I can limit one DB role to only see 1 of 15 Analytic Views available within the HANA repository. Finally, you can apply Analytic Privileges to secure data rows from returning during query execution.

**************************************************

We are using Analysis for Office SP3 to connect to HANA OLAP connection in BI 4.1 Platform. It display every view in other packages. We are using HANA Live content which has all the views under single package. So far we have been through each manual and guide. Are we missing something or is it not possible?

Your help and suggestion will be highly regarded.

Regards

Angad

[Former Member · Accepted solution]

Hi Guys,

Jonathan Haun has written a blog here in reply to my tweet.

So far I have tested his suggestions (i.e. adding corresponding column views in _SYS_BIC instead of giving _SYS_BIC full access to role) and it works in test environment (rev 60)

Roles - PUBLIC

SQL Privileges - Execute and SELECT on _SYS_BI. SELECT on each associated column view in _SYS_BIC, SELECT on Schema

Analytic Privileges - The one that I created to allow views under package

System - None

Packages - My package that also has a few other analytic, attribute & calc views. It is always good to create sub packages for specific department and move views in there.

We tested it at work (rev 64) and it didn't work for HANA Live content views. We still were able to view the HANA live content views which stays in other package sap.hba.ecc. Kindly, one of the consultant showed us a patch discussed in SAP note 1907697 (released on September 2nd 2013) which at the end fixed our issue. We also learned that after applying that patch _SYS_BIC is not required under SQL privilege.

"SAP note explains with rev 64 the _SYS_BI.BIMC_Cubes view is reverted to its original definition, excluding the feature for selecting only the user authorized views. In revision 60 to 63 the definition of _SYS_BI.BIMC_Cubes included a SQL part of select from SYS.ACCESSIBLE_VIEWS which was showing only the views/cubes for which the user has permissions (analytical Privilege) to select from"

I have also found that views in HANA live content have "Enable Analytic Privilege" un-checked. If we tick that box and create approriate analytic privilege to allow business users to view only their allowed views in package then other views are secured and users cant see them.

However, I still don't understand that should we implement both (above mentioned solution/workaround) or just do one depending upon the version?

Anyway, I hope it will help some one in future.

Regards

Angad

[justin_molenaur2 · Accepted solution]

Hi Angad, per our other thread I thought I would shed some light here. I think you have two things going on.

1. Any view (including HANA Live content) needs to have Analytic Privileges enabled in order to secure them at the row level per my understanding. I just tested creating a user with specific analytic privileges and also a view with analytic privileges disables and the user could not see the view. So I am not 100% clear on the functionality of that checkbox.

2. If you require the ability to only show users the views they are authorized to see (very typical), then you need to modify the view per that OSS note.

Otherwise, I think you have it nailed down. Let us know how it goes!

Regards,

Justin