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?

Cheers…

Then you can choose to have a security hole by granting the DBADM authority to the inserter. There's also a DATAACCESS authority but I don't think it'd been fully implemented before DB2 9.7.

Thanks for the reply...

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.

Cheers

Randy_Roberts, I guess DYNAMICRULES option is the one you are after .

BIND - IBM DB2 9.7 for Linux, UNIX, and Windows

How to override the default bind options :

IBM DB2 9.7 for Linux, UNIX and Windows Information Center