3

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

Picture

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
I.B.N.
  • 992
  • 4
  • 15
  • What is the definition of your table functions in `SQL SECURITY` part? I've checked scripted calculation view and its internal procedure is defined with `sql security definer`, so authorizations are not propagated inside it. – astentx May 06 '21 at 09:23
  • Sorry for the delay, it's `sql security invoker` – I.B.N. May 11 '21 at 22:48

1 Answers1

1

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.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29