Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
SreekanthSurampally
Active Contributor
4,802
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.

 

 
9 Comments
0 Kudos
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
former_member210043
Participant
0 Kudos
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

 
0 Kudos
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
glen_spalding
Participant
0 Kudos
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?

 

thanks

g
SreekanthSurampally
Active Contributor
0 Kudos
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.
glen_spalding
Participant
0 Kudos

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

ta

SreekanthSurampally
Active Contributor
can you set the DI schema,   set schema RPTHDI_ZMCL_RPT_HDI_1#DI ;
glen_spalding
Participant
0 Kudos
well spotted, thanks 🙂
glen_spalding
Participant
0 Kudos

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

 

Labels in this area