A Groovy script that executes some SQL against a DB2 for LUW 9.7 database throws an exception:
com.ibm.db2.jcc.a.bn: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=USERID;EXECUTE;SCHEMA.GET_FACE_AMOUNT, DRIVER=3.51.90
IBM states that SQLCODE -551 means:
authorization-ID does not have the required authorization or privilege to perform operation operation on object object-name.
Initially, this seems to be a pretty straightforward problem. This is a database that was recently copied from another system. It is not unlikely that when it was restored, permissions were not granted to USERID to execute SCHEMA.GET_FACE_AMOUNT.
However, once the database administrator demonstrates that he can execute the GET_FACE_AMOUNT function while logged on as USERID, we’re left with a puzzle.
What We Learned
Our DB2 permissions were applied to user groups. The executing userid is a member of the ‘rw’ group, which should have read, write and execute permissions to the GET_FACE_AMOUNT function. As it turns out, however, DB2 does not apply group permissions to user defined functions.
It’s a much better practice to manage database permissions using roles. I’m not a DB2 DBA, so I can’t comment on exactly what the differences between groups and roles are. However, it seems like roles are a more robust way of defining permissions for a collection of users.