Insufficient privilege to grant service user

[axitycesarfelce]

Hi all, Help please!

I have a container has has a schema name, I also have my grantor service that can access my remote sources and classic.

resources: - name: myDB-hdi type: com.sap.xs.hdi-container properties: service-name: '${service-name}' parameters: config: schema: MYSCHEMA - name: grantor-service type: org.cloudfoundry.existing-service parameters: service-name: grantor-service

How can I add the `GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA "MYSCHEMA" TO "MYGRANTORUSER" WITH GRANT OPTION;` if i can't do it from the console using DBADMIN or neither SharedKey in the BAS?

I'm getting always Error: (dberror) [258]: insufficient privilege: Detailed info for this error can be found with guid '887E3CF0BE599E4AAB722BBE2DA4E5CC'

[axitycesarfelce · Accepted solution]

Hi,
Thanks to denys.kempen, mickael.cocquerel, and gregorw

Your Video Series HANA Cloud Smart Data Integration, link "Grant SAP HDI Container Administrator Privileges to a User", and mickael.cocquerel with opening the admin console helped me to solve my issue.

I finally did this to the user and also the user in the HANA ERP system used form replication we had to add
TRIGGER and SELECT to the schema "SAPPRO"

set schema "MYSCHEMA#DI";
CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_PRIVILEGES;
INSERT INTO #PRIVILEGES ( PRIVILEGE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME ) 
    VALUES ( 'EXECUTE', '', 'USER_SDI_AGENT' );
CALL MYSCHEMA#DI.GRANT_CONTAINER_SCHEMA_PRIVILEGES( #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);
DROP TABLE #PRIVILEGES;

Thanks

[RAP1705 · Accepted solution]

set schema "DBADMIN";

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;

INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'UPS_ABAP_REMOTE', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.TT_DEFAULT_CONTAINER_ADMIN_PRIVILEGES;

CALL "DBADMIN".GRANT_CONTAINER_API_PRIVILEGES(#PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

DROP TABLE #PRIVILEGES;

I had the same error message and followed the help and videos mentioned above. I paste the code in SAP BTP/Database Explorer and received the following error message:

Semantic error: unresolve table or view _SYS_DI.TT_API_PRIVILEGES

Semantic error: unresolve column PRIVILEGE_NAME, OBJECT_NAME

Semantic error: unresolve procedure GRANT_CONTAINER_API_PRIVILEGES

Any idea as to how to resolve these new error messages?

RAP

[axitycesarfelce · Accepted solution]

Hi RAP,

That script was executed on HANA ERP, not in the Database Explorer for HANA Cloud.

Sorry! I saw the comment late!

Cesar

[axitycesarfelce]

Hi mickael.cocquerel Thank you for helpful answer, it helped but didn't solved it entirely.

The problem was that in the documentation is missing the statement

set schema "MYSCHEMA#DI"; <br>

Then I could run the other statements:

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

Thanks to all denys.kempen, gregorw and mickael.cocquerel!

I'm still having authorization issues running the ReplicationTask

10:11:43 AM (/home/user/projects/nsadmin/db) Start executing RT_035T1....

10:11:53 AM (/home/user/projects/nsadmin/db) An error occurred while executing statement 'call "RT_035T1.START_REPLICATION"' with error message '(dberror) [686]: start task error: "NSAGRO64"."RT_035T1.START_REPLICATION": line 27 col 6 (at pos 995): [140038] Error executing SQL command in task.;Error executing ALTER_REMOTE_SUBSCRIPTION_QUEUE with command: 'ALTER REMOTE SUBSCRIPTION "NSAGRO64"."SUB_SAPPRO_ERPRS_ZSD_T_035T1" QUEUE'. ,sql processing error: QUEUE: SUB_SAPPRO_ERPRS_ZSD_T_035T1: Failed to add subscription for remote subscription SUB_SAPPRO_ERPRS_ZSD_T_035T1[id = 410316] in remote source ERPRS[id = 162553]. Error: exception 151050: CDC add subscription failed: RS[ERPRS]: Failed to add the first subscription. Error: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [258]: insufficient privilege: Detailed info for this error can be found with guid '2135B551D4427047B888875B421A9B5A'

Then I run to check the guid, and not error is shown

kind regards,
Cesar

[Cocquerel]

is there a remote system involve ? maybe, you should run GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS there.

[axitycesarfelce]

Hi, @mickael.cocquerel, yes we needed more privileges on HANA ERP we could execute the replications task now.

Thanks

[Cocquerel]

You can use DataBase Explorer tool, add an entry with your HDI Container, right click on it and will get a menu "Open SQL Console(Admin)".
From there you will be able to run the API "GRANT_CONTAINER_SCHEMA_PRIVILEGES" ( see https://help.sap.com/viewer/3823b0f33420468ba5f1cf7f59bd6bd9/2.0.05/en-US/d75182444361461992bcd331f3...

[axitycesarfelce]

Hi denys.kempen ,

In the procedure:

"In an SQL console, connect to the database with an administrator of the HDI container “C”."
I think that the problem is that DBADMIN is not the administrator of container, how is that possible?

kind regards,
Cesar

[dvankempen]

Per above, this is by design.

[axitycesarfelce]

Hi @denys.kempen
Thanks for answering, By the way I'm a fan of your SAP Academy videos!

The result of that query:

GUID,CREATE_TIME,CONNECTION_ID,SESSION_USER_NAME,CHECKED_USER_NAME,PRIVILEGE,IS_MISSING_ANALYTIC_PRIVILEGE,IS_MISSING_GRANT_OPTION,DATABASE_NAME,SCHEMA_NAME,OBJECT_NAME,OBJECT_TYPE 887E3CF0BE599E4AAB722BBE2DA4E5CC,2021-10-22 22:44:19.135000000,239411,NS_SDI_AGENT,NS_SDI_AGENT,INSERT,FALSE,TRUE,,NSAGRO64,,SCHEMA

I also tried the following thanks to Gregor Wolf, but I have the same result
https://help.sap.com/viewer/c2cc2e43458d4abda6788049c58143dc/2021_3_QRC/en-US/8bad1a8605b343ababf821...

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRINCIPAL_SCHEMA_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'NSAdmin-hdi', 'NSAGRO64', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_CONTAINER_ADMIN_PRIVILEGES; 
SELECT * FROM #PRIVILEGES;
CALL NSAGRO64#DI.GRANT_CONTAINER_API_PRIVILEGES(#PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?); DROP TABLE #PRIVILEGES;   

I receive the same error because DBADMIN or NS_SDI_AGENT doesn't have INSERT privilege on the SCHEMA

I think I have a mistake when creating the container. My step is that I deploy the container from the MTA, because if I do it from the rocket directly from BAS icon it add's a NSAGRO64_1 the "_1". We don't need the "_1" because all the artifacts of replication task that I have uses the NSAGRO64 schema and they are 370 reptasks.

kind regards,
Cesar

[dvankempen]

Thanks Cesar,

HDI works with isolated containers with its own runtime and design time users. The SYSTEM or DBADMIN user will not have any authorisations.

This post and tutorial might clarify the architecture

• https://blogs.sap.com/2020/10/22/hdi-with-sap-hana-cloud-video-tutorial/

[dvankempen]

Hi Cesar,

You can query the system for more detailed information:

CALL SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS ('887E3CF0BE599E4AAB722BBE2DA4E5CC', ?)

As documented in the Security Guide

• GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS