Skip to Content
Technical Articles

XSA Accessing Remote Sources & External Objects (Schemas, etc)

When developing with XSA and the WebIDE you will likely need to access existing database objects, schemas, tables, remote sources or other objects from an HDI Container.  This configuration has been captured before by Christophe Gilde, but the process has evolved with the latest feature release of the WebIDE (4.3.63 for HANA 2 SPS3).

Tenant Database Objects

1. Role & User

XSA Artificats

2. User-Provided Service
3. mta.yaml
4. .hdbgrants
5. .hdbsynonym

 

1. Role & User

For Simplicity we have combined the classic database privileges into a single role “GRANT_REMOTE_SOURCES”.  We could also grant access to specific privileges as described here Enable Access to Objects in a Classic Schema

CREATE ROLE GRANT_REMOTE_SOURCES;

GRANT SELECT, EXECUTE ON SCHEMA FAKENEWS TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE FILE_LOADER TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE FILE_LOADER TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
GRANT ROLE ADMIN TO GRANT_REMOTE_SOURCES;

DROP USER GRANTOR_SERVICE;
CREATE USER GRANTOR_SERVICE PASSWORD NotMyPassword123 NO FORCE_FIRST_PASSWORD_CHANGE;
ALTER USER GRANTOR_SERVICE DISABLE PASSWORD LIFETIME;

GRANT GRANT_REMOTE_SOURCES TO GRANTOR_SERVICE WITH ADMIN OPTION;

We can check in HANA Studio that these permission are as expected.

Now that we have a user with the role assigned we can switch to our XSA developement

XSA Artificats

2. User-Defined Service

We can now create the user defined service with either WebIDE, XSA Cockpit or XS command line.

In the WebIDE we need a project

We need associate the project with the correct space can then build the db unit of this.
Now we can add/create our User-Defined Service

If we haven’t already created the service we can do this here.

Beware, the port is that of your tenant database, the default would be 30015, but I have multiple tenants so my port is 30041.

3. mta.yaml

By adding this service in the WebIDE it will automatically update the mta.yaml file, which is a good thing.  The mta.yaml hold the resources that our project requires.  This now references our user-provided service.

An alternative way to create the user-provided service is with the xs command line.  Make sure you are in the correct xs SPACE, here mine is PROD

xs t -s PROD
xs cups grantor-service -p '{"host":"mo-3fda111e5.mo.sap.corp","port":"30015","user":"GRANTOR_SERVICE","password":"NotMyPassword123","driver":"com.sap.db.jdbc.Driver", "tags":["hana"]}'
xs service grantor-service

You can still use the WebIDE, but now you would tick the box “use existing service” and you would only need to enter the service name.

Now when I build the db module again it will create a binding for this service to the di-builder

We can see (and create/edit) this in the XSA Cockpit

 

4. .hdbgrants

We now need to pass on the role “GRANT_REMOTE_SOURCES” that we defined above to our HDI Container.  This is done by creating an .hdbgrants file within your project src directory.

{
	"grantor-service": {
		"object_owner": {
			"roles": [
				"GRANT_REMOTE_SOURCES"
			]
		},
		"application_user": {
			"roles": [
				"GRANT_REMOTE_SOURCES"
			]
		}
	}
}

We should now build the db module of the project, all being well we will now have access to our existing database objects, in my case Remote Source and the FAKENEWS schema and tables.

5. .hdbsynonym

If we create a Calc View and search for a table from existing the schema we need to click the “External Services” drop down and then our grantor-service. This will then automatically create the required synonyms for us.

 

