Skip to Content
Technical Articles

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.

 

 

3 Comments
You must be Logged on to comment or reply to a post.
  • 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.

    https://www.youtube.com/watch?v=yuPBSwBlFfo&list=PLoc6uc3ML1JR38-V46qhmGIKG07nXwO6X&index=9

    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.

    Regards,

    Vijay

  • 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   

             executed

    iii.CREATE LOCAL TEMPORARY COLUMN TABLE #ROLES LIKE _

    SYS_DI.T_NO_SCHEMA_ROLES; – Getting the error as unresolved Table

              T_NO_SCHEMA_ROLES

     

    Added screen shot for reference 

     

    Please help in this regard

    Thanks,

    Nikil

     

    /
    • 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.

      Regards,

      Daniel