Consume XS classic Hana calculation view from XS Advanced via synonym in a MTA project

[kevindass]

This blog talks about few roadblocks and challenges one could face when working on Hana XSA Accessing Remote Sources & External Objects (Schemas, etc) by ian.henry or Synonyms in HANA XS Advanced, Accessing Objects in an External Schema by christoph.gilde or Using Synonyms to Access External Schemas and Objects in XS Advanced- by help.sap.com

Details of issue faced are in SAP Community Questions section Access XSC Hana calculation view via synonym MTA project from XSA

Below is preview of issue.

Error

Error message states that the runtime time user (*_RT) for a HDI container lacks certain privileges

Background

We are building BW data modeling artifacts on XSA platform so as to connect to SAP Analytics for Cloud(SAC).

We did follow all steps from below SAP note and SAP help documentation as well during our development process.
2459265 - How to access foreign schemas in SAP Web IDE for SAP HANA (XS Advanced)
2916898 - How to create synonym for non-HDI schema table in XSA

We faced difficulties accessing XSC calculation view from _SYS_BIC system schema via synonym MTA project in Web IDE. Note Hana table access from other schema like (HANABW etc) works fine.

Hana View Calculation view in XS Classic build using Hana Studio.

 

There are three possible areas to start troubleshooting for above error on missing privileges

1. Missing code in .hdbgrants file in MTA project


2. Missing role/object privileges etc in Hana Catalog role BH_UPS_SYSBIC::EXTERNAL_ACCESS or BH_UPS_SYSBIC::EXTERNAL_ACCESS_G


3. Hana Classic object in this case Hana Calculation view

In order to know missing privileges. One could activate Hana trace on database user connecting from XSA layer(in this case it is going to be HDI container runtime user). However this would error at pretty high level as below

TraceContext     TraceContext.cpp(01259) : UserName=BWSAPECCMASTERDATA_HDI_BWSAPECCMASTERDATA_DB_1_EH9MK06W5XJMW145SBQLAGKP6_RT, ApplicationUserName=<your_userame>, ApplicationName=<????>, StatementHash=ecdb3e100ba7ff75fe00558b0e57186e, StatementExecutionID=844437895568501

Ptime Exception: insufficient privilege: [2950] user is not authorized :  occurred while unfolding _SYS_BIC:LBRANDS.BHPOP.OTC.TransactionData/ZCV_BHPOP_PO_DM

Ptime Exception: insufficient privilege: [2950] user is not authorized :  occurred while unfolding _SYS_BIC:LBRANDS.BHPOP.OTC.TransactionData/ZCV_BHPOP_PO_DM

In order to know error at system level, collect authorization trace as below:

1. Run the following statement in the hana database to set the Database trace to info:

alter system alter configuration ('indexserver.ini','SYSTEM') SET ('trace','authorization')='info' with reconfigure;

2. Reproduce the issue that results in the authorizations and/or missing privilegs error

3.When the execution finishes please turn off the Database trace by setting it back to its default as follows in the Hana studio:

alter system alter configuration ('indexserver.ini','SYSTEM') unset ('trace','authorization') with reconfigure;

After that, collect the fullsysteminfodump as per SAP note 1732157

Results:

Authorization XmlAnalyticalPrivilegeFacade.cpp(01331) : Authorization error: User BWSAPECCMASTERDATA_HDI_BWSAPECCMASTERDATA_DB_1_EH9MK06W5XJMW145SBQLAGKP6_RT
is missing analytic privileges in order to access _SYS_BIC:LBRANDS.SAPECC.PO/ZCV_EKKO; AP _SYS_BI_CP_ALL was not granted

Summary:

To consume XS classic Hana calculation view from XS Advanced via synonym in a MTA project check if the “Apply Privileges” set to BLANK for main view as well as all dependent views used in main view.