HANA Cloud role assignment - DBADMIN cannot, then who?

[ssimsekler]

Hello

We have a CAP application which deploys HDI artefacts. We are having problems with direct DB-level access for users e.g. to delete a table, create a virtual table, etc.

We have a hdbrole artefact which creates a role; however, we have two problems:

1. As described here (towards the end), DBADMIN* cannot assign the roles unless it is granted the same roles as grantable. Because the HDI container technical users are all generated automatically; how can we assign the role in the hdbrole to DBADMIN so that DBADMIN can also assign it to other users?

2. As described here, the hdbrole cannot assign CREATE VIRTUAL ... as schema privilege. Is there another way?

3. Because CAP generates the schema with a random number as its name, how can we assign object privileges for a "schema" (I reckon a role without a schema can be created with object privileges) as we cannot fix the schema name (because it gets re-generated if we recreate the HDI container or deploy it to another subaccount)?

Kind Regards

Serdar

* The document says SYSTEM user; but, HANA Cloud reserves the SYSTEM user and I believe the statement here is also valid for the DBADMIN user.

[thomas_jung · Accepted solution]

You should never create objects directly in an HDI container via SQL. They must all be created via design time artifacts in the container instead.

But in general if you want to grant roles from the container you do not use DBADMIN. Instead you should create your own HDI Admin user for this.

ABAP Freak Show Ep. 17 - SAP HANA Deployment Infrastructure (HDI) Administration

https://youtu.be/zyHhxZWW40M

[ssimsekler · Accepted solution]

Hi thomas.jung

Thanks a lot. Actually, that's what we are trying to avoid, i.e. creating objects in an HDI container directly. But there have been occasions where we thought DB-level access is required:

1. There has been cases where an entity was deleted in CAP application; but the tables/views generated for it were not deleted at DB level; so, we had to drop the HDI container and let the CAP app deployment create a new one as we could not delete tables/viewexternally.
2. Developers need to create virtual tables for data migration using SDI which is a separate one-time activity from project implementation.
3. We have the hdirole file to create the role artefact via HDI deployment. But, we have been having problems assigning it. With your video, I believe once the HDI_ADMIN is given the privileges, that user can be used to assign the roles containing similar privileges. This happens via HANA Cockpit.
4. Can we do role assignments to proper users with DT artefacts, like hdbgrants is used for extra-schema access.

[thomas_jung · Accepted solution]

>There has been cases where an entity was deleted in CAP application - Shouldn't happen but when it does do a cf delete-service not try to clean it up at the DB level. Otherwise metadata could be left behind.

>Developers need to create virtual tables for data migration using SDI - If in the HDI container these should also be done via design time artifact

>We have the hdirole file to create the role artefact via HDI deployment. But, we have been having problems assigning it. - Yes this is what the video shows and if you follow these steps to create an HDI_ADMIN (or group admin) it can grant these roles.

>Can we do role assignments to proper users with DT artefacts, like hdbgrants is used for extra-schema access. -- No hdbgrants is only for performing grants to technical users. Follow the process of using the HDI_ADMIN users to grant additional access.

[ssimsekler · Accepted solution]

Thanks Thomas. Very helpful as always. Much appreciated!

[sucheno · Accepted solution]

Hi serdar.simsekler,

1. There has been cases where an entity was deleted in CAP application; but the tables/views generated for it were not deleted at DB level; so, we had to drop the HDI container and let the CAP app deployment create a new one as we could not delete tables/viewexternally.

I guess this is expected behavior, Did you try using undeploy.json instead of dropping the whole HDI container? That would undeploy the objects mentioned in the undeploy.json file.

You can find more details here: https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.03/en-US/ebb0a1d1d41e4ab0a06ea95171...

Also more details here: https://answers.sap.com/questions/13006363/undeploy-replication-task-in-web-ide.html?childToView=131...

Regards,

Suchen.

[neil_uebelein · Accepted solution]

I believe the YouTube video referenced in your answer (SAP HANA Deployment Infrastructure (HDI) Administration) has been moved to a new url: https://www.youtube.com/watch?v=njVZWRGTJAI&ab_channel=SAPDevelopers

