We have problem executing stored procedures after granting EXECUTE to the application user. It is only after DBADM privilege is granted to the appl user the stored procedures can be executed successfully. Isn't this risky though?

Is DBADM privilege required in executing all stored procedures?
If so, is there any white paper available documenting this requirement?

Thanks in advance.

You only need to grant EXECUTE on that procedure and, if it's not a java SP, also EXECUTE on the corresponding package.

On DB2 8.1 FP4a for AIX, I have created a stored procedure (language SQL) and have only granted execute access on the procedure, without any execute authority on the package, and it works fine. The stored procedure is executed by a java application using a user authid which is not instance owner, dbadm, etc.

Right, that's how it works in v8. Before that you had to grant EXECUTE rights for the package too.

By the way, pinecone: check that the user under whose authority the procedure runs has permissions to access and/or update the objects that are referenced in the procedure.

n_i

I'm curious about your last comment, I thought it was
sufficient to have execute privileges. In other words
you are saying that its not possible to run procedures
without having permissions on the underlying objects ?

- not very good for delegation

I meant to say that the "definer" of an SP must have the rights to execute every statement in the procedure. I believe it's under definer's authority that the statements are executed at run time. So yes, you can "delegate" but to do that you'll need appropriate rights yourself.

OK now I understand

Cheers