on 2021 Oct 23 1:10 AM
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-serviceHow 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'
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
71 | |
33 | |
9 | |
8 | |
8 | |
7 | |
7 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.