Extremely helpful content btw

[former_member743230 · Accepted solution]

Hi thomas.jung,

I am getting the below error while I am trying to assign a user schema privilage(App_PRIVILAGES#) using HDI_GROUP_ADMIN, is there a different way to assign roles having grant with option.

ERROR:
"APP_PRIVILEGES#": cannot grant privilege; accepted values are "CREATE ANY", "CREATE TEMPORARY TABLE", "EXECUTE", "SELECT", "SELECT METADATA", "INSERT", "UPDATE", "DELETE", "DEBUG", "DEBUG MODIFY", "UNMASKED", "SQLSCRIPT LOGGING", "TRIGGER", "INDEX", and "REFERENCES"

Query used:

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_PRIVILEGES; 
INSERT INTO #PRIVILEGES ( PRIVILEGE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME ) VALUES ( 'APP_PRIVILEGES#', '', 'TEST_USER' );
CALL APP_CLOUD#DI.GRANT_CONTAINER_SCHEMA_PRIVILEGES( #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);
DROP TABLE #PRIVILEGES;

[thomas_jung · Accepted solution]

I don't think you are really supposed to grant roles with grant privileges outside of the HDI container. After that would pretty much negate all the controls of HDI.

[former_member743230 · Accepted solution]

Hi thomas.jung ,
Thanks for the response.
I removed the grant privileges from the role and tried to assign the same to a user using HDI_ADMIN_GROUP, but I still get the same error as above. It will be really help full if you can guide me here.
.hdbrole file is:

{
  "role": {
    "name": "APP_PRIVILEGES",
    "schema_privileges": [
      {
        "reference": "SCHEMA_REF",
        "privileges": [
          "CREATE ANY",
          "ALTER",
          "DELETE",
          "EXECUTE",
          "INSERT",
          "SELECT",
          "UPDATE",
          "SELECT METADATA",
          "SELECT CDS METADATA",
          "CREATE TEMPORARY TABLE"
        ]
      }
    ]
  }
}

<br>

.hdbroleconfig file is:

{
  "APP_PRIVILEGES": {
    "SCHEMA_REF": {
      "schema": "CLOUD_SCHEMA"
    }
  }
}
<br>

Query used:

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_PRIVILEGES; 
INSERT INTO #PRIVILEGES ( PRIVILEGE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME ) VALUES ( 'APP_PRIVILEGES', '', 'TEST_USER' );
CALL CLOUD_SCHEMA#DI.GRANT_CONTAINER_SCHEMA_PRIVILEGES( #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);
DROP TABLE #PRIVILEGES;

ERROR:

"APP_PRIVILEGES": cannot grant privilege; accepted values are "CREATE ANY", "CREATE TEMPORARY TABLE", "EXECUTE", "SELECT", "SELECT METADATA", "INSERT", "UPDATE", "DELETE", "DEBUG", "DEBUG MODIFY", "UNMASKED", "SQLSCRIPT LOGGING", "TRIGGER", "INDEX", and "REFERENCES"

[detakeo · Accepted solution]

Remove below from file and then try
SELECT CDS METADATA","CREATE TEMPORARY TABLE

[lak_sh_mi · Accepted solution]

@Thomas Jung, the link https://youtu.be/zyHhxZWW40M is not working. As my question is

1. with respect of a DBADMIN user trying to delete/drop a virtual table(a Design time), is that allowed ?
2. If so why it is required to drop it through DBADMIN?
3. If not am fine we have to use the HDI Admin user as you suggested in text reply.
4. To clarify these point, need to watch that video.https://youtu.be/zyHhxZWW40M . Please can you update that link

thanks Lakshmi

[thomas_jung · Accepted solution]

Sorry. We had to move a bunch of videos last year between YouTube channels which unfortunately changes the links. This is the new/correct link: SAP HANA Deployment Infrastructure (HDI) Administration - YouTube

No DBADMIN isn't going to be able to delete/drop anything in a HDI managed Design time. Nor should any user directly delete it via SQL. Delete the design time artifact from the project and redeploy the HDI container to get the runtime object to be deleted.