From zero to Analytics pt3: Setting up a user for the HANA Analytics Adapter
The HANA Analytics adapter exposes an endpoint to analytics tools such as SAP Analytics Cloud, Analysis for Office and other reporting clients. The adapter is an application built for XS Advanced. This means the endpoint for connections does not rely on the deprecated XS Classic engine, unlike its predecessor.
Here are the instructions to create a sample user that will be connecting from the external clients.
Configuring the user for the HANA Analytics adapter
You can download the latest HANA Analytics Adapter for on-premise here: https://tools.hana.ondemand.com/#hanatools
Note that I am using version 1.5.0 for this.
The adapter includes two Java applications. One is the approuter, which will receive the HTTP(s) requests and the other is the backend module implementing the InA queries.
A quick and dirty (yet colorful) representation of components:
This diagram is not representing the dependency on the EPMMDS plugin, which needs to be installed in the database (it’s already there in HANA express)
I will create a sample user for this. This user will be accessing the database directly and will need a JWT token.
For more information about user authentication mechanisms check the SAP HANA Security Guide
This assumes the system database is the identity provider.
Connect to the databases
This is the kind of task that is usually done by an administrator. Like many of you out there, I am the administrator and developer of my HANA express instance.
I will do this from the SQL console in Web IDE. You can find the instructions to do this using the SAP HANA Cockpit here
You will need to connect both to the system database AND the tenant.
I’ll start with the system database:
And now the tenant database:
You will need a user with administration permissions to create other users (USER ADMIN).
Open two SQL consoles, one for each database:
Check the JWT providers in both databases
SELECT JWT_PROVIDER_NAME FROM JWT_PROVIDERS;
The result under JWT_PROVIDER_NAME should be the same for both the system and the tenant database. It should NOT be blank.
If it’s blank, try command renew-db-trust from the operating system of the instance ( XSA renew-db-trust -u XSA_ADMIN ). Or check this note. Thankfully, this is not an emotional entity and it’s relatively easy to re-establish trust.
Copy the value:
TENANT DATABASE – Create a user
In the HXE tenant database (or whatever tenant is mapped to the space with your calculation views), execute the following statements (replace the sample user name and password if you want).
Replace the placeholder for name of your JWT provider with the value from the previous step
CREATE USER HAAUSR PASSWORD HanaRocks1 NO FORCE_FIRST_PASSWORD_CHANGE; grant EXECUTE ON SYS.EXECUTE_MDS_DEV to HAAUSR; ALTER USER HAAUSR ENABLE JWT; ALTER USER HAAUSR ADD IDENTITY 'HAAUSR' FOR JWT PROVIDER <name of your JWT provider>;
SYSTEM DATABASE – Create the user and grant permissions
The user also needs to exist in the system database
CREATE USER HAAUSR PASSWORD HanaRocks1 NO FORCE_FIRST_PASSWORD_CHANGE;
Grant the external user permissions to your HDI container
You can guess from the previous statements that this user will be able to access the database but will not be able to read the calculation views in your HDI container. You will need to grant this external user with access to the HDI container where your calculations view live.
Open the HDI container in the database explorer:
Right click on it and choose Open as admin
Get the name of the schema:
And run the following statements (notice the #DI added to the end of the schema):
set schema "ANALYTICS_1#DI"; CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_PRIVILEGES; INSERT INTO #PRIVILEGES ( PRIVILEGE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME ) VALUES ( 'SELECT', '', 'HAAUSR' );
Now add the following statement and execute it:
CALL ANALYTICS_1#DI.GRANT_CONTAINER_SCHEMA_PRIVILEGES( #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);
You can test the access and user works by adding a new connection to the HXE database with user HAAUSR instead of SYSTEM:
This is the most complicated piece of this entire process and you’ve made it through!
For the sake of length, the following blog post shows how to install the HANA Analytics Adapter: https://blogs.sap.com/2019/01/22/from-zero-to-analytics-pt4-installing-the-hana-analytics-adapter/
Hi Lucia Subatin ,
why do we need to create two same users (one in SYSTEMDB, one in tenant DB) ?
and what’s EXECUTE_MDS_DEV ?
The MDS procedure is called by the backend Java module, revoke permissions to it and you’ll get an error when attempting to connect. It is not needed in the SYSTEMDB apparently, so I removed that clause.
You need the user in the systemdb so that you can log in from the XSA app when testing the URL for example.
Hi Lucia Subatin
Thanks for the great blog. While executing step for checking JWT providers, the result for the query “SELECT JWT_PROVIDER_NAME FROM JWT_PROVIDERS;” came back with 2 entries for SYSTEMDB and 1 enty for tenant DB. I followed the instructions from SAP note 2654543 - Re-establish certificate trust between XS advanced and SAP HANA database but it still came back with the same results.
Any idea about how to fix it.
Hi Ricky - Having mutliple for JWT providers may not necessarily be wrong. You are looking for the one that is the same in both systemdb and HXE databases. Otherwise, you may need to recreate the provider.
Hi Lucia Subatin,
The JWT entry which is same in systemdb and HXE is called XS_APPLICATIONUSER while the other entry in systemdb is XS_JWT_XSA_0FA7450_<rest of the number> which is the one similar to what you used in the blog. Do you think I should go ahead and use the entry XS_APPLICATIONUSER as JWT provider or better to recreate the provider. If I need to re-create the JWT provider, please share the SAP link to do so.
Thank you for your help.