DB2 – How to Change the Owner of a Stored Procedure

db2

Let's say that I have a procedure called schema.proc1.
How do I change the owner of it? I'm using db2 express-c 10.5

I've tried the following command running as the olduser,

transfer ownership of procedure schema.proc1 to user newuser preserve privileges;

I want to do this because I believe this is a constraint that I'm having to not being able to create or replace schema.proc1 as newuser.

I would like to point out what I've done regarding privileges and the newUser (all of this were done with the oldUser).

TRANSFER OWNERSHIP OF SCHEMA mySchema TO USER newUser PRESERVE PRIVILEGES;
GRANT ALTERIN ON SCHEMA mySchema TO USER newUser WITH GRANT OPTION;
GRANT CREATEIN ON SCHEMA mySchema TO USER newUser WITH GRANT OPTION;
GRANT DROPIN ON SCHEMA mySchema TO USER newUser WITH GRANT OPTION;


GRANT BINDADD ON DATABASE TO USER newUser with grant option;
GRANT CONNECT ON DATABASE TO USER newUser with grant option;
GRANT CREATETAB ON DATABASE TO USER newUser with grant option;
GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO USER newUser with grant option;
GRANT CREATE_NOT_FENCED_ROUTINE ON DATABASE TO USER newUser with grant option;
GRANT IMPLICIT_SCHEMA ON DATABASE TO USER newUser with grant option;
GRANT LOAD ON DATABASE TO USER newUser with grant option;
GRANT QUIESCE_CONNECT ON DATABASE TO USER newUser with grant option;
GRANT EXPLAIN ON DATABASE TO USER newUser with grant option;
GRANT SQLADM ON DATABASE TO USER newUser with grant option;
GRANT WLMADM ON DATABASE TO USER newUser with grant option;


grant createin on schema mySchema to newUser with grant option;
grant alterin on schema mySchema to newUser with grant option;
grant dropin on schema mySchema to newUser with grant option;

Event with all of this, still no luck on replacing a stored procedure.

Thanks in advance!

Best Answer

I believe you are correct on the syntax. A few points to note.

  1. You cannot grant to yourself or revoke from yourself, so someone else may need to grant you the privileges you are after.
  2. You may need the DB level privileges CREATE_EXTERNAL_ROUTINE and/or CREATE_NOT_FENCED_ROUTINE granted to you. May may also need BINDADD granted to you as well. These privileges allow you to create stored procedures.
  3. You do need to be the owner of the stored procedure to do a REPLACE. Otherwise as long as you have CREATIN and DROPIN on the schema, you might be able to DROP and then CREATE the stored procedure.
Related Question