Skip to Content
Author's profile photo Bhavit Sharma

HANA MDC cross-database access

In a multi tenant database setup for HANA, the tenants are isolated from each other and can not see the content in other tenants by default. But there can be need to access some data from the other tenant. This can be accomplished using a cross-database access functionality that is provided by HANA.

The first thing to know about cross-database access is that it only allows SELECT access to data from the source database.

Pre-Requisites:

  1. You are logged in as SYSTEMDB
  2. You have the system privilege INIFILE ADMIN

Configuration (from the administration editor in HANA Studio):

  1. Enable cross-database access by changing the parameter enable = true in [cross_database_access] under global.ini in SYSTEMDB.
  2. This will enable the feature but does not configure it
  3. For configuration add the following parameters: targets_for_<source_db_name> under global.ini-> [cross_database_access] and adding the <target_db_name> as value.
    Example: You have two databases DB1 and DB2 and you want to be able to access DB1 from DB2. So you add the parameter targets_for_DB2 with the value DB1.
  4. This will configure one way access to DB1 from DB2. But similar to previous configuration, access to DB2 from DB1 can also be configured at the same time by adding the parameter.
  5. This will complete the configuration

 

Concept of remote identities:

As we know, in single database, every user will need proper authorization/privileges on an object to access that object. This is the same in MDC cross-database access. But since the users are isolated, we need to create something like a “proxy” called remote identities.

Terminology I will be using from here on in my example(s):

Source Database: Where the object/data exists and hence the database you need access to.

Target database: Where you are running the query and want the results. Hence the database you need access from.

DB1: Target tenant database

DB2: Source tenant database

USER1: User in source database that needs access to object(s) in target database

USER2: Remote Identity of USER1 in source database

Example:

USER1 needs to run a Select query on table T1 that exists in DB2. You need to add a remote identity in source database for USER1

  1. Create USER2 (remote identity for USER1) in DB2:
    CREATE USER USER2 WITH REMOTE IDENTITY USER1 AT DATABASE DB1.
    or
    Add remote identity to an already existing user in DB2
    ALTER USER USER2 ADD REMOTE IDENTITY USER1 AT DATABASE DB1

Here USER1 will ask USER2 to run the query on database DB2 and provide the results. But for USER2 to itself get result from the table T1, it needs access (SELECT) to table T1.

Therefore we provide USER2 the necessary acess:

GRANT SELECT ON “<SCHEMA>”.”T1″ TO USER2;

But what if you want to create a calculation view which needs access to table T1 from DB2?

We still need the USER2 to get data from table T1. But since the “activation guy” for the calculation view is the _SYS_REPO of DB1 (every tenant has its own _SYS_REPO user and are isolated from each other).

The database does not allow the administrator to add a remote identity to SYS_REPO in any tenant. Instead, the administrator should create a dedicated user (for example, REPO_DB1_DB2), which you can use exclusively for privileges in the remote database. This user only needs the privileges for tables that are used as remote data sources and does need privileges for all the tables in the remote DB that are used in calculation views.

The remote identity for _SYS_REPO (REPO_DB1_DB2) will need select access with grantable to others option:

GRANT SELECT ON “<SCHEMA>”.”T1″ TO REPO_DB1_DB2 WITH GRANT OPTION

You are now able to run select queries as well create a view in one tenant that needs access to table in a different tenant.

Some things to note:

  • Cross Database is currently limited to read-only access
  • Cross-database queries do currently not support multiple tenant database levels as part of a view hierarchy.
  • Hierarchy views with remote objects or calculation views with hierarchies on top of remote objects are not supported.

Useful Links:

Enable and Configure Cross-Database Access

Cross-Database Authorization in Multi-Tenant Database Containers

SAP HANA Modelling Guide

Troubleshooting Error Situations Related to Cross-Database Access

Note 2196359 – Limitations for cross-database access in an SAP HANA MDC environment

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Nick Bligh
      Nick Bligh

      Hi Bhavit - good guide, thanks!  I have completed the setup and can perform a 'select' statement from one database to another, yet when creating a calculation view I do not get an option to select from a remote source?  E.g. I can only select a table from the database I am logged into.  Is there anything other than the above I need to do for this ?  Thanks.  Nick.

      Author's profile photo Bhavit Sharma
      Bhavit Sharma
      Blog Post Author

      Hello Nick,

       

      When you say "remote source", do you mean remote tenant (i.e tenant from the same HANA system) or do you mean remote source as in a different database all together (Eg. SDA etc)?

      Author's profile photo Nick Bligh
      Nick Bligh

       

      Hi Bhavit - correct tenant from the same HANA system.

      Thanks.

      Nick.

      Author's profile photo Bhavit Sharma
      Bhavit Sharma
      Blog Post Author

      Dear Nick,

       

      In HANA Studio, you have to be logged in both the tenants. Once you are logged in. Create the calculation view in the target tenant and when, lets say, you use project. You can right click on the projection node and it will give you the option to select from the tenants you are logged in HANA Studio and then search for the desired table. I hope that answers your question.

       

      Regards,

      B

      Author's profile photo Nick Bligh
      Nick Bligh

       

      Hi Bhavit.  Thanks for your response.  What you have described above does not work for me.  I have raised a message with SAP.  A SELECT statement works fine, but I can not see all the tenants like you state with the projection node.

       

      Thanks.

       

      Nick.

      Author's profile photo Former Member
      Former Member

      Can the cross database access be done from "systemdb" to a "tenantdb"?

      Author's profile photo Bhavit Sharma
      Bhavit Sharma
      Blog Post Author

      Dear Konstantin,

       

      It should work the same way. Setup the parameter targets_for_SYSTEMDB in the global.ini -> cross_database_access. Create or Alter necessary users (remote identity). Although what is your purpose for having cross-db access from SYSTEMDB? As you know SYSTEMDB is used for central system administration.

      Author's profile photo Former Member
      Former Member

      Dear Bhavit,

      I try to evaluate the possibilities to monitor a tenant system using only one connection to the HANA system. It seems to me that not all information about a tenant can be gathered from SYS_DATABASES in systemdb.

      But I am a beginner in the basis administration and maybe I am wrong and if you have a hint for me how the monitoring can be done in a proper way in an MDC system I would be thankful.

      many regards

      Konstantin

      Author's profile photo Vladimir Zapata Gomez
      Vladimir Zapata Gomez

      Hi All,

       

      Anyone knows how to do this for a hana cloud enviroment?

      is this feature enabled for hana cloud?

       

      Thanks in advanced,

       

      Vladimir Zapata

      Author's profile photo ANKIT JAIN
      ANKIT JAIN

      hi Bhavit Sharma : My purpose is to query all the tenant DB's with the help of some query from system DB and want to collect information of a particular table which is common across all tenant DB's.

      Is it possible?