Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
DYNAMICRULES RUN means that any dynamic statements executed by the stored procedure are executed using the authority of the user executing the stored procedure, NOT the definer. –  Ian Bjorhovde Mar 10 at 19:18

1 Answer 1

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.
share|improve this answer
    
I am not aware of the fact that DDLs can be executed statically. Can you point me to a link that contains how to do it? DDLs have to prepared and executed dynamically in DB2 Stored procedures. –  Senthil Mar 10 at 1:29
1  
"DDLs have to prepared and executed dynamically in DB2 Stored procedures." -- That's not true. Unlike some other databases, in DB2 DDL statements are just like any other SQL statements and can be embedded (executed statically). Check the example above. –  mustaccio Mar 10 at 12:17
    
I understand for CREATE table. But I am executing an ALTER TABLE and I get an SQL0104N error when I do so. My use case is to ALTER TABLE ...NOT ENFORCED, Perform a load and then ALTER TABLE...ENFORCED –  Senthil Mar 10 at 18:00
    
SQL0104N means you have a syntax error somewhere. Would you like me to guess where exactly? –  mustaccio Mar 10 at 18:02
    
Can you pls try this and let me know what happens...create OR REPLACE procedure test begin alter table blah add column f2 int ; end..I am using Db2 9.7 –  Senthil Mar 10 at 18:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.