Skip to Content
Technical Articles

How to use objects contained in a schema outside of your Web IDE Full-Stack project in SAP HANA Service

If you are working in Web IDE full-stack in a SAP HANA Service environment and you would like to consume objects that are not part of your project, you can achieve this with the following three steps:

  1. Connect with a service to the schema
  2. Assign the necessary authorizations to the technical users of your project
  3. Create synonyms pointing to the objects

 

All of these steps are described in an on-premise context for example in documetation or blog1blog2. For a recent example that also includes loading data and accessing external schemas in the context of SAP HANA Service see blog3

To make the start of modeling a bit easier the blog shows the steps of consuming objects outside of your project in SAP HANA Service. The example assumes that table “SalesOrder” in schema “BICERTDATA” should be consumed in a Calculation View and that you create a user provided service “UPS” to this end.

1. Connect with a service to the schema

In the SPACE of your instance, select the option “User Provided Service” under Services

 

If a user provided service that fulfills your requirements does not already exist you can create it with button “New Instance”.

The following definition would create a user provided service called “UPS” that is based on a database user UPSUSER with password “secret1234”

{

“desc”: “example”,

“user”: “UPSUSER”,

“password”: ” secret1234″,

“tags”: [

“hana”

]

}

 

Make sure that the database user UPSUSER exists, has the authorization to GRANT SELECT WITH GRANT OPTION on table “SalesOrder”, and uses this password. The GRANT SELECT WITH GRANT OPTION does not need to be assigned directly to the UPSUSER as long as the UPSUSER can grant a role with the respective privilege.

Next, you have to add the service to your project by including it in your .yaml file. To do so right-click on the .yaml file of your project and select “Open Code Editor”. Below is a simple yaml file. The changes due to the added user provided service named “UPS” are marked in bold:

ID: Demo
_schema-version: ‘2.1’
version: 0.0.1

modules:
– name: db
type: hdb
path: db
requires:
– name: hdi_db
  properties:
        TARGET_CONTAINER: ~{hdi-container-name}
       
    – name: UPS
      group: SERVICE_REPLACEMENTS

      properties:
        key: ServiceName_1
        service: ~{the-service-name}

resources:
– name: hdi_db
properties:
hdi-container-name: ${service-name}
type: com.sap.xs.hdi-container

 – name: UPS
   parameters:
      service-name: UPS
   properties:
      the-service-name: ${service-name}
   type: org.cloudfoundry.existing-service

 

With newer Web IDE versions there is also a graphical support for creating and adding the user provided service by right-clicking on the database module and selecting “HANA Service Connection”

 

In the dialog, you can either reuse an already existing user provided service and only add the reference to the service into the .yaml file, or create a new user provided service that is then also automatically added to the .yaml file:

 

2. Assign the necessary authorizations to the technical users

Next you will need to create and build a hdbgrants file. This file should grant SELECT WITH GRANT OPTION on objects that are to be used in e.g., Calculation Views to the user “object_owner”. In most situations no additional privileges are needed for the application user. As best practice, these privileges should be granted via roles.

{
“UPS”: {
“object_owner”: {
“roles”: [
“roleForObjectOwner”
]
},
“application_user”: {
“roles”: [
“roleForApplicationUser”
]
}
}
}

The roles “roleForApplicationUser” and “roleForObjectOwner” can be creates as SQL roles. Make sure that the database user behind the user provided service (UPSUSER) has been granted the authorization to grant these roles and has at least privilege SELECT METADATA on the underlying objects that should be used in Calculation Views. In addition UPSUSER should have system generated role “PUBLIC” (see also question “In a nutshell: What authorizations do I need to start modelling?” here)

 

3. Create synonyms pointing to the objects

Finally, you need to create and build synonyms that point to the respective objects. The following description would create synonym “SalesOrder” pointing to table “SalesOrder” in schema “BICERTDATA”:

{

“SalesOrder”: {

“target”: {

“object”: ” SalesOrder”,

“schema”: ” BICERTDATA”

}

}

}

 

