A batch process run by user U drops and creates a work table T. Simplified setup:
rights for user U
db2 grant dataaccess on database to user U db2 grant all on table T to user U db2 transfer ownership of table T to user U
Since the code is called from several places I thought it would be nice to drop and create the table from a procedure P, to avoid that different code creates different looks for the table.
db2 -td@ -f "create procedure P() LANGUAGE SQL BEGIN BEGIN -- do nothing if drop table fails DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END; execute immediate 'drop table T'; END; execute immediate 'CREATE TABLE T (x int)'; END @"
However, when user U calls this procedure it fails with:
db2 "call P()" SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: "U". Operation: "CREATE TABLE". Object: T SQLSTATE=42501
Is it possible to grant rights to user U (beside dbadm or other nuclear options :) so that it can successfully execute procedure P?