HANA XSA UPS - Access cross HANA database tables

[ssurampally]

Hi, I am creating an UPS in XSA Dev, that points to HANA prod and then use that service in .hdbgrants file to grant access to the container. After that I create a synonym for the target database table. Is there any configuration that needs to be done before to make cross database access scenario to work. Because, when I tried to build the synonym file that is created with a target parameter on the prod schema and table name, it is failing with the error "Target doesn't exist". Then I thought I will add a database parameter also as it is a cross database access, then it gives another error saying ensure the cross-database is defined correctly and grant "linked database on remote source" privilege. So I am wondering is there any config that I miss to allow this UPS to grant access? please let me know.

Syntax of synonym file that I have used as below,

{ 
  "com.sap.hana.example::DUMMY" : { 
     "target" : { 
        "database"   : "DATABASE_A", // optional (cross-database access)
        "schema"     : "SYS",        // optional
        "object"     : "DUMMY",
        "revalidate" : true          // optional (Boolean)
     }
  },

[Cocquerel · Accepted solution]

"Cross-Database Access" refers to a technique that allows to query several Tenants within the same HANA system.

see https://help.sap.com/docs/SAP_HANA_PLATFORM/6b94445c94ae495c83a19646e7c3fd56/e2cd38a98ee245babfc1605...

Are your dev and prod on the same HANA system ? If not, you have to use SDA and .hdbvirtualtable instead.

[ssurampally · Accepted solution]

Ok, Thanks for the clarification. Yes, Dev and Prod different HANA instances. So, I can't use UPS to get access on HANA prod instance schema?

So In that case I will need to use SDA to get the virtual tables created in my HANA Dev as Catalog objects (as a non HDI container). Then I create an UPS to access my Dev HANA objects and hdbgrants file will be used to grant the access on virtual tables to my HDI container. Then I can create a synonym file to target the virtual tables created in HANA dev.

Please let me know if it is a feasible approach or any other option available?

[Cocquerel · Accepted solution]

it would work but you can also create the virtual table directly in your HDI container using .hdbvirtualtable artifact ( see https://help.sap.com/docs/SAP_HANA_PLATFORM/3823b0f33420468ba5f1cf7f59bd6bd9/08191144d5804c86af9395b... ) . In this case, no need of synonyms.

[ssurampally · Accepted solution]

Thanks for the input Michael, Actually I may not need these tables be in QA and Prod so I will keep them outside of hdi and can keep them in Catalog of Dev.

[Cocquerel · Accepted solution]

yes, but you will face the same issue with the synonyms. When you will deploy your hdi container to QA and prod, it will failed because the virtual tables target of your .hdbsynonyms do not exist.