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.
Figure 1: CUPS entry in mta.yaml file example
Also, add a dependency of this service in HDB module of your project.
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.
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.
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.
Figure 5: HDI container service entry in mta.yaml file example
Add a dependency of this service in HDB module of your project.
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.
Figure 7: .hdbsynonymgrantor file using container specific roles example
Creating synonym
Create a .hdbsynonym file in ‘src’ folder of your HDB module.
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.
good job Sanam
Have anybody some ideas, how to get access from my HDI container to tables or CDS entities in another HDI container?
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.
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?
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.
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.
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.
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?
>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.
Thanks! Looking forward to see this docs also as new materials and hana academy videos as I can see activity at your github account.
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]
"
Had exactly the same problem and wasn't sure if we need to give "privileges_with_grant_option"!
Each advise would be very nice!
I think the name of your role itself is the key to this error:
There are two kinds of role names:
A role whose name ends with # can only be granted via the HDI APIs to a container’s object owner (“<container>#OO”).
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
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.
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
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.
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
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.
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?
>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
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
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?
Hello Thomas,
no we are not able to preview the data.
Regards,
Navin
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?
Thanks a lot for update! I will try to use your cross container example.
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}
please see my other comment 🙂 Thankyou
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
Nevermind!
The cups had the wrong password! Thankyou for your help!
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.
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.
Any idea what schema this is trying to create a temporary table in?
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?
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:
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'
<<<<.
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
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
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