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
@PhilipMugglestone.
Have fun!
Philip
