CAP Project and SAP Hana Cloud - modifying data from existing schema

[former_member189945]

Hi everyone!

We're new to CAP and have a setup of SAP Hana Cloud in Cloud Foundry and have issues accessing/modifying data in an existing schema's table from Node.js CAP app. Actually OData read/query works and successfully fetches the data from another schema. This we achieved with using a synonym.

Read result "/srv1/varastopaikka":

{"@odata.context":"$metadata#varastopaikka","value":[{"STORLOC":"A001","PT_GEOJSON":"{\"type\": \"Point\", \"coordinates\": [500600, 670000]}","PT_WKT":"POINT (500600 670000)"},{"STORLOC":"A204","PT_GEOJSON":"{\"type\": \"Point\", \"coordinates\": [535443, 6762554]}","PT_WKT":"POINT (535443 6762554)"},{"STORLOC":"A321","PT_GEOJSON":"{\"type\": \"Point\", \"coordinates\": [532531, 6780873]}","PT_WKT":"POINT (532531 6780873)"}]}

However, any modification is throwing error "insufficient privilege":

"2021-01-19T08:42:19.55+0200 [APP/PROC/WEB/0] OUT [cds] - POST /srv1/varastopaikka
2021-01-19T08:42:19.68+0200 [APP/PROC/WEB/0] ERR [2021-01-19T06:42:19.687Z | ERROR | 1102525]: { Error: insufficient privilege: Detailed info for this error can be found with guid '...'
2021-01-19T08:42:19.68+0200 [APP/PROC/WEB/0] ERR at Socket.<anonymous>
...
2021-01-19T08:42:19.68+0200 [APP/PROC/WEB/0] ERR at Socket.emit (events.js:198:13)
2021-01-19T08:42:19.68+0200 [APP/PROC/WEB/0] ERR at addChunk (_stream_readable.js:288:12)
2021-01-19T08:42:19.68+0200 [APP/PROC/WEB/0] ERR at readableAddChunk (_stream_readable.js:269:11)
2021-01-19T08:42:19.68+0200 [APP/PROC/WEB/0] ERR at Socket.Readable.push (_stream_readable.js:224:10)
2021-01-19T08:42:19.68+0200 [APP/PROC/WEB/0] ERR at Pipe.onStreamRead [as onread] (internal/stream_base_commons.js:94:17) code: 258, sqlState: 'HY000', query: 'INSERT INTO ...( STORLOC, PT_GEOJSON ) VALUES ( ?, ? )', values: [ [ 'A004', '{"type": "Point", "coordinates": [500000, 6000000]}' ] ], numericSeverity: 4 }"

We are not using .hdbgrants file which I assume is needed. (For some peculiar? reason reading the data from table is working without. Also I cannot find the synonym anywhere when looking from SAP Hana Database Explorer).

However, when we try to add that (after creating the roles manually in DB) into the project, we get error:
"grantor and grantee are identical".

hdbgrants file:
{
"capspatial-db": {
"object_owner": {
"container_roles": [
"SPATIAL::external_access_g#"
]
},
"application_user": {
"container_roles": [
"SPATIAL::external_access"
]
}
}
}

Any suggestions what we're missing?

[gregorw]

The important part we need to help you is the hdbrole definition in the existing schema. Please check out this example for a source database ReadOnlyAccess.hdbrole. I'm consuming this project in the project access-hdi-with-synonyms.

[former_member189945]

Hi Gregor,

Thanks for your reply.

Actually I have created the target schema and roles directly with sql commands and don't have separate project for them. The commands used:

create role "SPATIAL::external_access";

grant select, insert on schema spatial to "SPATIAL::external_access";

create role "SPATIAL::external_access_g#";

grant select, insert on schema spatial to "SPATIAL::external_access_g#" with grant option;

grant "SPATIAL::external_access_g#" to "36E396C7D37942BBB0AA63F64611D4E5#OO" WITH ADMIN OPTION;

grant "SPATIAL::external_access" to "36E396C7D37942BBB0AA63F64611D4E5#OO" WITH ADMIN OPTION;

Actually got the OData CREATE working with directly assigning the cap generated user INSERT privilege into target schema and by removing the .hdbgrants file from the project (Probably I had assigned the SELECT privilege to the user before and thus it was working). But I suppose, this is not the best practise way of working?

This makes it work:

grant insert on schema spatial to "36E396C7D37942BBB0AA63F64611D4E5#OO" with grant option;

[former_member189945]

INSERT works but then UPDATE throws the following error:

"Error: data manipulation operation not legal on this view: SAP_TAB_VARASTOPAIKKA"

I can see some discussion on similar issue already in this topic:

https://answers.sap.com/questions/13129292/hana-update-and-insert-data-manipulation-operation.html

Need to investigate further.

[former_member189945]

OK, got the UPDATE call also to work. Added a trigger for the database update (I was using one for INSERT already)