Check on the missing Analytical Privileges of a calculation view

[ssurampally]

Use case: You are a HANA system administrator/developer and got to work on an Authorization issue in SAC/BOBJ reporting live model based on HANA Calculation view for a business user.  The CV has several transactional and dimensional CVs used  and It is hard to identify which object has analytical privilege issue.

Example:

 

In the above picture, in CV_GL_REPORT there are 4 other CVs used and the user getting an authorization error on this CV due to not having access to one or more CVs that are being used here.

Solution:  SAP provided a system view EFFECTIVE_STRUCTURED_PRIVILEGES to check the missing privileges on the CV. In the DB explorer tool, you can run the SQL statement below to get the analytical privileges check done.

select * from effective_structured_privileges

     where root_object_name = 'CV_GL_REPORT' and 

           root_schema_name = 'EDW' and

           user_name = 'SSURAMPALLY';

It requires an equal predicate on ROOT_SCHEMA_NAME, ROOT_OBJECT_NAME, and USER_NAME. So must provide them in WHERE clause.

On the execution of the SQL, you will get a result of check status for each CV that is used in CV_GL_REPORT with status Applied or Not applied.

With that result, you can fix the authorization error by adding the object to one of the Analytical privileges that is created for this model purpose.

Note: This is applicable for HANA XSA(On-premise) and HANA cloud(Cloud) products using either DB explorer in Web IDE or Business Application Studio.

 

Thanks

Sreekanth