Analytical Privileges on CV for restricted data access in Reporting – XSA
SQL analytical privileges are generally used in controlling the data access on Calculation views to different user groups in reporting and analytics. In XS classic, data access/Analytical privileges are created in HANA studio and assign them to a database user directly, or group it in a role and then assign to a database user. However, in XSA, analytical privileges are created in design time and will be built inside the HDI container. So technically these privileges are owned by HDI container Object owner and will need to be granted to database user for reporting.
In this document, I explain the process of creating analytical privileges and granting them to a database user., Creation of analytical privilege is well explained in the SAP Documentation , however assigning the privilege to user is explained in security document So I thought of writing this document to consolidate the process to access calculation views in HDI container which have analytical privileges applied on them.
So the below picture shows the process accessing Calculation views in reporting applications like BOBJ, SAC and Lumira. OLAP connection(JDBC connection) is used to connect to HANA database to retrieve the data into BOBJ tools. For setting up this connection, database user in HANA who has access to CV models is mandatory.
In XS advanced, HDI containers are always isolated from the main database, access to the objects in Container is to be explicitly granted to any of the database user(including SYSTEM user).
So in my current scenario, reporting user will get access to the CV created in HDI container which also has analytical privileges defined as design artifact.
Step 1: In Web for HANA, I created a simple SQL analytical Privilege on CV,
Step 2: Creating a role: In Web IDE for HANA, created a .hdbrole to contain the required permissions on the container and also analytical privilege
Step 3: Grant the role to Database user, in Web IDE, Database explorer, the role will be granted to a database user using a stored procedure call as shown below.
Step 4: Access the CV using my database user, in Web IDE for HANA, database explorer, connect to main database with user SSURAMPALLY, then try to access the CV with analytical privilege associated on it, the below SQL can be run in console,
the result data will be displayed with analytic privilege applied on the CV. You can get the code samples for Role and Granting the role to user in here, Github
Thank you very much.
Thanks for the explained method of assigning access. Have you tested dynamic analytic privilege in XSA. I am trying this scenario, but data preview is having auth issue. I am using default_access_role concept which Thomas Jung explained to assign custom access to container.
Now I have tried your above mentioned approach to assign access to my user but still the issue persist.
500 insufficient privilege: Could not obtain all relevant analytical privileges for user DYNAMIC_AP_HDI_D_AP_HDB_1_72Z6WL0TKCUQ6BFPRWKG9CO0P_RT accessing view .DYNAMIC_AP_HDI_D_AP_HDB_1.CL_Dyna_AP due to exception: Invalid SQL AP definition
Can you provide your input.
Hello Sreekanth ,
I followed the same steps as you mentioned in bolg.
step1 : Created AP Successfully -AnalyticPrivileges_POC.hdbanalyticprivilege
step2: Created Role Successfully - APRole.hdbrole
step 3 : i am getting the error as unresolved Table .
Opened the sql admin of the HDI Container of the project did the below steps.
i. SELECT current_user from dummy - Successfully executed
ii.set schema POC3_SCHEMA_2_993BCOYK1573J80U1YQRJIP93_DT; -- Successfully
iii.CREATE LOCAL TEMPORARY COLUMN TABLE #ROLES LIKE _
SYS_DI.T_NO_SCHEMA_ROLES; - Getting the error as unresolved Table
Added screen shot for reference
Please help in this regard
You are declaring your local table with the wrong type, it should be of type "_SYS_DI"."TT_SCHEMA_ROLES" as Sreekanth well stated initially.
Hope it helps.
hello, i can see it has been a while.
i am using your steps, however, when calling the procedure, i get
Error: (dberror) : insufficient privilege: Detailed info for this error can be found with guid
can you advise which user i am meant to be calling this with?
Hi Glen, You will need to run this SQL CALL procedure in the HDI Container SQL Console(Admin) as a technical user, it should not be in the SQL console of your DB user.
hello, i understand the SQL Console (Admin), but it still does not work.
i asume you right click on the Project and select SQL Console (Admin) to get to the SQL box, right? or is there another way ?
current user is RPTHDI_ZMCL_RPT_HDI_1_AX7QMUK3K3RETN8P8BOJI4P9G_DT
can you set the DI schema, set schema RPTHDI_ZMCL_RPT_HDI_1#DI ;
well spotted, thanks 🙂
hi sreekanth, i wonder if you help again.
i have an analytic privilege, in a ROLE. the native ROLE "fi_all_role" existed in the old native folder/package structure
i'm having a problem locating this ROLE in my HDI Container, and it fails when being assigned (called), because it cannot be found