4

I have a list of user accounts on AIX server, where the db2 database resides in. The user accounts all belong to a specific group which I can confirm from aix shell.

User accounts work in DB2. However, I cannot seem to be able to add execution rights to the group so I wouldn't have to grant execution rights to individual user accounts.

I have tried this:

GRANT EXECUTE ON PROCEDURE MYSCHEMA.MYPROCEDURE TO GROUP MYGROUP WITH GRANT OPTION;

using a user that has created the stored procedures, and it succeeds. However, when trying to execute that procedure using another account that belongs to that group in aix server, I get

"useraccounthere" does not have the required authorization or privilege to perform
operation "EXECUTE" on object "MYSCHEMA.MYPROCEDURE".. SQLCODE=-551, SQLSTATE=42501,
DRIVER=4.14.113

Any ideas what could be wrong, or what I could check?

  • I would check what the stored procedure runs over. If the group (or user) does not have permission to an underlying object (ie, table, view, array, etc.), then they fail to be able to execute the stored procedure. – Chris Aldrich Apr 30 '14 at 12:42
  • Also, are you using transparent LDAP lookup? ie, do you have db2set DB2AUTH=OSAUTHDB -i ? – Chris Aldrich Apr 30 '14 at 12:44
  • What does this show: select a.specificname, a.grantee, a.granteetype, a.executeauth from syscat.routineauth a, syscat.routines r where (r.routineschema, r.specificname) = (a.schema, a.specificname) and (r.routineschema, r.routinename) = ('YOURSCHEMA','YOURPROC') – mustaccio May 1 '14 at 20:16
  • Thanks for the comments! I'll test on Monday once I'm back to work. – eis May 2 '14 at 9:39
  • Forgot to update on this - it was indeed as @ChrisAldrich mentioned in the first comment. – eis Dec 23 '14 at 18:46
1

I ran into a similar problem and while this wasn't what happened here, this answer may help others running into this.

Make sure the OS group mygroup is all lowercase. DB2 on AIX does not recognize uppercase letters in the group name and will fail to validate a user belongs to a group if that group name has uppercase letters. eg:

# id useraccounthere
uid=xx(useraccounthere) gid=yyy(mygroup) <- this works

# id useraccounthere
uid=xx(useraccounthere) gid=yyy(MyGroup) <- this does not work

This is documented here: DB2 UDB Security

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.