Skip to Content

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

15 Comments
You must be Logged on to comment or reply to a post.
  • 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.

  • 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

  • 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.

    • 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

  • 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

    • 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

  • 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?