HANA Cloud with HDI and stored procedures

[Robin_Walther]

Hi Experts.

I'm trying to use SAP HANA Cloud with an HDI Container and I noticed a strange behaviour that I don't understand.

The HDI Container and a table in it are created using CAP. Let's just call the table TEST_CAP.

I configured a User to access this table using this Tutorial: https://www.youtube.com/watch?v=njVZWRGTJAI

Using this tutorial I created a User that can read/update/insert data on this schema. I also gave the right to execute.

Let's call this User "DB_USER".

I also created a table using the Database Explorer and SQL using the "*_RT"-User. Let's call this table TEST_SQL.

Now I'm trying to encapsulate an upsert on table TEST_CAP in a stored procedure.

The creation works without a problem using the "*_RT"-User.

Now if i try to execute the stored Procedure using DB_USER I get the message that I have insufficient privilege to execute this procedure.

I also created a stored procedure using the "*_RT"-User that does an upsert on the table TEST_SQL.

This procedure is executed without a problem using DB_USER.

Another strange behaviour is that if I create a stored procedure to upsert table TEST_CAP using user DB_USER i can use it without a problem.

Can someone explain this behaviour to me? Why is there a difference between table TEST_CAP and TEST_SQL? And why can I use the stored procedure using table TEST_CAP created by DB_USER but not the one created by "*_RT" User?

[Cocquerel]

Which security mode are you using for your procedure: INVOKER or DEFINER ?

[Robin_Walther]

Hi MIchael.

Thanks for your question. Unfortunately I'm really new to this topic and not sure what you mean. I do not specify a security mode when creating my stored procedure.

It looks like this:

[Cocquerel · Accepted solution]

By default, the security mode for procedure is DEFINER. It requires the owner of the procedure to be able to grant required privilege to the user running the procedure. By default, the runtime user ( _RT) has INSERT/UPDATE/... privileges for itself but is missing the "GRANT to others" option. The fact that the user running the procedure has already the privilege does not help.
A simple solution for your case would be to switch to INVOKER mode : see https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7a2da744ce544db1814a5ff...

[Robin_Walther · Accepted solution]

Thank you very much! This solution worked.

I don't really understand why it worked on the table I created myself using SQL. Is it because the creation automatically gives me the "GRANT to others" option?

Best regards

Robin