41 Comments
You must be Logged on to comment or reply to a post.
  • Hi Ian,

    one question regarding the port number for the non-hdi tenants: do you have an idea where I can find the port numbers? Currently I’m only able to use tables from the system database.

    If I look at the connection infos, both, the system database and the tenant “hxe”, use the same port.

    Info: I’m using a HANA Express 2.0 SPS 3 installation.

  • Hi Uwe,

    Yes, with HANA Express the instance is 90.

    System database port is 39013

    Tenant database should be 39015

    • Hi Ian,

      yes, that’s it. Found it in the documentation: for each new tenant add 2 to the system tenant port number. 39013 + 2 -> 39015

      Thank you.

  • Hi Ian,

    thanks for the detailed explanation. We recently upgraded to SAP WebIDE for SAP HANA SPS 04 Patch 1 ( Build 4.4.12 ). Unfortunately the option within the WebIDE “Add External HANA Services” does not appear in the drop down menu.

    When we come to the step adding the synonyms we get the Error: Incorrect Service

    Do you have any idea or any advice?

    Kind regards

    Markus

     

    • Hi Markus,

      Thanks for the feedback.  Are you right clicking on the db module?

      Can you share what you see within your project?

      The synonyms rely on the external service, so we must create that first.

      As a more difficult method you can use on of the other methods to create the service and then update the mat.yaml manually.

  • Hi Ian,

    thanks for your reply. I am right clicking on the db module, but the Add external HANA Service option does not appear.

    I am trying to use the other methods by editing the mta.yaml file, but I musst miss something, because when I get to the part adding a synonom for a database table in a calculation view, I do get the error:

    Do you have any advice?

    Kind regards

    Markus

  • Hi Ian,

    I can build the db module and the build the process is successful. Here is the ouput.

    21:10:40 (Builder) Build of “/ewenetz_daten_signifikante_lasten” started.
    21:10:43 (DIBuild) Build of “/ewenetz_daten_signifikante_lasten” in progress.
    21:10:43 (DIBuild) [INFO] Target platform is XSA
    [INFO] Reading mta.yaml
    [INFO] Processing mta.yaml
    [INFO] Processing module db
    21:10:45 (DIBuild) [INFO] Logs of build task for module db:
    > [INFO] Injecting source code into builder…
    > [INFO] Source code injection finished
    > [INFO] ————————————————————————
    > Your module contains a package.json file, it will be used for the build.
    > up to date in 0.335s

    [INFO] Creating MTA archive

    21:10:47 (DIBuild) [INFO] Saving MTA archive ewenetz_daten_signifikante_lasten_0.0.1.mtar
    [INFO] Done
    21:10:47 (DIBuild) ********** End of /ewenetz_daten_signifikante_lasten Build Log **********
    21:10:47 (Builder) The .mtar file build artifact was generated under /mta_archives/ewenetz_daten_signifikante_lasten.
    21:10:49 (Builder) Build of /ewenetz_daten_signifikante_lasten completed successfully.
  • Do you now see the “Add External HANA Service” on the right click menu?  Or is it the same as before?

    • I have not tried the version you are using, but normally the features would remain.

      You can still achieve the same by adding the service manually via either XSA cockpit or XS CLI.  You would then need to update the mta.yaml to refer to this service.  Once that’s done build your db module and proceed from there.

      • Hi Ian,

        that is what we did. I added the artefacts manually in the code editor. We still get the error: Incorrect Service, when we want to use the synonym within the calculation view. When I open the HDI container in the data explorer, the synonyms works fine.

        Any thoughts?

        • Hi Markus,

          Are you saying that you have created some synonyms using .hdbsynonym files in your project and these are working in the DB Explorer? Were these created with the code editor?

          If you open this file with synonym editor and click the … by the object name can you see you the service you have defined?

          Can you also try removing the HDI container from DB Explorer and Opening the HDI Container from the Developer perspective again.

          /
          • Hi Ian,

            I have created the objects in the code editor. I have removed the HDI container from the DB Explorer and added the container from the development. The synonyms are appearing, but I canno tload the table content via the synonyms any more.

            When I open the synonym with the Editor and I am trying to add another synonym, i do get the same error saying Incorrect Service.

    • Hi Markus, @Ian Henry, Were you able to get the option we too are getting same issue no option shown for add external HANA service other than that also while creating synonym there is no option also to search for HANA services we were hand installed SAP WebIDE for SAP HANA SPS 04 Patch 1 not showing then upgraded to patch3 still not showing? any idea what is missing

       

      /
  • /
  • Hi Ian Henry,,

     

    Thank you for your feedback to me.

    I try to build database module in HDI container. (remove .hdbgrants)

     

    I have created the GRANTOR_SERVICE user and provide the role in the SYSTEM schema below.

     

    and the User-Provided Services

     

    Do you have any ideas for my error?

     

    Best Regards,

    Ditthanan P.

  • Hi Ian,

    I have recently migrated Xs classic to XSA and in the process we got report from migration tool that now SYS.USER have different landscape here in XSA.

    Can you please tell me what is the alternative of SYS.USER in XSA.

    Thanks

  • Hi Ian,

    I have successfully created External SAP HANA Service, Also i can see MTA.YAML file gets updated automatically. I am also done with Successful DB Module Build.

    But i am getting error in building hdbgrants file.

    Processing grants files…
    Processing “src/grants1.hdbgrants”…
    Error: service grantor-service not found; the service definition does not exist.

     

    I can see grantor-service in XSA Cockpit

     

    Also MTA.YAML file gets updated with grantor-service

     

    Can you please help me on this issue?

     

    Thanks

    Prakash

  • Hi Ian,

    Thanks for blog.

    I have one question related to Step1. Role& User

    What is the FILE_LOADER here

    when we try to run the same command, we got error. Please let me know

     

    GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE FILE_LOADER TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
    GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE FILE_LOADER TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
    GRANT ROLE ADMIN TO GRANT_REMOTE_SOURCES;
      • Yes, in my Case i have ECC schema in HANA Classic ( which are replicated via SLT).

        Need to access those tables as Synonyms in XSA\Web IDE.

        i have already skipped those 2 lines relevant to FILE LOADER and able to create synonyms but unable to build them as runtime objects

        Please advise

        • It sounds like you don’t have any remote sources.

          You should just be adding the external objects, in this case the ECC schema with grant option, similar to step 1 in this blog.

  • /
    • Hi Corina,

      It appears that there’s a problem with your grantor-service definition.  Perhaps the password has changed or expired?  Double check the hana host and port too, step 3 of this blog shows you how to do this.

      Cheers, Ian.

  • Hi Ian,

     

    I followed your blog and try to build the solution but I got an error when I build the .hdbgrants file.

    SAP Web IDE for SAP HANA
    Version: SAP Web IDE for HANA 2
    SPS 04 Patch 7 ( Build 4.4.20 )

    I created the the following default access roles file but still have the error.

     

    Do you have any ideas?

    Thx,

    Peter

  • Hi Peter,

    The error seems to point to the user XXX0688 not existing.

    Can you remove/rename the *access_role.hdbrole and try to rebuild?

    • Hi Ian,

      I removed the all *access_role.hdbrole and rebuild but I have the same error. My question is HDI will generate 3 users in HANA DB xxx9688 itself xxx9688#OO and xxx9688#DI, we don’t have any user with xxx9688::access_role.

       

      For user xxx9688 already exist and granted Create Any so this user should have all access. Why the XSA complaint the user xxx9688::access_role is missing since ::access_role is a role not a user.

      We followed your example to create and the only difference might be we use SAP txn SCTS_AMHC to generate the HDI container.

      Thx,

       

      Peter

  • Hi Ian,

    I’ve been trying to connect to a classic schema which resides in a HANA instance on a physical host that is different from my HXE/XSA host. After creating a grantor service via xcups, I am able to see the remote schema/tables during the definition of synonyms in WebIDE’s synonym editor. However, when I try to actually build the synonym, I am always faced with the following error message: “Error: com.sap.hana.di.synonym: The “RESULTDB.PERFORMANCE_TEST” synonym target does not exist [8250501] at “src/synonyms/test.hdbsynonym” (0:0)”. I have tried this multiple times from scratch, but had unfortunately no success yet.

    Things seem to work though, when importing the classic schema into the local HXE tenant DB (and pointing the grantor service to the local DB). Is there any additional step I have to take when trying to establish access to a different HANA node?

    Many thanks,

    Karsten

    • Hi Karsten,

      It sounds like you could have a missing piece of the puzzle.

      1.Missing the GRANT OPTION on the classic schema privilege, see Step1.

      2.Haven’t got a .hdbgrants to pass this on to you HDI user, see Step4.

      • Hi Ian,

        The grant options seem defined properly (I have done both, step 1. and 4.). When having the classic schema inside the local HXE tenant DB and dropping the respective access roles, I get the error that container object owner HDB_#OO” cannot access the schema. Adding the role resolves this (as expected).

        However, when creating an additional tenant DB on the HXE host (or on any other host) and loading the classic schema into that second tenant, I am running into the following problem:

        Using grantor service “grantor-service” of type “sql”
        Error: Error executing: GRANT “GRANT_REMOTE_SOURCES” TO “DB_1#OO”;
        (nested message: invalid user name: DB_1#OO: line 1 col 33 (at pos 32))
        grantor service: “grantor-service”, type: “sql”, user: “GRANTOR_SERVICE”
        file name: cfg/rundeck.hdbgrants

        Obviously, the user DB_1#OO only exists in the primary XSA tenant DB and not in the secondary tenant. How can the remote data access work, when the DB_1#OO user is not part of the second /remote tenant DB? In most XSA tutorials the classic schema seems to be inside the same tenant DB that is used by the XSA instance for hdi-containers…

        Also, I noticed that you grant access to a REMOTE SOURCE in your example above…that’s the part I haven’t defined. I was assuming that schema access will be established through the user-provided-service (grantor_service in your case) and doesn’t require HANA’s smart data integration feature…am I wrong here and could this be the issue?

         

        • Hi Ian,

          Did you manage to solve this issue ? I am also having the same error when accessing a schema which is on a different tenant.

  • Hey Ian Henry,,

    Would a possible solution to the above issue be to create the missing database users in the target tenant, and then try the build on .hdbgrants? For example, in Karsten Molka case, he could create user DB_1#OO in the different target tenant (NOT the xsa tenant).

  • Hello,

    Thanks for the nice blog.Can you please check and tell me why when deploying i am getting service instance not available

    ID: helloclworld
    _schema-version: '2.1'
    description: hello
    version: 0.1.34
    modules:
      - name: helloclworld-db
        type: hdb
        path: db
        parameters:
          memory: 256M
          disk-quota: 512M
        requires:
          - name: helloclworld-db-hdi-container
            properties:
              TARGET_CONTAINER: '~{hdi-container-name}'
          - name: consumed-core-container
            group: SERVICE_REPLACEMENTS
            properties:
              key: ServiceName_1
              service: '~{consumed-service-name}'
      - name: helloclworld-xsjs
        type: nodejs
        path: xsjs
        parameters:
          service-name: helloclworld-xsjs
          service-url: '${default-url}'
        provides:
          - name: xsjs_api
            properties:
              url: '${default-url}'
        requires:
          - name: helloclworld-db-hdi-container
          - name: helloclworld-db
          - name: helloclworld-uaa
          - name: helloclworld-dest
          - name: helloclworld-connect
      - name: helloclworld-srv
        type: java
        path: srv
        parameters:
          memory: 1024M
          service-name: helloclworld-srv
          service-url: '${default-url}'
        provides:
          - name: srv_api
            properties:
              url: '${default-url}'
        requires:
          - name: helloclworld-db-hdi-container
            properties:
              JBP_CONFIG_RESOURCE_CONFIGURATION: '[tomcat/webapps/ROOT/META-INF/context.xml: {"service_name_for_DefaultDB" : "~{hdi-container-name}"}]'
          - name: helloclworld-uaa
          - name: helloclworld-dest
          - name: helloclworld-connect
      - name: helloclworld-web
        type: html5
        path: web
        parameters:
          disk-quota: 512M
          memory: 256M
          service-name: helloclworld-web
          service-url: '${default-url}'
        build-parameters:
          builder: grunt
        requires:
          - name: xsjs_api
            group: destinations
            properties:
              forwardAuthToken: true
              url: '~{url}'
              name: xsjs_api
          - name: helloclworld-uaa
          - name: helloclworld-dest
          - name: helloclworld-connect
      - name: helloclworld-web2
        type: html5
        path: web2
        parameters:
          disk-quota: 512M
          memory: 256M
          service-name: helloclworld-web2
          service-url: '${default-url}'
        build-parameters:
          builder: grunt
        requires:
          - name: xsjs_api
            group: destinations
            properties:
              forwardAuthToken: true
              url: '~{url}'
              name: xsjs_api
          - name: helloclworld-uaa
          - name: helloclworld-dest
          - name: helloclworld-connect
          - name: srv_api
            group: destinations
            properties:
              forwardAuthToken: true
              name: srv_api
              url: '~{url}'
              strictSSL: false
    resources:
      - name: helloclworld-db-hdi-container
        type: com.sap.xs.hdi-container
        properties:
          hdi-container-name: '${service-name}'
      - name: helloclworld-uaa
        type: org.cloudfoundry.managed-service
        parameters:
          path: ./xs-security.json
          service-plan: application
          service: xsuaa
      - name: consumed-core-container
        type: org.cloudfoundry.existing-service
        parameters:
          service-name: consumed-db
        properties:
          consumed-service-name: '${service-name}'
      - name: helloclworld-dest
        parameters:
          service-plan: lite
          service: destination
        type: org.cloudfoundry.managed-service
      - name: helloclworld-connect
        type: org.cloudfoundry.managed-service
        parameters:
          service: connectivity
          service-plan: lite
      - name: consumed-db
        type: org.cloudfoundry.existing-service
        parameters:
          service-name: consumed-db
    
    
    I am sorry, I am still getting deployment errors after matching to your code. Sorry to bother you again , can you please check and let me know what mistake i am doing
    
    "consumed-db": 404 Not Found: Service instance consumed-db not found.  This is the error. and consumed-db is there in my yaml file