I'm running db2 9.5 on AIX and have an authorisation issue on a SP:
Basically, the procedure creates a table (31 of them actually), then another process attempts to insert to the table(s). This causes a problem with authorisation as the inserter does not have insert authority on the table created from the procedure.
I don’t want to have the administrative overhead of granting to all the tables created in the procedure – is there another way of achieving this?
We don't really want to extend the authority on the inserter, so that rules that option out. Obviously we could issue grant statements for each of the tables created in the SP - which is an administrative overhead.
It's not clear to me how the authority of the SP is carried forward. In db2 Z - you can specify whether or not authority for the executing SP is based on the caller or the compiler of the SP. It seems in LUW that the compiler/binder of the SP is the authorisation that is used for the running of the SP.
Is there a way to make the callers authorisiation pass to the SP - this would also solve our problem.