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