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:
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 USER2 WITH REMOTE IDENTITY USER1 AT DATABASE DB1.
or
Add remote identity to an already existing user in DB2
ALTER 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:
The first thing to know about cross-database access is that it only allows SELECT access to data from the source database.
Pre-Requisites:
- You are logged in as SYSTEMDB
- You have the system privilege INIFILE ADMIN
- Enable cross-database access by changing the parameter enable = true in [cross_database_access] under global.ini in SYSTEMDB.
- This will enable the feature but does not configure it
- 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.
- 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.
- This will complete the configuration
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 USER2 WITH REMOTE IDENTITY USER1 AT DATABASE DB1.
or
Add remote identity to an already existing user in DB2
ALTER 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.
No comments:
Post a Comment