on 10-06-2020 1:03 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
>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.
Hi serdar.simsekler,
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.
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
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;
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.
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"
@Thomas Jung, the link https://youtu.be/zyHhxZWW40M is not working. As my question is
thanks Lakshmi
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.
User | Count |
---|---|
81 | |
9 | |
9 | |
7 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.