HANA XSA Simplified 4: SAP HANA Database Authorization provisioning for HDI Container roles

In this series of HANA XSA Simplified; this is my 4^th blog post. In this blog post; I will explain how the HDI Container roles can be provisioned to End users and developers to access the HANA views using third party reporting tool like Tableau, Power BI, Analysis for office or similar other reporting tools. These reporting tools directly connects with the HDI schema in tenant DB to access the calculation views and tables.

You can visit other blog posts in this series using below links.

HANA XSA Simplified 1: HANA XSA Implementation Methodology for different customer scenarios

HANA XSA Simplified 2: HANA XS classic and XS Advance comparison and migration from XSC to XSA

HANA XSA Simplified 3: Using GIT as a central repository in WebIDE and Deploy Process

HANA XSA Simplified 5: Deployment options for XS Advance MTA projects

In most of the SAP blogs and help documents, SAP has explained the process on how to create the authorizations and roles for XSA UI5 applications. But as we see many of our customers are still using third party tools like Tableau or Analysis for office for the reporting which directly connects with the underlying schema in tenant DB instead of accessing data from the XSA application level. In HANA XS classic; _SYS_REPO user was the owner for all the repository roles. It was comparatively easy to provide the schema and calculation view level access to end users. But with HANA XSA container concepts and enhanced security, it makes difficult to provide the end user authorizations directly. Now #OO user or object owner user is owner for all the roles created under that container. Only #OO user can provide these roles to other users; even SYSTEM or _SYS_REPO user does not have authorization to provide the HDI roles to other users in that tenant.

By default, #OO user will be locked and disabled and SAP suggest not to enable or unlock this user. In this case there are 2 ways to provide the authorizations to other users without enabling or unlocking the #OO user. Let me know in comments if you know any other method apart from these 2 methods.

1. Using the XSA WebIDE: Add the HDI project into Database explorer as an HDI Container. Right click on the project and click on ‘Open SQL Console (Admin)’. Let’s consider the schema name for the project is ‘TEST_PROJ’, role created in the container is ‘TEST_PROJ.DB::TEST_ROLE’ and we are providing this authorization to ‘_SYS_REPO’ user. Use the below code in SQL Console (Admin) to provide the authorization. ‘GRANT_CONTAINER_SCHEMA_ROLES’ procedure is used to provide the access and ‘REVOKE_CONTAINER_SCHEMA_ROLES’ procedure is used to remove the authorizations.

   set schema "TEST_PROJ#DI"; 
   create local temporary column table "#ROLES" like "_SYS_DI"."TT_SCHEMA_ROLES"; 
   insert into "#ROLES" ("ROLE_NAME", "PRINCIPAL_SCHEMA_NAME", "PRINCIPAL_NAME") values ('TEST_PROJ.DB::TEST_ROLE', '', '_SYS_REPO'); 
   call "TEST_PROJ#DI"."GRANT_CONTAINER_SCHEMA_ROLES"("#ROLES", "_SYS_DI"."T_NO_PARAMETERS", ?, ?, ?); 
   --call " TEST_PROJ#DI "."REVOKE_CONTAINER_SCHEMA_ROLES"("#ROLES", "_SYS_DI"."T_NO_PARAMETERS", ?, ?, ?); 
   drop table "#ROLES";​

   This method is good to provide authorizations to few roles for a smaller number of users. For huge number of Roles and users it will be difficult to use this method.

2. Create HDI Administrator user: In this method we create an HDI Administrator user. This Admin user can be used to provide the authorization to other users. Create the HANA DB user ‘ADMIN_USER’ in the tenant. Login with a SYSTEM user to the tenant where containers are mapped and run the below SQL code.

   CREATE LOCAL TEMPORARY TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;
   INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'ADMIN_USER', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_DI_ADMIN_PRIVILEGES;
   CALL _SYS_DI.GRANT_CONTAINER_GROUP_API_PRIVILEGES('_SYS_DI', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);
   DROP TABLE #PRIVILEGES;​

   Now the ADMIN_USER can be used as a role admin user which can add HDI roles from the container to the End users and developers like we used to do in HANA XS classic.

   To revoke the HDI Administrator user authorization from the user execute the below SQL using SYSTEM user.

   CREATE LOCAL TEMPORARY TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;
   INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'ADMIN_USER', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_DI_ADMIN_PRIVILEGES WHERE NOT (PRIVILEGE_NAME = 'SELECT' AND OBJECT_NAME LIKE 'T%');
   CALL _SYS_DI.REVOKE_CONTAINER_GROUP_API_PRIVILEGES('_SYS_DI', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);
   DROP TABLE #PRIVILEGES;

Hope these methods will help you to provide the authorizations to End users and developers which are still using the third-party reporting tools and need to access the data directly from the container schema.

If you are new to the HANA XSA and HDI Roles then you can go through the Best practices and recommendations for developing roles in SAP® HANA document which will help you to clear all your concepts.