How to access an SAP HANA XSA Container schema from external applications such as NodeJS

My Licensed To Code colleague Roland Schulz and I have been working on SAP HANA XSA development for the past few years. We have been doing lots of NodeJS development on both SAP HANA XSA and also outside of HANA. We have a scenario which we are developing of a NodeJS application and needed to write data from the external NodeJS application into an SAP HANA Container schema but we could not connect to the schema from the outside.

So how do we do this?

So here’s the problem.  I am logged into SAP HANA Express on the Google Cloud XSA as the SYSTEM user and I am not able to see the contents in the container schema SAP_AD_HDB_1.  Yes – you may have guessed it from the tables…. we are doing SAP and Active Directory integration using NodeJS.  Actually, we are integrating with Azure Active Directory and using NodeJS is ideal since Microsoft provides the Graph API and NodeJS libraries for that functionality.

Here are the steps we implemented that allows us to access the schema data from the outside.  I had help from Thomas Jung at TechEd 2018 in Las Vegas to show how I can achieve this and I told him that I would write a blog to do so…. but I only got around to it after a few months after being super busy with other SAP UI5 and HANA XSA projects.

Here we go…

Step 1 – Create another user which we will use to grant the SYSTEM user the necessary privileges to see the data in the container schemas.

CREATE USER JAY_HDI_ADMIN PASSWORD ***** NO FORCE_FIRST_PASSWORD_CHANGE;

Step 2 – create the privileges temporary table to load in the privileges we want to assign the user above

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_PRIVILEGES;

Step 3 – insert the admin user from above with the privileges into this temporary table with the values from the _SYS_DI.T_DEFAULT_CONTAINER_USER_PRIVILEGES container table

INSERT INTO #PRIVILEGES ( PRIVILEGE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME ) SELECT PRIVILEGE_NAME, OBJECT_NAME, 'JAY_HDI_ADMIN' FROM _SYS_DI.T_DEFAULT_CONTAINER_USER_PRIVILEGES;

Step 4 – Run the procedure to grant the schema privileges for the SAP_AD_HDB_1 schema to the user

CALL _SYS_DI.GRANT_CONTAINER_SCHEMA_PRIVILEGES( 'SAP_AD_HDB_1', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

Step 5 – Drop the temporary table since we do not need it anymore:

DROP TABLE #PRIVILEGES; 

Step 6 – Create a role for the HDB container that we can then grant: sap_ad_hdb_admin:

Code:

{

    "role":{

        "name": "sap_ad_hdb_admin”,

        "schema_privileges": [{

           "privileges": ["SELECT METADATA", 

                          "SELECT CDS METADATA", 

                          "SELECT", 

                          "INSERT", 

                          "EXECUTE", 

                          "DELETE", 

                          "UPDATE", 

                          "CREATE TEMPORARY TABLE"

           ]

        }]

    }

}

Activate this:

Step 7 –  With the SYSTEM user, grant the ROLE ADMIN system privilege to the user created above:

Step 8 – log in as the new HDI container user created above in Eclipse:

Step 9 – assign the roles to the SYSTEM user (or any other user you want to access the HDI container generated schema)

Before:

Then add the role we create – sap_ad_hdb_admin.  I did notice that there are two other roles automatically created – SAP_AD_HDB_1::access_role and SAP_AD_HDB_1::external_privileges_role – so it looks like SAP may have added these roles in the newest support pack which may do the same thing as the role we created – i will need to find out more about that:

Activate this:

Step 10 – moment of truth….. now see if you can get view the contents of the HDI container schema through the SYSTEM user (or another user that you may want to use)

And here it is….