HDI Container Administration on HANA Cloud

DBADMIN is the super admin who can access the majority metadata and execute operations against the objects of HANA Cloud. However, it has no permission to access the data in the HDI container and cannot grant HDI container privileges to any other users.

SAP System Admin – SAP HANA Certification Preparation Guide

Thus, in order to grant HDI Container Administrator Privileges to a User, we should create an HDI container-group administrator or an HDI-container administrator with the necessary privileges as a starting point from DBADMIN first.

Create an SAP HDI Administrator

Open the SAP HANA Cockpit:

Switch to the Security and User Management:

Under the User & Role Management section, open the User Management app:

Create a user:

Determine a specific name:

Enter your passport and uncheck on Force Password Change on Next Logon:

Assign Privileges to a User

Open the Privilege Management app

Filter out with your user name, and then click on Edit button to add Roles:

Click on Add button:

Assign Privilege as per your requirement:

You can also assign Object Privilege:

Choose the Object of an HDI container 738720F9AFD34B7CAE1338E937A9F550:

Choose a Privilege:

An Error would pop up as below, you can open the Database Explorer to check its detailed error message:

Open the SAP HANA Database Explorer:

Execute the procedure GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS, more details can be found here: Resolve Insufficient Privilege Errors

call SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS (‘7A5E44E7966E1E4DA449CC7723C090C0’, ?)

You will find the super user DBADMIN missed GRANT option, more details can be found here: GRANT Statement (Access Control)

WITH ADMIN OPTION and WITH GRANT OPTION

Specifies that the granted privileges can be granted further by the specified user or by users with the specified role.

HDI container administrator privileges are initially granted to a user by an administrator of the container group that the container belongs to. HDI container privileges cannot be granted with superuser DBADMIN, but HDI container can be administrated with the HDI Administration that DBADMIN created.

Instead, we should grant the privileges by calling API to execute specific procedures.

Grant HDI Container Administrator Privileges to a User

Grant the new HDI administrator user the required privileges by executing the following statement:

CREATE LOCAL TEMPORARY TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;
INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT '<HDI_admin_username>', 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;

For example:

CREATE LOCAL TEMPORARY TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;
INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'TIAXU', 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;

Check in the Database Explorer:

Login with the username and password of the user that you created before:

Grant HDI Container-Group Administrator Privileges to a User

Insert the following SQL statement into the SQL console:

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;
INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT '<new_container_group_admin_username>', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_CONTAINER_GROUP_ADMIN_PRIVILEGES;
CALL _SYS_DI.GRANT_CONTAINER_GROUP_API_PRIVILEGES('<container_group_name>', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);
DROP TABLE #PRIVILEGES;

Replace the name of the user in INSERT command in line 2 with the name of the user to whom the API privileges should be granted

Replace the name of the container group in the CALL command in line 3 with the name of the desired container group name.

Get the container group name

In this page Display Details of the HDI Configuration, you can get the _SYS_DI HDI View for all HDI container groups in the database: M_ALL_CONTAINER_GROUPS

Let’s see what container groups we have:

SELECT * FROM _SYS_DI.M_ALL_CONTAINER_GROUPS

Replace G with the container group name:

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;
INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'TIAXU', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_CONTAINER_GROUP_ADMIN_PRIVILEGES;
CALL _SYS_DI.GRANT_CONTAINER_GROUP_API_PRIVILEGES('BROKER_CG', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);
DROP TABLE #PRIVILEGES;

Grant Specific HDI Container Administrator Privileges to a User

Execute the SQL statement with the new user instead of DBADMIN:

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES; 
INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'NEW_CONTAINER_ADMIN', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_CONTAINER_ADMIN_PRIVILEGES; 
CALL _SYS_DI#G.GRANT_CONTAINER_API_PRIVILEGES('C', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?); 
DROP TABLE #PRIVILEGES;

Replace C with the HDI container name 738720F9AFD34B7CAE1338E937A9F550

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES; 
INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'TIAXU', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_CONTAINER_ADMIN_PRIVILEGES; 
CALL _SYS_DI#BROKER_CG.GRANT_CONTAINER_API_PRIVILEGES('738720F9AFD34B7CAE1338E937A9F550', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?); 
DROP TABLE #PRIVILEGES;

Now you’ve granted the HDI Container Administrator Privileges with procedures.