The previous post added some context to the tutorials on how to set up an instance of the SAP Cloud Platform, SAP HANA Service, get up and running with development and connect the Data Provisioning Agent.
You have created a remote source, or maybe you have an ABAP schema replicated with SLT and you want to access those tables from your database module in Web IDE.
This post replays the process that has been documented for a while for on-premise XS Advanced. There are a few variations for HANA instances in Cloud Foundry.
This tutorial also includes the creation of the external schema and using the fancy new wizard to load data into a table, but the steps for the remote source to work are also covered.
This is of course optional and an exercise to simulate the external schema.
The relevant parts for Smart Data Integration are:
Once the grant is working, you are now set for success. Your HDI container technical user can see and use the remote source created in the database.
You can now proceed to create virtual tables, flowgraphs and/or replication tasks: https://developers.sap.com/tutorials/haas-dm-create-flowgraph-sdi.html
The confusion is very clear... so here is a drawing
UserX has permissions to access the remote source and to bestow those authorizations to other users. To prove that, you could manually log in to the database as UserX and execute a GRANT SQL statement.
The user-provided service delivers the credentials of the UserX (you entered the user and password). This user will be granting its own permissions to the object owner in the HDI container so that this technical user can use the remote source. The permissions that will be granted to the different users are listed in the hdbgrants file.
Last but not least, if you have a physical table in the catalog and you want to reference it in your HDI container, you can create a synonym.
In a normal situation, the technical user would have more permissions than the application user, who may be just fine with read-only-ish.
The wiring happening in the background is even more clear when you try to add an invalid access (like I’m doing in the example below).
The console in Web IDE says: Error executing: GRANT "SOME PERMISSIONS" ON "SOME OBJECT" TO "XX_1#OO"
In the tutorial, UserX is called PLUSR. See how the deployment takes the hdbgrants file and literally executes a GRANT statement. “DB1_1#OO” is the object owner, one of the technical users created with the HDI Container and who will need permissions to the remote source to create a virtual table.
If you want to get other similar error messages, create a user with no permissions to anything or without grant option and use it in the user-provided service. You will get an authorization error for that grant statement.
Comparing to XS Advanced on-premise?
In the on-premise world, this has been out there and documented for a while. There are some differences in the development flow as of this date but the main pitfalls I see are related to lack of permissions in the granting user.
In the on-premise world, because the connection to the database is explicit in the user-provided service, it is also a common problem to use the port for one tenant while the HDI containers is sitting in another. This throws an error like "Error executing: GRANT .. invalid user name". This does not happen in this scenario as the connection is taken from the primary HDI container.
One difference is the nice wizard included in SPS03 in Web IDE for SAP HANA that automates a lot of these steps. This one is not in Web IDE Full Stack.
A cool feature in HANA as a Service are the insufficient privilege errors coming with a GUID.
Something like Insufficient privilege. Details for this error can be found with GUID .
You get that GUID and use it in this procedures:
Which will give you details on what is missing so you don't have to activate traces and scrape logs.
We continue to bake more tutorials. So stay tuned on Twitter or LinkedIn!
You have created a remote source, or maybe you have an ABAP schema replicated with SLT and you want to access those tables from your database module in Web IDE.
This post replays the process that has been documented for a while for on-premise XS Advanced. There are a few variations for HANA instances in Cloud Foundry.
Using replicated tables from a classic schema in the database module in Web IDE
The full step-by-step for cross-schema access in Cloud Foundry with the SAP HANA Service is here: https://developers.sap.com/tutorials/haas-dm-access-cross-container-schema.html
This tutorial also includes the creation of the external schema and using the fancy new wizard to load data into a table, but the steps for the remote source to work are also covered.
This is of course optional and an exercise to simulate the external schema.
The relevant parts for Smart Data Integration are:
- Create a user (we will call it UserX - the tutorial uses "PLUSR") that has access to the remote source. It also needs authorizations to grant that access to other users. That “with grant option” or “with admin option” are vital here. Better practices say you should create a role.
- Create a user-provided service with the user that can grant authorizations.
- Declare that user-provided service as a resource to your database module in the mta.yaml file
- Let the database module know that your existing HDI container is your preferred one (TARGET_CONTAINER does the trick) but that it also depends on the user-provided service to access the objects in your plain schema or the remote source. It's not entirely relevant here, but if you want to know what SERVICE-REPLACEMENTS is doing, you can check this.
- Create an .hdbgrants file.
- Build that grants file now and make sure you do not get any errors. If you continue creating artifacts based on this access and something has gone wrong, troubleshooting becomes harder.
Once the grant is working, you are now set for success. Your HDI container technical user can see and use the remote source created in the database.
You can now proceed to create virtual tables, flowgraphs and/or replication tasks: https://developers.sap.com/tutorials/haas-dm-create-flowgraph-sdi.html
The confusion is very clear... so here is a drawing
UserX has permissions to access the remote source and to bestow those authorizations to other users. To prove that, you could manually log in to the database as UserX and execute a GRANT SQL statement.
The user-provided service delivers the credentials of the UserX (you entered the user and password). This user will be granting its own permissions to the object owner in the HDI container so that this technical user can use the remote source. The permissions that will be granted to the different users are listed in the hdbgrants file.
Last but not least, if you have a physical table in the catalog and you want to reference it in your HDI container, you can create a synonym.
In a normal situation, the technical user would have more permissions than the application user, who may be just fine with read-only-ish.
The wiring happening in the background is even more clear when you try to add an invalid access (like I’m doing in the example below).
The console in Web IDE says: Error executing: GRANT "SOME PERMISSIONS" ON "SOME OBJECT" TO "XX_1#OO"
In the tutorial, UserX is called PLUSR. See how the deployment takes the hdbgrants file and literally executes a GRANT statement. “DB1_1#OO” is the object owner, one of the technical users created with the HDI Container and who will need permissions to the remote source to create a virtual table.
If you want to get other similar error messages, create a user with no permissions to anything or without grant option and use it in the user-provided service. You will get an authorization error for that grant statement.
Comparing to XS Advanced on-premise?
In the on-premise world, this has been out there and documented for a while. There are some differences in the development flow as of this date but the main pitfalls I see are related to lack of permissions in the granting user.
In the on-premise world, because the connection to the database is explicit in the user-provided service, it is also a common problem to use the port for one tenant while the HDI containers is sitting in another. This throws an error like "Error executing: GRANT .. invalid user name". This does not happen in this scenario as the connection is taken from the primary HDI container.
One difference is the nice wizard included in SPS03 in Web IDE for SAP HANA that automates a lot of these steps. This one is not in Web IDE Full Stack.
A cool feature in HANA as a Service are the insufficient privilege errors coming with a GUID.
Something like Insufficient privilege. Details for this error can be found with GUID .
You get that GUID and use it in this procedures:
call SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS ('<GUID>', ?)Which will give you details on what is missing so you don't have to activate traces and scrape logs.
We continue to bake more tutorials. So stay tuned on Twitter or LinkedIn!
- SAP Managed Tags:
9 Comments
