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

[manuel_pereztiribelli]

I am facing a similar role. I wonder if you have found a solution.

I think one alternative is to use wildcards in the role (I have not tested it)

[former_member565459]

Change 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.

[Jörg_Brandeis]

Hi Prathamesh,

can you please show us the full output of GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS. The interesting part was not visible in your screenshot: for which object are you missing a SELECT privilege? And which role does this object play in your scenario? This will help to understand the message.

Maybe granting the permission it is the easiest solution?

With kind regards,

Jörg

[former_member565459]

Hello jbrandeis,
Thanks for your comment.
As I've mentioned, this issue isn't specific to a particular TF or CV, its a random one and gets resolved when I re-activate the respective TF or CV, without granting any extra permissions.
As asked, I've attached the entire output in the question description.
Please, go through the same and help me with your inputs.
TIA.

Regards,

Prathamesh H.