Introduction

Many and probably most of you are familiar with the multi-tenant database concept of SAP HANA. As the name indicates, it allows for separation of tenant data in logically isolated databases, which again run on a single bare metal system and share the same low level (operating system and infrastructure specific) resources (memory, CPU power, storage etc.). This concept makes HANA a cloud-ready database; it especially enables elastic deployment and operation of tenant databases. For example a customer (tenant) can request a HANA database instance for his/her cloud account and will get one “tenant” database provisioned in the account. These tenants are agnostic of other tenants sitting next to each other even on the same host. I know of systems, who achieve this isolation in the DB’s catalog – by setting properties or even flags in the database tables. In HANA’s case the isolation is on much lower level; HANA tenants have separate catalogs, repositories, user vaults etc. But sometimes you may desire explicit access to a tenant database, different than the one you explicitly own! We recently had this requirement in a project. A high volume of data was produced in a development system, but after a while we needed to move the data to a QA instance. One can solve this challenge with traditional means: “export the data from DEV and import them in QA”. But that would have meant that we were to store GBs of data in an intermediate step. We wanted to avoid this as we had some constraints on the storage infrastructure. So we decided to configure a cross-tenant database access and simply “SQL select” the data from the original tables and “SQL insert” them into tables in the new tenant. And this effort turned to be relatively low!

In the following I will describe this process in an end-to-end manner even giving you examples with real tables, schemas and users. You will be able to reproduce this process on an on-premise HANA instance – e.g. HANA Express. This will not work in the Cloud, since a cloud account doesn’t have access to the “system database” of a cloud instance (a cloud account always gets a tenant instance). I used SAP HANA 2.0 SPS02 as I was performing the configurations and was executing the (SQL) code.

Note: Everything I introduce here can be found in the official documentation about HANA. The information is scattered but available. The only thing I do differently here, is that at one step the official documentation requires the usage of the HANA Cockpit. I ignored that “recommendation” and performed this step simply in my HANA Studio.

Note: There are other blogs on this topics already available in the community. Refer for example to this one. As I started writing these lines, it was meant to be a document for my project team colleagues. Later we realized that there are information available in the community (e.g. the blog I just referred to), But I was encouraged by my team to publish the information in a blog format – regardless of the fact that information on the topic already existed. I hope, you’ll find it useful.

Note: From HANA 2.0 SP01 onwards, HANA is only installed as a so-called multi-tenant database. This means, there is a distinction between

• the system database and
• a tenant database.

The system database (there is only one per SID) is responsible for keeping the system settings and configurations of the whole database instance. This is not a full-blown SQL database and shall not (and cannot) be used for storing application data.

Application data are stored in tenant databases. One tenant database is installed by default. The name of this “default” tenant database equals the SID, e.g. HA1. Arbitrary many tenant databases can be created in addition and on behalf of the project.

Setting up the tenant database

The SID of my HANA instance is HA1 – per installation. I automatically got a tenant instance with the same name as the SID. I then created the second tenant database by executing the following command (as user SYSTEM in the system database) in the HANA Studio.

CREATE DATABASE HA2 SYSTEM USER PASSWORD <passwd>

This command also sets the password of the user SYSTEM in the tenant database HA2. I finally ended up with a system database (HA1) and two tenant databases (HA1 and HA2).

Setting up development users and schemas

In all databases (system and tenant) there is the user SYSTEM. But then I also created “developer users” in the tenant databases (GORANHA1 and GORANHA2 – respectively). I also created schemas owned by those users in the respective tenant databases (performed with the user SYSTEM):

CREATE SCHEMA DENMARK owned by GORANHA1
CREATE SCHEMA NORWAY owned by GORANHA2

Then I needed some tables (or at least one – just to be able to go full circle for this blog). I created the table POPULATION in both schemas. Here is its definition in HA1:

Note: In real projects those kind of users and schemas will most likely already exist. You may need to adjust the privileges to be able to perform selects and inserts in the respective schemas and tables. This also applies to users and schemas generated by HDI containers from the Web IDE! Here I lay out the concept for runtime objects, but they hold for design time artifacts just as well.

Here is the result (just as an overview):

System database HA1

Tenant database HA1

• Schema = DENMARK
• Owner = GORANHA1
• Table = POPULATION

Tenant database HA2

• Schema = NORWAY
• Owner = GORANHA2
• Table = POPULATION

This is how the connections in my HANA Studio looked like:

Enabling the cross-database access

The enablement of the access is relatively easy. First enable the cross access on system database level for the corresponding databases. The access is unidirectional. Just issue the commands on the system database with user SYSTEM in the HANA Studio:

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') set ('cross_database_access','enabled')='true' WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') set ('cross_database_access', 'targets_for_HA2')='HA1' WITH RECONFIGURE;

These commands tell the system database that the tenant HA2 wants to access the tenant HA1. This will result with the following entries in the global.ini of the system database:

For the access I used the users GORANHA2 in HA2 and GORANHA1 in HA1. The important thing is that the user in the target database (GORANHA1 in HA1) becomes “remote identity”. The official documentation speaks about creating this user. In my case the user already existed and I just had to alter his “role”. I performed the following (as user SYSTEM in HA1):

ALTER USER GORANHA1 ADD REMOTE IDENTITY GORANHA2 AT DATABASE HA2

The way the user GORANHA1 was created, doesn’t include the SELECT privilege for the table POPULATION in the schema DENMARK. I had to give this privilege to the user (as user SYSTEM):

GRANT SELECT ON "DENMARK"."POPULATION" TO GORANHA1

But this also didn’t work in my case, since the user SYSTEM could not grant the select on the schema DENMARK. In order for the previous statement to work, I gave the user SYSTEM the privilege to SELECT from the schema DENMARK with “grant option” (as user GORANHA1):

GRANT SELECT ON SCHEMA "DENMARK" TO SYSTEM WITH GRANT OPTION

And that’s it. Now the cross-database access is configured and one can test the setup – of course provided that you have some data in the table “DENMARK”.”POPULATION”.:

INSERT INTO "NORWAY"."POPULATION" SELECT * FROM "HA1"."DENMARK"."POPULATION"

That’s it! I hope you’ll find this post useful.