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.
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.
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
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.
Still i am unable to access the View from the Java program.
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.
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.
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?
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,
Im trying to do similar thing (use of synonyms to connect two containers) did you somehow managed to grand privileges "WITH GRANT OPTION" ?
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?
I got the answer to my question, Web IDE needs to be installed on QA/Prod to manage the HDI containers.
Do you know how we can do this from SQL without logging into HANA Studio. We were trying to automate this
Granting like this is not working for container roles. Is it working for you? for us it says invalid role name
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.
Thanks Daniel Liu. It is working for me now
I am trying the same but it doesn't work. Says "
Is there a way where we can create container specific users, apart from the application users automatically created by the HANA Service broker?
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.
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.