how to alter sequence in db2 stored procedure by another user

Question

I am using DB2 LUW Version 10.5.0.5 on Linux (x86_64) I have written a procedure to alter a sequence to restart with a certain number. The procedure takes the sequence name and the number to restart with as parameters. The procedure got created successfully . I granted a developer connect access on the database and execute access on the procedure. But he is getting SQL -551 (permission issue for alter sequence sql) when he executes the procedure. Create table works perfectly fine when it is executed in a procedure by anyone who even doesnt have create table access. WHy alter doesn't work then? Is there a way to allow developers to alter a sequence in a database without granting alter access on the sequence itself?

Answer 1

Effect of DYNAMICRULES bind option on dynamic SQL

You must set the DYNAMICRULES bind option to BIND before the routine creation to make dynamic sql statements of the routine execute with the routine creator authorization. They are executed with authorization of a user who runs the routine otherwise.
You can do it by calling the following statement before the CREATE OR REPLACE PROCEDURE statement in the same session:

CALL SET_ROUTINE_OPTS('DYNAMICRULES BIND')

But you are not able to execute dynamically the following statements with such a bind option as described in Table 2. Definitions of Dynamic SQL Statement Behaviors:

GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT ON, RENAME, SET INTEGRITY, and SET EVENT MONITOR STATE

This means, that you are not able to run dynamically ALTER SEQUENCE with an authorization different from the invoker's one.
You may grant ALTER privilege on sequence to your developer to resolve the issue.

• Neha: Thanks Mark. Really appreciate your help. However, the solution didnt work. I ran call set_routine_opts('DYNAMICRULES BIND') in the same session as create procedure as dbadm user. Upon calling the procedure with dev userid, I am getting error "SQL0549N The "ALTER" statement is not allowed for "package" "<package name>" because the bind option DYNAMICRULES RUN is not in effect for the "package". SQLSTATE=42509"
• Mark Barinstein: Sorry, I forgot that some statements can't be executed in such a routine. I've updated the answer.