How to enable and use cross-tenant database queries in HANA DB

[rajarajeswari_kaliyaperum]

CROSS-TENANT DATABASE QUERIES:

Below is the simple question and answer format to get an easy understanding as of how to make use of cross-tenant database access.

https://help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/2.0.05/en-US/5c190a0e07d64f9dac98af9938...

1.What is use of SAP HANA cross-database access? What is the minimum version on which this was enabled ?

Since HANA 1.0 SPS 09(revision 90) ,read-only or select queries between tenant databases are now possible. ie , cross-application reporting, cross-database SELECT queries are possible between tenant nodes of same System DB is now possible . This means that database objects such as tables and views can be local to one database but be read by users from other databases in the same system. However they are not enabled by default. This feature must be first enabled for the system in the system database and then have to be configured which databases may communicate with one another.

 

2.Is Cross-database access between the system database and a tenant database possible?

No. Cross-database access between the system database and a tenant database is  not possible. Just that the system database can show monitoring data from tenant databases (views in the schema SYS_DATABASES) but can never show actual content from tenant databases.

 

3.What are the objects that can be accesses by HANA cross-database access and how can the queries be designed ?

The following object types on remote databases can be accessed using cross-database access:

a.Schemas

b.Rowstore and columnstore tables (not including virtual tables)

c.SQL views (not including monitoring views)

d.Graphical calculation views

-If they only use supported object types as data sources

-If they don’t use procedure-based analytic privileges

e.Synonyms

The following object types on the local tenant database can access database objects on the remote tenant database:

a.SQL views

b.Scripted and graphical calculation views

c.Procedures

d.Synonyms

Sample SQL with cross db access:

  SELECT * FROM schema1.table1 AS tab1, db2.schema2.table2 as tab2 WHERE tab2.column2 = 'dfffgdfg'

 

4.How to enable cross tenant database option in SystemDB?

To allow queries between databases, we must first enable cross-database access and then specify which databases may communicate with one other. We can do this by configuring the global.ini configuration file .

Procedure:

1.Enable parameters in SystemDB

a.In global.ini set the parameter cross_database_access = true in the configuration file global.ini either via cockpit or studio

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

b.Enable communication from one tenant database to one or more other tenant databases by executing the following statement in the system database:

  ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') set ('cross_database_access', 'targets_for_<source_db_name>')='<target_db1>[,<target_db2>...]' WITH RECONFIGURE;

Example: We have two databases DB1 and DB2 and we want to be able to access DB1 from DB2. So we have to add the parameter with targets_for_DB2 the value DB1.

Note:Cross-database access is configured only in one direction. If in the above example you also want DB2 to be able to access DB1, you would have to add the parameter targets_for_DB1 with the value DB2.

 

5.What does the term Remote identity mean ?How to create it?What are the restrictions involved?

In order to access the objects that are present in another tenant db, source tenant db user must be mapped to a user (remote identity) in the target tenant database. Ie,

A user in one database can run a query that references objects in another database if the user is associated with a sufficiently privileged user in the remote database. This associated user is called a remote identity. This is the user who executes the query (or part of the query) in the remote database and therefore the user whose authorization is checked.

Assume that we have a system with 2 tenant databases: DB1 and DB2.USER2 in DB2 wants to query the table SCHEMA1.TABLE1 in DB1, for example,

SELECT * FROM DB1.SCHEMA1.TABLE1.

To make the above query work from DB2, a remote identity of user2 must be created in DB1.

STEPS:

                      a.The administrator of DB1 creates a user in DB1 with a remote identity in DB2:

CREATE USER USER1 WITH REMOTE IDENTITY USER2 AT DATABASE DB2

 b.The administrator of DB1 grants user USER1 the privileges required to read the table SCHEMA1.TABLE1:

                                 GRANT SELECT ON SCHEMA1.TABLE1 TO USER1 [WITH GRANT OPTION]

Now, USER2 in DB2 can select from SCHEMA1.TABLE1 in DB1.

Restrictions:

-A user can be the remote identity for only one user in another database.

-An existing user can be assigned a remote identity using the ALTER USER statement

 -The association between a user and a remote identity is unidirectional. In the above example, USER2 can access SCHEMA1.TABLE1 in DB1 as USER1, but USER1 cannot access objects in DB2 as USER2.

-Only the SELECT privileges of the user in the remote database are considered during a cross-database query. Any other privileges the remote user may have are ignored.

-Before users with remote identities can be created, an administrator must enable cross-database access for the system in the system database and specify which databases can communicate with one another.

 

6.What are the system views for monitoring cross-database access ?

The following system views contain information about cross-database authorization in a tenant database:

-USERS (SYS) : The column HAS_REMOTE_USERS indicates whether or not a particular user in the local database has remote identities in other databases.

-REMOTE_USERS (SYS):This system view shows which local users can be used by users on other databases for cross-database query execution.

Note: The system views EFFECTIVE_PRIVILEGES and ACCESSIBLE_VIEWS do not include privileges that a user has through a remote identity.

 

7.How to apply resource limitations on remote database accesses ?

For cross-database queries workload classes in the remote tenant database is the only way of applying resource limitations; in this case only the following set of workload class mapping properties is available:

-APPLICATION USER NAME

-CLIENT

-APPLICATION COMPONENT NAME

-APPLICATION COMPONENT TYPE

-APPLICATION NAME

-USER NAME

For cross-database queries it is not possible to control resource allocation by setting user parameters; normally, we set values for the parameters STATEMENT MEMORY LIMIT, STATEMENT THREAD LIMIT and PRIORITY on user level, but this is not supported for cross-database queries.

8.What are the list of know errors / limitations when using remote database access for query execution?

Refer the below link for known errors and issues with cross database access.

https://help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/2.0.05/en-US/5ed7e5f179684682bd476a5e7f...