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.

1. SYSTEMDB(SYSTEM) Create three tenant databases

CREATE DATABASE DB1 SYSTEM USER PASSWORD Manager1;
CREATE DATABASE DB2 SYSTEM USER PASSWORD Manager2;
CREATE DATABASE DB3 SYSTEM USER PASSWORD Manager3;

2. SYSTEMDB(SYSTEM) Make the configuration in red box at the system layer, Administration -> Configuration -> global.ini -> cross_database_access

We first enable the cross-database queries and then set DB1 and DB2 as targets for DB3.

3. DB3(SYSTEM) Create USER_C in the tenant database DB3

CREATE USER USER_C PASSWORD aA111111;

4. DB1(SYSTEM) Create USER_A in the tenant database DB1 and associate USER_A with USER_C in the tenant database DB3

CREATE USER USER_A PASSWORD aA111111;
ALTER USER USER_A ADD REMOTE IDENTITY USER_C AT DATABASE DB3;

You can also use CREATE USER … WITH REMOTE IDENTITY … statement directly like in step 5.

5. DB2(SYSTEM) Create USER_B in the tenant database DB2 and associate USER_B with USER_C in the tenant database DB3

CREATE USER USER_B PASSWORD aA111111 WITH REMOTE IDENTITY USER_C AT DATABASE DB3;

You can also use CREATE USER plus ALTER USER statements like in step 4.

6. DB1(USER_A) Create the PRODUCT table and insert some products.

CREATE COLUMN TABLE USER_A.PRODUCT (
  ID INTEGER,
  NAME NVARCHAR(100),
  LOC NVARCHAR(100),
  DESC NVARCHAR(100)
);
INSERT INTO USER_A.PRODUCT VALUES (1001, 'Product A', 'CN', 'Made in China');
INSERT INTO USER_A.PRODUCT VALUES (1002, 'Product B', 'CN', 'Made in China');

7. DB3(USER_C) Can’t wait anymore? OK. Let’s first let USER_C query products made in China

SELECT * FROM DB1.USER_A.PRODUCT;

Here you go.

8. DB2(USER_B) Create the PRODUCT table and insert some products

CREATE COLUMN TABLE USER_B.PRODUCT (
  ID INTEGER,
  NAME NVARCHAR(100),
  LOC NVARCHAR(100),
  DESC NVARCHAR(100)
);
INSERT INTO USER_B.PRODUCT VALUES (2001, 'Product C', 'US', 'Made in US');
INSERT INTO USER_B.PRODUCT VALUES (2002, 'Product D', 'US', 'Made in US');

9. DB3(USER_C) Now let USER_C query products made in US.

SELECT * FROM DB2.USER_B.PRODUCT;

10. DB3(USER_C) Create the PRODUCT view and query the data from this view.

CREATE VIEW USER_C.PRODUCT AS
SELECT * FROM DB1.USER_A.PRODUCT
UNION ALL
SELECT * FROM DB2.USER_B.PRODUCT;
SELECT * FROM USER_C.PRODUCT;

Till now we’ve successfully achieved the scenario we created. 😎

Want more?

– DB3(USER_C) Failed to insert data into table DB1.USER_A.PRODUCT since only SELECT is allowed.

INSERT INTO DB1.USER_A.PRODUCT VALUES (1003, 'Product E', 'CN', 'Made in China');

– DB1(USER_A) Failed to let USER_A query data from the view DB3.USER_C.PRODUCT since the unidirectional association.

SELECT * FROM DB3.USER_C.PRODUCT;

– DB2(SYSTEM) Failed to associate USER_B with USER_A at the tenant database DB1 since only one user is allowed.

ALTER USER USER_B ADD REMOTE IDENTITY USER_A AT DATABASE DB1;

– DB1(SYSTEM) Have a look at remote users.

SELECT * FROM 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. 🙂