Synonyms (SAP HANA)

How to Create Synonyms

This how to is based on a fictional existing schema "USER_DB" which contains a table "EMPLOYEES".

CREATE SCHEMA USER_DB
CREATE TABLE USER_DB.EMPLOYEES (id INT PRIMARY KEY, firstname VARCHAR(50), lastname VARCHAR(50)) 

Create User Provided Service in XSA Cockpit

USER_DB_UPS

{
  "host": "localhost",
  "port": "30015",
  "driver": "com.sap.db.jdbc.Driver",
  "schema": "USER_DB",
  "user": "  <user-db-schema-user> “,
  "password": " < password >”,
  "tags": ["hana"]
}

Verify USER_DB_UPS in Database Explorer by adding it from HDI containers

Add User Provided Service to mta.yaml

Add the user proviced service as a resource to the MTA project

Create new resource "USER_DB_CROSS_SCHEMA_SERVICE"

1. Type: org.cloudfoundry.existing-service
2. Parameters: service-name = USER_DB_UPS
3. Properties: ups_name = ${service-name}

Change DB module

1. Requires: USER_DB_CROSS_SCHEMA_SERVICE, group = SERVICE_REPLACEMENTS
2. Properties:
   1. key = USER_DB_CROSS_SCHEMA_SERVICE
   2. service = ~{ups_name}

Assign Privileges

In order to assign the priviledges from the UPS to the object owner user of the HDI container create new file in /db/cfg/user_db_schema.hdbgrants

The user in the UPS needs permissions to GRANT SELECT, SELECT METADATA and SELECT CDS METADATA

{
    "USER_DB_UPS": {
        "object_owner": {
            "schema_privileges": [{
                "reference": "USER_DB",
                "privileges_with_grant_option": ["SELECT METADATA", "SELECT CDS METADATA", "SELECT"]
                }]
        },
        "application_user": {
            "schema_privileges": [{
                "reference": "USER_DB",
                "privileges_with_grant_option": ["SELECT METADATA", "SELECT CDS METADATA", "SELECT"]
                }]
           }
     }
}

Create Synonym

Create /db/src/user_db.hdbsynonym with following content:

{
    "user_db_employees": {
        "target": { 
            "object": "EMPLOYEES",
  	    "schema": "USER_DB"
         }
    }
} 

Test synonym i.e. with a Calculation View based on it.

Public Tutorials from SAP

1. https://developers.sap.com/tutorials/xsa-create-user-provided-anonymous-service.html
2. https://developers.sap.com/tutorials/haas-dm-access-cross-container-schema.html