You might want to use the .hdbsynonymconfig approach that is also detailed at the help portal to achieve more flexibility when addressing the source schema, e.g., using no fixed schema but fill the schema by the user provided service. This flexibility can come handy in a transport context.

With these steps you should now be able to use the object in e.g., your Calculation View. Simply select the object with the created synonym in the “Add Data Source” step of your Calculation View and press “Finish”

 

 

Alternatively, with newer versions of Web IDE you can create the synonym via the “Add Data Source” dialog (button “Create Synonym”) and thus do not have to define the synonyms manually beforehand.

If you do not want to create the synonyms when adding data sources but would like to use a graphical editor, you can also create synonyms for all objects in a schema by double clicking on a .hdbsynonym file and selecting “Mass Import of Synonyms”:

 

5 Comments
You must be Logged on to comment or reply to a post.
  • Hi Jan, Thanks for the blog.

    I am able to use the user provided service created for a Schema1 and also another service created for Schema2 with out any issues. I have created 2 separate .hdbgrants files.

    But I am able to use only one service at a time, DB module build is failed when I use both services in my project.

    Error: service ServiceName_2 not found; the service definition does not exist.

    Can you help here?

    Thanks.

    • Hi Oguri,

      the error message indicates that no user provided service exists in your SPACE with name “ServiceName_2”. At the same time you seem to refer to this service name in your project.

      How did you create the user provided services. Did you use the dialog of “Add external service”? This should automatically create the correct services (if you do not tick “Existing User Provided Service”.) and should add the service to your .yaml file.

      Please check, where you have defined “ServiceName_2”. You can for example use the magnifying glass icon on the right in Web IDE to search for the string. Alternatively, the log should also indicate in which file the definition occurs.

      Depending on your intention you will need to replace the service name with the respective existing user provided service, or create the user provided service. In case you create the user provided service via the dialog “Add external service” the service will automatically added to the .yaml file. This might lead to duplicates ( you can check by right-click on .yaml and open in editor)

      Best,

      Jan

      • Hi Jan, The two services are tested and working fine with respective .hdbgrants file entries. (if we use them one at a time)

        And If both services are used in the same project then the issue occurs. This issue is identified with group: SERVICE_REPLACEMENTS in mta.yaml file.

         

        It is working fine if we remove group: SERVICE_REPLACEMENTS and maintain simply the original Service name. SAP recommends to use SERVICE_REPLACEMENTS.

         

        Below way is not working…

              - name: cross-container-service-1
                group: SERVICE_REPLACEMENTS
        
                properties:
                  key: ServiceName_1
                  service: ~{the-service-name}
                                    
        
              - name: cross-container-service-2
                group: SERVICE_REPLACEMENTS
        
                properties:
                  key: ServiceName_2
                  service: ~{the-service-name}
                                    
        ----
        ----
        ----
        
          - name: cross-container-service-1
            parameters:
               service-name: SCHEMA1-grantor
            properties:
               the-service-name: ${service-name}
            type: org.cloudfoundry.existing-service
        
        
          - name: cross-container-service-2
            parameters:
               service-name: SCHEMA2-grantor
            properties:
               the-service-name: ${service-name}
            type: org.cloudfoundry.existing-service
        

         

        But this below way is working fine.

        I want to identify what exactly is the issue with group: SERVICE_REPLACEMENTS

              - name: SCHEMA1-grantor
              
              - name: SCHEMA2-grantor
        
        ----
        ----
        ----
        
          - name: SCHEMA1-grantor
            type: org.cloudfoundry.existing-service
        
          - name: SCHEMA2-grantor
            type: org.cloudfoundry.existing-service
        

        Thanks in Advance.

        • Hi Oguri,

          With service replacement (see e.g., here) it is possible to refer to a service under a different name than the name with which the service has been defined. With this option you can refer to differently named services in different systems and still keep the reference in files like e.g., .hdbgrants the same. The only change would be the service-name in the yaml file.

          Your definition looks OK to me and I could not reproduce your error with your definition (using two .hdbgrants files). It might be best to raise an incident to SAP to have a closer look what is going on in your system.

          Best,

          Jan