Db2 – Run a Stored Procedure in DB2 (which has dynamic DDL) under the Definer rights

db2

For our Datawarehouse applications, we want to perform ALTER TABLE ALTER CONSTRAINT <constname> NOT ENFORCED, before the load. Perform the load and then ALTER TABLE ALTER CONSTRAINT <constname> ENFORCED.

Tables are owned by DBA (SYSADM) and the application id doesn't have rights to perform ALTER.

So, the DBA asked me to create a Stored Procedure which will be owned by DBA and will be invoked by the application id.

For this, we explored DYNAMICRULES option of DB2.

So before the procedure is created by the DBA, the below call is made by the DBA

  1. CALL SET_ROUTINE_OPTS('DYNAMICRULES RUN)
  2. Procedure is created under DBA owner
  3. Execute grant privilege to the application id

When I called the procedure using the application id, the error that I get is

SQL0551N "App id" does not have the required authorization or privilege to
perform operation "ALTER TABLE" on object "DBA.Object_name".

The documentation that I referred is

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0007210.htm

Any help on this would be very much appreciated, as I feel that Dynamic DDLs can't be allowed to execute using Definer rights by a invoking auth id.

Best Answer

In general, dynamic SQL statements can be executed with definer (binder) rights. However, certain DDL statements, including ALTER TABLE can only be executed dynamically under DYNAMICRULES RUN.

I'm not sure why you chose to run the statement dynamically. DB2 allows static execution of DDL statements, in which case they execute with the definer authority.

A simple example: 

db2inst1@blusrv:~> db2 "create procedure test begin create table blah (f1 int); end"
DB20000I  The SQL command completed successfully.
db2inst1@blusrv:~> db2 "call test"

  Return Status = 0
db2inst1@blusrv:~> db2 describe table blah

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
F1                              SYSIBM    INTEGER                      4     0 Yes   

  1 record(s) selected.
Related Question