Skip to Content
Author's profile photo Sanampreet Singh

XS Advanced features: Synonyms.

This blog will give you information on how to use objects of one HDI container into another and objects of a non-HDI container schema into HDI container .


A word about HDI Containers


As we enter the world of XS Advanced, we come across many new terms and one of them is “HDI container”.

You can think of it as a database schema. It abstracts the actual physical schema and provides schema-less development. You can read more about them in the blog written by Thomas Jung. Please visit http://scn.sap.com/community/developer-center/hana/blog/2015/12/08/sap-hana-sps-11-new-developer-features-hdi

The key points that we need to emphasize while working with the HDI containers are:

  • A database schema and a technical user also gets created in the HANA database for every container. All the run time objects from the container like tables, views, procedures etc. sit in this schema and not in the schema bind to your database user.
  • All the database object definitions and access logic has to be written in a schema-free way.
  • Only local object access is allowed. It means that you can only access the objects local to your container. You can also access the objects of other containers and non-HDI container schemas (foreign schemas) but via synonyms as long as the technical user of the HDI schema has been granted access to this foreign schema.

1. Creating Synonyms for non-HDI schema objects

Now we will be looking at an example of creating synonyms for the objects of a non-HDI container schema (foreign schema) in a HDI container.

This example is based on SPS 12 and uses both XS command line tool and SAP Web IDE for SAP HANA (XS Advanced) tool.

Prerequisites:

  • You should have a database user who should be able to access XSA Web IDE tool.
  • Your database user should have the authorization (WITH GRANT OPTION) on the foreign schema.

Let’s start with the example step by step.

Create a user provided service.

We have to create a user provide service for the foreign schema. Open XSA client tools and login using your user by issuing ‘xs login’ command.

Now create user service by issuing ‘xs create-user-provided-service’ or ‘xs cups’ command. You can use the following syntax:


xs cups <service-name> -p "{\"host\":\"<host-name>\",\"port\":\"<port-number>\",
\"user\":\"<username>\",\"password\":\"<password>\",\"driver\":\"com.sap.db.jdbc.Driver\",
\"tags\":[\"hana\"] , \"schema\":\"<foreign schema name>\" }"

Modifying mta.yaml file.

We have to correctly configure all services including the user provided service in the mta.yaml file. This allows using the user provided service within the project.

Add an entry of the user provided service you created in ‘resources’ section of mta.yaml file.

mta1.JPG

Figure 1: CUPS entry in mta.yaml file example

Also, add a dependency of this service in HDB module of your project.

mta_new.JPG

Figure 2: CUPS dependency in HDB module in mta.yaml file



Creating .hdbsynonymgrantor file.

This file specifies the necessary privileges to access external tables. Open XSA Web IDE and under HDB module of your project create a new folder with name ‘cfg’. Just like the ‘src’ folder, its name is special. This tells the HDI deployer that this folder contains configuration files and treats them appropriately.

Create your .hdbsynonymgrantor file under this folder.

grantor_file_new.JPG

Figure 3: .hdbsynonymgrantor file example


Creating synonym for external object

Create a .hdbsynonym file in ‘src’ folder of your HDB module. In one .hdbsynonym file you can define multiple synonyms to be used in your project.

synonym_new.JPG

Figure 4: .hdbsynonym file example


Now, you should be able to use these tables of foreign schema in your container using these synonyms.



2. Creating Synonyms for other HDI container objects

All the steps for creating synonyms for other HDI container objects are very much similar to the above steps with some minor changes.

Prerequisites:

Before you start doing anything please keep in mind that HDI container object privileges can only be granted to other containers via container local roles.

So you have to deploy one or more .hdbrole files defining object privileges to the ‘grantor container’ (foreign container).

In this scenario we don’t have to create CUPS for the other container as a service already exist for that. We can find the name of this service using HRTT tools and checking the details of the HDI container or you check that by issuing ‘xs services’ command in XSA client tools.The service name should be having your database user and workspace prefixed to it.

Modifying mta.yaml file.

After we have the required service name, we have to modify mta.yaml file.

Add an entry of the user provided service you created in ‘resources’ section.

mta3.JPG

Figure 5: HDI container service entry in mta.yaml file example


Add a dependency of this service in HDB module of your project.

mta4.JPG

Figure 6: HDI container service dependency in HDB module in mta.yaml file

Creating .hdbsynonymgrantor file.

This file specifies the necessary privileges to access tables of other container.

