Skip to Content
Author's profile photo Philip MUGGLESTONE

The easy way to make your HDI container accessible to a ‘classic’ database user

I’ve blogged previously about how SAP HANA XS Advanced Model enables true isolation between applications and the resultant challenges should you need to enable intra-HDI container access.

One such scenario is where you’d like to authorize access to your XSA Advanced app (i.e. HDI Container) to a “classic” database user – perhaps a legacy HANA app or one used by an analytics tool which connects to HANA via JDBC/ODBC.

The Database Explorer component of Web IDE for HANA provides a “SQL Console” to execute scripts against the underlying schema of your HDI container. However the connection is via a “technical user” which doesn’t possess the “with grant option” rights necessary to grant or revoke authorizations.

Hence the solution I showed previously (in this video tutorial) which involved creating a role then using the user management UI of HANA Studio / Eclipse to grant that role to your “classic” database user.

However with SPS01 things got easier with the introduction of the “SQL Console (Admin)” option which connects to the HDI Container’s schema with a different “technical” user that does allow grant/revoke.

So all you have to do is open “SQL Console (Admin)” and issue the necessary grants – all directly from Database Explorer in Web IDE for HANA.

“That’s great” you say “but what’s the exact syntax required to grant authorizations? Can I use GRANT SELECT ON SCHEMA or similar?”

Well no, you need to use some HDI Container specific stored procedures. They’re documented here in the HANA Administration Guide.

There are four types:

