I have one HDI container (HDI1) that access an Table Function (TF_1) in another HDI container (HDI2), I create the synonym, It`s OK, working fine.
But when the TF_1 calls another Table Function (TF_2) in HDI2 I get "Insufficient Privilege" error. I have to create a synonym to TF_2.
Is It Correct? Why I don`t need to do this for another object Type (E.g. Calculation Views)
Thank you
I've reproduced this scenario without HDI, but with plain SQL schemas and users instead.
Creating and running TF_1 and TF_2 as the owner of HDI 2 is working fine.
Granting EXECUTE on TF_1 to the owner of HDI 1 works also fine. HDI 1 can successfully run TF_1 afterward, but not TF_2.
So far, so good.
With HDI the situation becomes more complex as your developer user is not the owner of HDI 1 schema.
Instead, upon activation/deployment of your code, the HDI infrastructure connects as the actual owner of HDI 1 to create the objects and then runs a series of GRANTs to other users, including your developer user to allow the execution of the function.
Now, in order to grant the execute privilege further, the GRANT OPTION* needs to be used in the grant-statement. That is a detail that is usually covered correctly by the HDI infrastructure.
In this case, however, it is clear, that the users that manage HDI 1 do not have the GRANT OPTION to further the privilege to other users.
I recommend reviewing the activation logs, what GRANTS have been declared for the cross-container access, and look out for "missing privilege" errors during activation.
