Access XSC Hana calculation view via synonym MTA project from XSA

[kevindass]

Hi,

We have been having issues even after following SAP notes and SAP Help Doc(details below) with accessing XSC hana calculation view via synonym MTA project on XSA in Web IDE.

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

Database Synonyms in XS Advanced

From <https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.02/en-US/556452cac83f423597d3a38a6f225e4b.html>

Issue details:

Accessing XSC calculation view from _SYS_BIC system schema via synonym MTA project in Web IDE. Note table access from other schema like (HANABW) works fine

Could not open 'BWSAPECCMASTERDATA_HDI_BWSAPECCMASTERDATA_DB_1'.'BwSapEccMasterData.models::ZCV_BHPOP_PO_DM'.
Error: (dberror) [258]: insufficient privilege: search table error:[2950] exception 306003: Authorization failed in Calculation Engine. User is not authorized to 'SELECT'
The data preview uses the following statement fetch data:
SELECT TOP 1000
"VBELN",
"VBELP",
"ZDEMAND",
"Row_count"

FROM "BWSAPECCMASTERDATA_HDI_BWSAPECCMASTERDATA_DB_1"."BwSapEccMasterData.models::ZCV_BHPOP_PO_DM";

XSC HANA Calculation View:

Note this view has “Apply Privileges” set to BLANK as well.

UPS user and respective roles

UPS user BH_UPS_SYSBIC has been
created with below grants

SET SCHEMA _SYS_BIC; 
-- Create role to be
granted for external access via synonym
CREATE ROLE "BH_UPS_SYSBIC::EXTERNAL_ACCESS";
--grant select on schema
_SYS_BIC to "BH_UPS_SYSBIC::external_access"; -- use this for allow
access to the whole schema
GRANT SELECT ON "_SYS_BIC"."<full_namespace>/ZCV_BHPOP_PO_DM" TO "BH_UPS_SYSBIC::EXTERNAL_ACCESS";
CREATE ROLE "BH_UPS_SYSBIC::EXTERNAL_ACCESS_G";
--grant select on schema _SYS_BIC to "BH_UPS_SYSBIC::external_access_g" with grant option; -- use this for allow access to the whole schema
GRANT SELECT ON "_SYS_BIC"."<full_namespace>/ZCV_BHPOP_PO_DM" TO "BH_UPS_SYSBIC::EXTERNAL_ACCESS_G" WITH GRANT OPTION;

Synonym

We did try both hdbgrants and hdbsynonymgrantor with roles as shown

hdbgrants code

Regards,

Kevin Dass

[former_member683296]

Hi Kevin,

I have same issue. Do you have created blog for this? Could u please share ?

Thx

Regards

[kevindass]

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

[kevindass · Accepted solution]

This has been resolved. I have written a blog on the same Consume XS classic Hana calculation view from XS Advanced via synonym in a MTA project

[lokeshharikrishnan]

Hi Kevin,

My 2 cents over here. Can you just try to add the schema_roles for the object_owner & application_user. You can check on the syntax for the same in the link mentioned below.

https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.02/en-US/f7381ccf4bc644f286ac72cc54...

Regards,

Lokesh

[ssurampally]

Hi Kevin, Technical user used for creating user provided service may not have full access to the calculation view in Classic. I see that, role created has access on _SYS_BIC schema., it will also need to have access on the Schema of which tables are used in the CV.

Secondly, it could be due to Analytical privileges, but you already mentioned that., it is not enabled for the CV. So it can't be the case.

Lastly, it may not sound technical sense, but if you can try placing the .hdbgrants file outside of src folder in WebIDE project, just under the DB folder level by creating a a new folder cfg, then place hdbgrants inside that. So with that it runs first and make the authorizations granted. you can give a try.

THanks

Sreekanth

[kevindass]

Sreekanth appreciate your response.

"can try placing the .hdbgrants file outside of src folder in WebIDE project"

This is already in place

"Technical user used for creating user provided service may not have full access to the calculation view in Classic. I see that, role created has access on _SYS_BIC schema., it will also need to have access on the Schema of which tables are used in the CV."

My understanding is, why would a user(say Application user or Technical user) need access to tables which are in a Calculation view? purpose of view is to of course to combine various tables and things around it by not letting user know which tables are within it. However the UPS user does have access to all tables.

Regards,

Kevin Dass

[kevindass]

Sreekanth.

This has been resolved.

Resolution was look at various Hana Calculation views used within the parent view ZCV_BHPOP_PO_DM and assigning

Analytic Privilege _SYS_BI_CP_ALL to UPS user.

I shall get a SCN blog on the same as well

[ssurampally]

Thanks for the update Kevin, I didn't think about upstream CVs used in your current CV. 🙂