Firstly it’s possible to grant/revoke access to the entire schema. An example might look like this (substitute your HDI Container’s schema name for “MYAPP_HDI_DB_1”:

set schema "MYAPP_HDI_DB_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', '', 'MYUSER');
insert into "#PRIVILEGES" ("PRIVILEGE_NAME", "PRINCIPAL_SCHEMA_NAME", "PRINCIPAL_NAME") values ('INSERT', '', 'MYUSER');
insert into "#PRIVILEGES" ("PRIVILEGE_NAME", "PRINCIPAL_SCHEMA_NAME", "PRINCIPAL_NAME") values ('UPDATE', '', 'MYUSER');
insert into "#PRIVILEGES" ("PRIVILEGE_NAME", "PRINCIPAL_SCHEMA_NAME", "PRINCIPAL_NAME") values ('DELETE', '', 'MYUSER');
call "MYAPP_HDI_DB_1#DI"."GRANT_CONTAINER_SCHEMA_PRIVILEGES"("#PRIVILEGES", "_SYS_DI"."T_NO_PARAMETERS", ?, ?, ?);
--call "MYAPP_HDI_DB_1#DI"."REVOKE_CONTAINER_SCHEMA_PRIVILEGES"("#PRIVILEGES", "_SYS_DI"."T_NO_PARAMETERS", ?, ?, ?);
drop table "#PRIVILEGES";

It’s pretty straightforward once you grasp the concept that you need to create a temporary table containing the schema privileges you wish to grant along with the “classic” database user name. It’s exactly the same process to revoke access – just replace GRANT with REVOKE as the stored procedure name.

You can watch a comprehensive SAP HANA Academy hands-on video tutorial showing this approach here.

A second option which allows a far more granular approach is to create an application role in your project then grant that role to the desired user. This way you can grant privileges individually to specific database objects such as tables, views, stored procedures etc.

set schema "MYAPP_HDI_DB_1#DI";
create local temporary column table "#ROLES" like "_SYS_DI"."TT_SCHEMA_ROLES";
insert into "#ROLES" ("ROLE_NAME", "PRINCIPAL_SCHEMA_NAME", "PRINCIPAL_NAME") values ('myapp.db::roles.myrole', '', 'MYUSER');
call "MYAPP_HDI_DB_1#DI"."GRANT_CONTAINER_SCHEMA_ROLES"("#ROLES", "_SYS_DI"."T_NO_PARAMETERS", ?, ?, ?);
--call "MYAPP_HDI_DB_1#DI"."REVOKE_CONTAINER_SCHEMA_ROLES"("#ROLES", "_SYS_DI"."T_NO_PARAMETERS", ?, ?, ?);
drop table "#ROLES";

Here’s a link to the respective hands-on video tutorial so you can see this in action.

Code snippets are provided in GitHub so you can follow along.

It’s also possible to grant and revoke container administrator privileges as well as grant and revoke access to the container development API.

You might be wondering why there’s a SET SCHEMA in the above examples as this is not mentioned in the documentation. That’s because the “technical” user used with “SQL Console (Admin)” doesn’t have rights to create a temporary table in its own schema – so as a workaround we can instead create the temporary table in the HDI Container schema.

As always your feedback is most welcome – below, in the YouTube comments section, or on Twitter @pmugglestone.

Have fun!

Philip

Assigned Tags

      20 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thanks for the info. Will this process get any easier in future versions? For example, I would like have simpler stored procedures that don't use table types as input parameters. A GUI would also be nice in the Web IDE. Also, can we use the procedures in the _SYS_DI schema to grant privileges across HDI container schema? I was not able to get them to work even though container name is an input parameter.

      I am hoping to setup a security model with the new HDI container roles but it is challenging to grant the <CONTAINER>#DI user the correct privileges for external objects. Its also challenging to grant external users access to container content. This was much easier when we only have to think about the _SYS_REPO user and _SYS_REPO owned everything. From a development prospective, HDI containers are a great idea. However, I honestly think this might be a step backwards in terms of security model manageability.

      Author's profile photo Philip Mugglestone
      Philip Mugglestone
      Blog Post Author

      Hi Jonathan,

      Thanks for your feedback - all good points. We'd all like to see the process of setting up security become as succinct as possible. However I'm not in a position to comment on product futures - for that the best I can do is refer you to the published roadmap : https://www.sap.com/products/roadmaps.html?search=hana&sort=title_asc#pdf-asset=38d1382d-c37c-0010-82c7-eda71af511fa&page=18

      Thanks,

      Philip

      Author's profile photo Vignesh Jeyabalan
      Vignesh Jeyabalan

      Hi Philip Mugglestone

       

      In my case i am trying to create View in HDI container using the SQL console not using a design time file . I am trying to access the View using a Java module that is also part of the same MTA.

      But if i create a CDS View the java code is able to access the View without granting any roles/privileges.

      Using your blog i have granted the  below users select access to the HDI container schema.

      • SYSTEM
      • _SYS_REPO
      • user that is part of the environment variables.
      • hdi_user that is part of the environment variables.

      Still i am unable to access the View from the Java program.

       

      Thanks

      Vignesh Jeyabalan

      Author's profile photo Mike Howles
      Mike Howles

      Is there a way to prevent developers who have access to webide/hrtt-core from running SQL Console as admin?  On one hand, the developer needs to do work on a DB module, however on the other hand, security team does not like that a developer can essentially grant access using this method.  It seems like the flaw is that I can run as the _DT user that has more privs than _RT.

      Author's profile photo Philip MUGGLESTONE
      Philip MUGGLESTONE
      Blog Post Author

      Hi Mike,

      I'm not aware of a way to disable this option for developers.

      An authorized space developer that connects to SQL Console as admin only has access to the HDI-container in question and is restricted to the authorizations of the _DT user which are very limited - basically calling the provided stored procedures such as GRANT_CONTAINER_SCHEMA_ROLES. So it's a very focused "admin" role.

      Thanks,

      Philip

      Author's profile photo Christiano Hage
      Christiano Hage

      Great blog Philip!

      As SAC requires a location table under SAC_BOC_SPATIAL in the "content" folder (as per SAP Note  2395407 referencing document Creating_Geo_model_from_LiveHana_updated.pdf), I need to consume a HDI table on a CalView created on HANA Studio.

      I followed this procedure to grant SELECT to _SYS_BIC (and my HANA user), but "WITH GRANT OPTION" is required, and thisis not possible using the grant method.

       

      Which strategy do you recommend me to expose data from an HDI table on a calc view created by another use in Studio?

       

      Regards, Chris

      Author's profile photo Peter Kiener
      Peter Kiener

      Dear Chris,

       

      we are struggling with exactly the same topic -> grant option is missing. Did you find a solution for this issue?

      Thank you for letting us know,

      Peter

      Author's profile photo Jan Tancibok
      Jan Tancibok

      Hi Chris,

      Im trying to do similar thing (use of synonyms to connect two containers) did you somehow managed to grand privileges "WITH GRANT OPTION" ?

      Author's profile photo Sreekanth Surampally
      Sreekanth Surampally

      Hi Philip, I am able to grant HDI schema privileges to Classic user in Dev system, but how can I access HDI container DB console of Quality and Production system to run these procedures?  my Web IDE is installed on Dev Server, I can only see the Dev server spaces to add in my DB explorer. Do we have to install Web IDE on Quality and Production systems also in order to do this or is there any alternative?

       

      Author's profile photo Sreekanth Surampally
      Sreekanth Surampally

      I got the answer to my question, Web IDE needs to be installed on QA/Prod to manage the HDI containers.

      Author's profile photo Daniel Liu
      Daniel Liu

      Hi Sreekanth,

      I think you don't need WEBIDE installed on QA and Prod if you still have HANA studio there. You can just go to Users in Systems tab, search for container role and assign to user.

      Thanks,

      Daniel

       

      Author's profile photo Krishna Tangudu
      Krishna Tangudu

      Hi Daniel

      Do you know how we can do this from SQL without logging into HANA Studio. We were trying to automate this

      Regards,
      Krishna Tangudu

      Author's profile photo Feng Liu
      Feng Liu

      Hi Krishna,

      You can run the grant statement in your code.

      GRANT container_role TO database user

      Thanks,

      Daniel

       

       

      Author's profile photo Krishna Tangudu
      Krishna Tangudu

      Hi Daniel,

      Granting like this is not working for container roles. Is it working for you? for us it says invalid role name

      Regards,
      Krishna Tangudu

      Author's profile photo Daniel Liu
      Daniel Liu

      I tried and it works

      grant "schema_name"."name_space::container_name" to database_role

      make sure the user has role admin to run the above statement.

       

       

      Author's profile photo Krishna Tangudu
      Krishna Tangudu

      Thanks Daniel Liu. It is working for me now

      Author's profile photo Rahul Mehta
      Rahul Mehta

      Hi Krishna

      I am trying the same but it doesn't work. Says "

      "Granting schema roles in the container "TEST_HDI_DB_1" and the parameters "[]"... failed"

       

      Regards

      Rahul

      Author's profile photo Priya Jha
      Priya Jha

      Hi Philip,

      Is there a way where we can create container specific users, apart from the application users automatically created by the HANA Service broker?

      Kindly guide.

       

      Regards,Priya

      Author's profile photo Philip MUGGLESTONE
      Philip MUGGLESTONE
      Blog Post Author

      Hi Priya,

      I'm not sure exactly what you mean by "container specific users" - you already know HDI containers come with two technical users - runtime and design time and are designed to be isolated out of the box - so it's recommended to use those technical users. If you absolutely needed to you could create a HANA user and use the HDI container design time technical user to grant the necessary roles and privileges of the container to that user however I have never done this.

      Thanks,

      Philip

      Author's profile photo Rahul Mehta
      Rahul Mehta

       

      Hi Philip
      I have followed the tutorials to setup hdi admin, hdi group admin and hdi container admin users. I then went to Business application studio and create an hdbrole. I am now trying to assign this role to a user logged in to database as hdi container admin but it says not authorized to assign role in the container schema as the object is owned by the technical user. Can you please help here ? I am using my trial account.

       

      Regards

      Rahul