Create your .hdbsynonymgrantor file under ‘cfg’ folder of your HDB module. You have to reference the local role(s) of the foreign schema in the ‘container_roles’ sections of a this file.

grantor2.JPG

Figure 7: .hdbsynonymgrantor file using container specific roles example



Creating synonym

Create a .hdbsynonym file in ‘src’ folder of your HDB module.

synonym2.JPG

Figure 8: .hdbsynonym file using container specific schema example

Now you should be able to use the objects of other HDI container using these snonyms.

Assigned Tags

      37 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Megha Gupta
      Megha Gupta

      good job Sanam

      Author's profile photo Former Member
      Former Member

      Have anybody some ideas, how to get access from my HDI container to tables or CDS entities in another HDI container?

      Author's profile photo Sachin C
      Sachin C

      Hi Nikita,

      For HDI Container you do not need to create any User provided service. Because such a service is already present for your other HDI container. Hence wherever you would have given the name of the User provided service, like in the yaml file or the grantor file, you need to give name of the service for the other HDI container.

      Author's profile photo Former Member
      Former Member

      And how I can use tables from dbmodule1 in container 1 in dbmodule2 in container 2?

      1. I can't make synonym because development is schema-less but synonym needs schema definition. What i need to do to expose table from dbmodule1 to dbmodule2?

      2. And what about usage of tables in dbmodules in different apps?

      Author's profile photo Sachin C
      Sachin C

      Schema less does not mean that there is no schema, It is created for any application which is bound to an HDI Container service. Its just that the application code need not be aware of the schema name because there is only a single schema an application can access. (to access other schemas we need to have synonyms).

      May be you are confused about HDI containers and schemas. Both are very similar concepts. You can even think of the HDI container as a schema.

      Now to figure out the schema name, you need to execute command xs env yourappname, when you can see the details of the services bound to your app. One of the parameters under the HDI container service is Schema.

      Author's profile photo Former Member
      Former Member

      I have tried to build 2 different db modules in different containers:

      1. application: app1; container: container1; module: db1

      2. application: app2; container: container2; module: db2

      Next I have got names of both schemas. Next, i have defined synonym in db2 to table in db1.

      When I've done this, I've got next error:

      [Error: Error executing: GRANT SELECT ON SCHEMA "SMR5IOBUFNCN6VLG_APP1_HDI_CONTAINER" TO "SMR5IOBUFNCN6VLG_APP2_HDI_CONTAINER#OO" WITH GRANT OPTION;

      (nested message: insufficient privilege: Not authorized)]

      What shall I write in grantor file to get this privileges? If I understand things right, user which runs this service must have grant privileges on objects. But this user has not by default.

      Author's profile photo Thomas Jung
      Thomas Jung

      Since both are HDI Containers, the foreign container needs to define an HDBROLE with the necessary access. In the HDBSYNONYMGRANTOR of project #2, you can list this role. The foreign container technical user should be able to grant the role with the necessary access to the target container then. I would suggest you refer to the readme.md document of the hdideploy module in the JavaScript Node.js modules delivered with XSA for a nice explanation of this process.

      Author's profile photo Former Member
      Former Member

      Hi, Thomas. Nice to see you in this discussion.

      It looks like module structure and distrubition content have changed since release of sps12, so I was not able to find README.md file in sps12's xs_javascript-1.6.4-bundle (sap-hdi-deploy version": "1.0.6").

      I was able to find it in sps11's though ( with version": "1.0.0").

      Is it still relevant?

      Unfortunately I'm not an SAP employee and I cannot access links like https://github.wdf.sap.corp/ which i have found at this doc.

      How I can find current version of readme.md and code examples?

      Author's profile photo Thomas Jung
      Thomas Jung

      >Is it still relevant?

      Unfortunately it does look it was removed in the 1.0.6 version, but I can see it in the upcoming 2.0.0 version. Perhaps an oversight.  I'll ask the documentation team.

      Author's profile photo Former Member
      Former Member

      Thanks! Looking forward to see this docs also as new materials and hana academy videos as I can see activity at your github account.

      Author's profile photo Former Member
      Former Member

      Thomas, can you provide some example of "privileges_with_grant_option" definition in hdbrole of grantor container. When i'm trying to do this, i've got next error: "

      ERROR: com.sap.hana.di.role [8254510] "privileges_with_grant_option": the key in "object_privileges" in the "master.roles::master" role has to end with "#"

      at "src/master/roles/master.hdbrole" [0:0]

      "

      Author's profile photo Dirk Raschke
      Dirk Raschke

      Had exactly the same problem and wasn't sure if we need to give "privileges_with_grant_option"!

      Each advise would be very nice!

      Author's profile photo Thomas Jung
      Thomas Jung

      I think the name of your role itself is the key to this error:

      There are two kinds of role names:

      • If a role name does not end with #, e.g. “MyRole”, then only privileges without grant options and only other roles whose names do not end with # can be included in the role.
      • If a role name ends with #, e.g. “MyRole#”, then it is allowed to include with-grantoption privileges in this role and other roles whose names end with #. 

      A role whose name ends with # can only be granted via the HDI APIs to a container’s object owner (“<container>#OO”). 

      Author's profile photo Sachin C
      Sachin C

      Hi Thomas,

      I followed these steps and synonym creation and deployment worked smoothly. In my CDS file I create a CDS view using this synonym. When I am accessing this CDS view I still get "Insufficient privilege" error.

      I got to know from someone that we need "privilege with grant option" in order to access views created with synonym. But my question here is :

      1) Based on your previous comment, "privileges with grant options" can only be assigned to Container object owners. But in run time we access using application users. So is there a way to get these roles to application users?

      2) I cannot understand why "grant option" is required to access the view. Because grant option is for granting the same privilege to other users right?

      Thanks,
      Sachin

      Author's profile photo Thomas Jung
      Thomas Jung

      Your application user doesn't need with grant. The technical user that is the source of the user provided service which you use to setup the hdbgrants file is the one that needs the with grant option.

      Author's profile photo Sachin C
      Sachin C

      Hi Thomas,

      hdbgrants? I was not aware of such a file till now! Did you mean hdbrole? And in my scenario there is NO user provided service.. The foreign schema is an HDI container.

      Thanks,
      Sachin

      Author's profile photo Thomas Jung
      Thomas Jung

      If your foreign schema is an HDI container, then you don't need a user provided service, but you still do need the HDBGRANTS file. That's the file that makes it possible for the technical user of your consuming container to get rights granted to it for the foreign container.

      Here is just such an example:

      https://github.com/SAP/com.sap.openSAP.hana5.example/blob/master/core-db/cfg/user.hdbgrants

      The hdi-user-service is the service replacement alias name for the foreign target container. Then I list an hdbrole from the foreign container which I want granted to my object owner and application technical users. This grant then takes place at the time of HDI build, but the actual grant is done by the object owner technical user of the foreign container.

      Author's profile photo Sachin C
      Sachin C

      Hi Thomas,

      I was using this file ,but with the extension as .hdbsynonymgrantor! Is that wrong?? I read it from an internal wiki.

      The rest of the parts are more or less similar to my current understanding. What I am confused about is :

      Is the 'object owner' same as the technical user of the container? If yes then who is the application user?

      Thanks,
      Sachin

      Author's profile photo Thomas Jung
      Thomas Jung

      HDBSYNONYMGRANTOR is the old file extension for this artifact originally supported in SPS 11 and the first release of SPS 12.  However as of SPS 12 Patch 1 of the SAP Web IDE for SAP HANA, it is changed to HDBGRANTS.  The old file extensions is supported in parallel for backwards compatibility.

      >Is the ‘object owner’ same as the technical user of the container? I

      There are two technical users generated for each container. One is the object owner. This is the user that during build performs the actual DROP/CREATE/ALTER DDL statements.  To the DB this will be the owner of all artifacts.

      Completely separate is the application technical users.  This is the user used when connecting to the container via application logic (Node.js/xsjs/Java).

      This way you have greater control over the security. You might need greater levels for the object owner than you want to grant to the technical application user.

      Author's profile photo Sachin C
      Sachin C

      So this brings me to my actual question. Using the hdbrole file and the hdbgrants file, you can give privilege to the object owner but NOT to the application user which is the user which accesses the objects during the runtime. So in my application which is an OData exposing application from CDS views, I am able to form the OData metadata.

      But when trying to access the data, I am getting an Insufficient privilege error.

      Which is because the application user does not have the required privileges. How to fix this problem?

      Author's profile photo Thomas Jung
      Thomas Jung

      >Using the hdbrole file and the hdbgrants file, you can give privilege to the object owner but NOT to the application user

      Why not?  The HDBGRANTS has a section for both users. You can even give both users the same role assignments if you wish:
      https://github.com/SAP/com.sap.openSAP.hana5.example/blob/dec2016/core_db/cfg/user.hdbgrants

      Author's profile photo Navin Sahadev
      Navin Sahadev

       

      Hi Thomas,

      I followed these steps and I get an insufficient privilege error while trying to access the data from CDS view which is created using the synonym. The Synonym deployment goes through fine. I could even construct OData metadata from this view. But data access seems to be not possible. I have the synonymgrantor file just like in the github example which you gave. Both the object owner and the application user does NOT have the "privilege with grant"(the role name does not end with #).

      Regards,
      Navin

      Author's profile photo Thomas Jung
      Thomas Jung

      Can you preview the data from the synonym itself from the HRTT?  Is it just the view that you get this error?  Did you add structured privileges to the CDS view?

      Author's profile photo Navin Sahadev
      Navin Sahadev

      Hello Thomas,

      no we are not able to preview the data.

      Regards,
      Navin

      Author's profile photo Thomas Jung
      Thomas Jung

      If you can't even query from the synonym itself, then it seems like something is wrong in your hdbgrants.  It seems like the object owner section got set right, otherwise you won't be able to create the synonym itself.  However it seems like perhaps the application_user section is missing or incorrect.  Can you post any details of your hdbgrants file?

      Author's profile photo Former Member
      Former Member

      Thanks a lot for update! I will try to use your cross container example.

      Author's profile photo Dirk Raschke
      Dirk Raschke

      You have to mention the "TARGET_CONTAINER" property of 'hdi-container' service in your mta.yaml file (under hdb module)


        - name: hdi-container

           properties:

             TARGET_CONTAINER: ~{hdi-service-name}

      Author's profile photo Former Member
      Former Member

      please see my other comment 🙂 Thankyou

      Author's profile photo Former Member
      Former Member

      okay so after reading the builder's source code I see that TARGET_CONTAINER is required. I have now added that, see below:


      - name: hdi-container

           properties:

             TARGET_CONTAINER: ~{hdi-service-name}

      However, I now get this issue:

      > deploy@ start /sapmnt/shared/DM0/xs/ea_data/prd-nohana01/executionroot/597957d2-3711-46cf-94bd-a1c632cbef58/app/tomcat/temp/builder/hdi-builder/builds/build-8139402604860611290/Database

      > node node_modules/sap-hdi-deploy/deploy.js --autoUndeploy

      { [Error: authentication failed]

      message: 'authentication failed',

      code: 10,

      sqlState: '28000',

      level: 1,

      position: 0 }

      Any idea? Thanks

      Author's profile photo Former Member
      Former Member

      Nevermind!

      The cups had the wrong password! Thankyou for your help!

      Author's profile photo Dirk Raschke
      Dirk Raschke

      Hi William,

      I had a lot of problems too, while I was trying to connect to another container.

      Here are a lot of steps described, maybe it will help you: SAP HANA XSA SPS 12: Access to another container

      But I'm not sure, if I passed your last issue.

      Author's profile photo Former Member
      Former Member

      How can I connect to a traditional cds schema? I have tried using the container method but I get this error:

      [Error: Error executing: CREATE LOCAL TEMPORARY ROW TABLE #PRMS LIKE _SYS_DI.TT_PARAMETERS;

      (nested message: insufficient privilege: Not authorized)]

      I have made sure that those roles allow making temporary tables in the schema I want to access.

      Thanks.

      Author's profile photo Former Member
      Former Member

      Any idea what schema this is trying to create a temporary table in?

      Author's profile photo Former Member
      Former Member

      I have built synonym to another container's view with select privilege defined in role.

      When i make select to this synonym in RTT all works fine. Next I have made view and procedure using this synonym, and all deployed fine. But when I try to make select on this view or call this procedure I have next message: "Error: (dberror) insufficient privilege: Not authorized:".

      Any idea?

      Author's profile photo Michelle Esler
      Michelle Esler

      I have been able to successfully create synonyms in my HDI container, however, when I go back at a later point to add a new target object, the module will not deploy.  I see the following errors:

      1:58:17 PM (Builder) Could not provision services for module 'ffa/ffa-db'. Could not create the HDI service for resource 'hdi-container'.
      Error: Could not create service key SharedDevKey: Binding service 7e9c4c34-ca03-4d34-8ba0-32e92eabd505 (service id aa08828f-d813-4f43-9333-78e165aa2fc1, plan id 00c1bb5e-12dd-4e15-9160-16aaef28dd03) on service broker "hdi-broker" (url: https://sgihanar01:30032/hdi-broker) to service key failed due to unexpected return code from broker: REST request PUT to url 'https://sgihanar01:30032/hdi-broker/v2/service_instances/7e9c4c34-ca03-4d34-8ba0-32e92eabd505/service_bindings/8ed5c61a-30c7-4e0c-b15e-0683ecffbf28' had response code 500 (Operation failed due to unexpected server error) with error message:
      >>>>
      stacktrace: com.sap.core.persistence.broker.BrokerException: Failed to create role 'hdi::odata::access_role'
      at com.sap.core.persistence.broker.hana.HanaInstanceFactoryBase.createRole(HanaInstanceFactoryBase.java:75)
      at com.sap.core.persistence.broker.hana.HDIInstanceFactory.createBinding(HDIInstanceFactory.java:185)
      at com.sap.core.persistence.broker.Broker.createBinding(Broker.java:249)
      at com.sap.core.persistence.broker.web.api.BindInstanceHandler.process(BindInstanceHandler.java:57)
      at com.sap.core.persistence.broker.web.DispatcherServlet$HandlerCall.process(DispatcherServlet.java:224)
      at com.sap.core.persistence.broker.web.DispatcherServlet.service(DispatcherServlet.java:98)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
      at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
      at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
      at org.apache.catalina.valves.RemoteIpValve.invoke(RemoteIpValve.java:676)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)
      at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)
      at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:670)
      at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:283)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
      at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
      at java.lang.Thread.run(Thread.java:812)
      Caused by: java.sql.SQLException: Failed to execute 'GRANT SELECT ON _SYS_BI.BIMC_ALL_AUTHORIZED_CUBES TO "hdi::odata::access_role"'
      at com.sap.core.persistence.broker.hana.HanaCommunicator.execute(HanaCommunicator.java:34)
      at com.sap.core.persistence.broker.hana.HanaCommunicator.grant(HanaCommunicator.java:154)
      at com.sap.core.persistence.broker.hana.HanaCommunicator.grant(HanaCommunicator.java:134)
      at com.sap.core.persistence.broker.hana.HanaCommunicator.createRole(HanaCommunicator.java:85)
      at com.sap.core.persistence.broker.hana.HanaInstanceFactoryBase.createRole(HanaInstanceFactoryBase.java:73)
      ... 27 more
      Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [258]: insufficient privilege: Not authorized
      at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:345)
      at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:185)
      at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:102)
      at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1031)
      at com.sap.db.jdbc.CallableStatementSapDB.sendCommand(CallableStatementSapDB.java:1794)
      at com.sap.db.jdbc.StatementSapDB.sendSQL(StatementSapDB.java:947)
      at com.sap.db.jdbc.CallableStatementSapDB.doParse(CallableStatementSapDB.java:191)
      at com.sap.db.jdbc.CallableStatementSapDB.constructor(CallableStatementSapDB.java:144)
      at com.sap.db.jdbc.CallableStatementSapDB.<init>(CallableStatementSapDB.java:89)
      at com.sap.db.jdbc.CallableStatementSapDBFinalize.<init>(CallableStatementSapDBFinalize.java:31)
      at com.sap.db.jdbc.ConnectionSapDB.prepareStatement(ConnectionSapDB.java:1266)
      at com.sap.db.jdbc.trace.Connection.prepareStatement(Connection.java:416)
      at com.sap.core.persistence.broker.hana.HanaCommunicator.execute(HanaCommunicator.java:31)
      ... 31 more

      message: Failed to create role 'hdi::odata::access_role'
      <<<<.

      1:58:17 PM (Builder) Build of /ffa/ffa-db failed.

      I've tried deleting the service, creating a brand new HDB module and restarting di-core application, however, nothing seems to fix the problem.  Any suggestions?

      Thanks!
      Michelle

       

      Author's profile photo Abhishek arora
      Abhishek arora

      Hi Sanampreet,

       

      We have succesfully created Grant file, but while creating the synonym we am getting below error.

       

      Error: com.sap.hana.di.synonym The TABLE_NAME synonym target does not exist [8250501]

       

      Regards,

      Abhishek

      Author's profile photo Sanampreet Singh
      Sanampreet Singh
      Blog Post Author

      Hi Abhishek.

       

      This only means that the table/object in the foreign schema for which you are trying to create synonym is not present. Are you sure that you are giving "fully qualified"/correct name of the object in hdnsynonym file?

      Regards,

      Sanampreet