Grant SYSTEM PRIVILEGES like EXPORT or IMPORT into...

VolkerSaggau · ‎2024 Jun 07

In order to get the system privileges someone has to give them to your HDI container. This are the steps you have to follow to get them into the container. In this a sample the IMPORT/EXPORT privileges could be given.

[optional] A user with DBA-ADMIN role creates a usergroup [HELP] such as "HDI_CONTAINER_RIGHTS"
Either use the HANA Cloud Central to create a USERGROUPor use a SQL command:

-- DROP USERGROUP "HDI_CONTAINER_RIGHTS";


CREATE USERGROUP "HDI_CONTAINER_RIGHTS";

Create a USER "HDI_IMPORT_EXPORT_GRANTOR":

or use a SQL command:

--DROP USER HDI_IMPORT_EXPORT_GRANTER;


CREATE USER HDI_IMPORT_EXPORT_GRANTER



PASSWORD Abcd1234 NO FORCE_FIRST_PASSWORD_CHANGE 



SET USERGROUP "HDI_CONTAINER_RIGHTS";

Define a ROLE "HANAIMPORTEXPORT4HDI" that has the privileges needed for the container:

or use and SQL command:
```
CREATE ROLE HANAIMPORTEXPORT4HDI;

GRANT IMPORT TO HANAIMPORTEXPORT4HDI;

GRANT EXPORT TO HANAIMPORTEXPORT4HDI;
```
Now this role has to be given to the HDI_IMPORT_EXPORT_GRANTOR with GRANT to others.
He will give the HDI container the role so that the container can import and export
or with the SQL commend:
```
GRANT HANAIMPORTEXPORT4HDI TO HDI_IMPORT_EXPORT_GRANTER WITH ADMIN OPTION;
```
Now we have done the HANA Cloud side of things.
There is a user "HDI_IMPORT_EXPORT_GRANTER" that can grant a role "HANAIMPORTEXPORT4HDI" with IMPORT and EXPORT privileges.
Now we have to get this role into our HDI container. We have to switch to the BTP Cockpit:
Inside your development space -here dev - a CUPS (Custom User Provided Service) needs to be defined.
```
{
   "user": "HDI_IMPORT_EXPORT_GRANTER",
   "password": "Abcd1234",
   "tags": [
      "hana"
   ]
}
```
Now there is a CUPS service in your space that could grant your HDI Containers the role to import and export. Of course you need all this in a similar style in other landscapes you are using. Explain this to your SAP HANA Cloud ADMIN. Certainly there are only very few people in production allowed to see the passwords. The rights of the role had been selected by intention very limited.

Now we go into BusinessApplicationStudio to get the wanted role into the HDI Container.

Invoke the CUPS service into your project:

That will appear as a "cross-container-service-n". (In my case I had another one as well)
This container get a name: here "cross-container-service-3" which you will also find back in the mta.yaml file:

Now that we have a service that could GRANT us the role we are looking for we need a hdbgrants file:

or here the text version:

{
   "ServiceName_3":
      {
          "object_owner":{
               "global_roles": [
                   {
                     "roles": [
                        "HANAIMPORTEXPORT4HDI"
                     ]
                   }
                ]
          },
          "application_user": {
               "global_roles": [
                   {
                     "roles": [
                        "HANAIMPORTEXPORT4HDI"
                ]
            }
         ]
      }
   }
}

(Formatting is maybe wrong but content should be ok

Next step is deployment
The result shows that we successful granted the hdbgrants files:

Now the import /export can be tested: Open the DBX application as RT user:
and the prices are now imported and exported through my S3 bucket ( as BTP object store service)
There is a PSE/Certificate setting that needed to be defined as well. Please check with below BLOG to establish this HANA Cloud setting.
BLOG on copy HDI containers explains how to get a certificate and create a PSE