Step by step tutorial - cross-database queries in SAP HANA SPS09
Hi everyone, in this blog post I'm gonna show you how to query data across databases. As we know, the concept of multitenant database containers (MDC) was introduced in SAP HANA SPS09. With MDC, now we can run multiple tenant databases on one SAP HANA system and that enables strong separation of data and users as well. But do you know the cross-database queries are also supported in SAP HANA SPS09? For example, it's possible for one user in its tenant database to query data from another tenant database. But how can we achieve this? Is it enabled by default? Maybe you have a lot of questions. Don't worry. Let's first have a look at some basics of the cross-database queries and I'll show you an example step by step.
Related materials
First of all, I'd like to share with you some useful materials about this topic. You can find everything I showed in this post from the following materials. If you're interested, you can read them and make the same example with mine.
- SAP HANA SPS 09 - What's New? - Security, page 41-42
- Enable and Configure Cross-Database Access - SAP HANA Administration Guide - SAP Library
- Cross-Database Authorization in Multitenant Database Containers - SAP HANA Security Guide - SAP Library
Key concepts
Here I just borrowed page 41 from SAP HANA SPS 09 - What's New? - Security, since I think this page highlighted the key concepts well.
The key concepts can be summarized as:
- Cross-database queries are not enabled by default. Thus we need to configure it.
- The mechanism of cross-database queries is based on "remote identity" which means User_1 plays the role of User_2 when he/she queries data from Table_A in the above example.
- A user in the target database can only be associated with one user in the source database, which means User_2 cannot be associated with User_3.
- The association is unidirectional.
- Only the SELECT privileges are considered, which means User_1 is not allowed to insert data into Table_A.
Scenario
Based on the key concepts, we can create a scenario to test the cross-database queries in SAP HANA SPS09 MDC environment.
As the above image displayed,
- there are three tenant databases in our scenario, i.e., DB1, DB2 and DB3.
- Meanwhile, there are three users in different tenant databases, USER_A in DB1, USER_B in DB2 and USER_C in DB3.
- USER_A has a table named PRODUCT which holds products in China.
- USER_B has a table named PRODUCT as well which keeps products in the US.
- Now USER_C wants to create a PRODUCT view which unions all products in China and in the US as well.
Now comes the problem. How can we achieve this?
Step by step example
Notice: At the beginning of each step, I'll first show <DATABASE>(<USERNAME>) in this step.
We first enable the cross-database queries and then set DB1 and DB2 as targets for DB3.
Here you go.
8. DB2(USER_B) Create the PRODUCT table and insert some products
9. DB3(USER_C) Now let USER_C query products made in US.
10. DB3(USER_C) Create the PRODUCT view and query the data from this view.
Till now we've successfully achieved the scenario we created.
Want more?
- DB1(SYSTEM) Have a look at remote users.
You can do the same with DB2(SYSYEM).
That's it! Hope you enjoyed reading my blog and making cross-database queries successfully.
https://attendee.gotowebinar.com/register/491475477666114820
