Calculation Views and Table Functions fail with error;258 insufficient privilege in HANA 2.0SPS05

[former_member565459]

Hello,

I've a scenario where the queries on Calculation View and Table Functions fail with error General error;258 insufficient privilege.
The same queries run absolutely fine when we re-activate the Table functions and Calculation Views.
This is a repetitive error and requires continuous monitoring.
Is there any permanent fix to the same ?

I checked for the GUID which says USER is missing SELECT privilege.
But, as I said the query runs smoothly when I reactivate the TF or CV.

Result of SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS:

florian.pfeffer , can you help me with this ?

TIA.

Regards,

Prathamesh H.

[Cocquerel · Accepted solution]

I have faced similar issue. The root cause was an object in my data model that was regularly dropped and recreated. When it was dropped, the system was automatically removing it from role runtime referring to it but when it gets recreated, I had to re-activate any objects that refereed by the role. Indeed, when re-activated an object, it also redeploy dependent objects including the role and doing so, the missing autorisation was coming back into the role runtime.
The solution that I have found has been to switch the table function from INVOKER to DEFINER security mode so that SELECT privilege on the object that was permanently dropped/recreated was not required anymore. The _SYS_REPO user that is the owner of the table function does not have the issue because its privileges are defined at schema level and so, not affected when dropping the object in the schema.
Regards,
Michael