Skip to Content
Technical Articles
Author's profile photo Sreekanth Surampally

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.



Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Unknown Unknown
      Unknown Unknown

      Hello Sreekanth,


      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.

      Error message:
      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.



      Author's profile photo Nikil b
      Nikil b

      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   



      SYS_DI.T_NO_SCHEMA_ROLES; - Getting the error as unresolved Table



      Added screen shot for reference 


      Please help in this regard




      Author's profile photo Daniel Ojados Conesa
      Daniel Ojados Conesa

      Hi Nikil,


      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.



      Author's profile photo glen spalding
      glen spalding

      hello, i can see it has been a while.

      i am using your steps, however, when calling the procedure, i get

      Error: (dberror) [258]: 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?




      Author's profile photo Sreekanth Surampally
      Sreekanth Surampally
      Blog Post Author

      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.

      Author's profile photo glen spalding
      glen spalding

      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


      Author's profile photo Sreekanth Surampally
      Sreekanth Surampally
      Blog Post Author

      can you set the DI schema,   set schema RPTHDI_ZMCL_RPT_HDI_1#DI ;

      Author's profile photo glen spalding
      glen spalding

      well spotted, thanks 🙂

      Author's profile photo glen spalding
      glen spalding

      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