Skip to Content

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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

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

    (0) 

Leave a Reply