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?
Best Answer
I'll add an answer then. The comment by mustaccio made me realize that U is able to drop T since U owns T. But as soon as T is dropped, U can not recreate it since
CREATIN
is not granted to U in the schema.The solution is to create a schema S, GRANT CREATIN/DROPIN ON S to user U, then change the schema of T to S.