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()
           -- do nothing if drop table fails
           execute immediate 'drop table T';
       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?

  • 1
    Firstly, in your example you rely on implicit schema resolution, which might be a factor. Secondly, there are specific privileges that U needs to have in order to create a table in a particular schema and tablespace, which you don't grant. U's and the other user (grantor) membership in various groups and roles might also affect the scenario. – mustaccio May 13 '20 at 19:25
  • I think you nailed it. DROP is ok since U owns the table, but create is not ok since U does not have CREATIN on schema. I think I will move the table out to another schema. If you add an answer I will accept it. – Lennart May 13 '20 at 19:56

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.

  • It's still interesting that you don't see the problem when running the same commands outside the routine. Does the PUBLIC group have permissions on the schema and tablespace? Or some other group or role that U belongs to? If so, your problem might be caused by some funny application of DYNAMICRULES inside the routine. – mustaccio May 15 '20 at 20:05
  • I'm not sure why that worked. When I tried, later on, I could not repeat the success, instead got the same error. The most likely cause is that I had terminals open on several desktops, and accidentally switched to a terminal connected as another user. Sorry for the confusion this caused. – Lennart May 15 '20 at 20:19
  • I'll try to rephrase the question – Lennart May 15 '20 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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