DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501 and group permissions

Revised 13 Mar 2014

The Situation

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.

#db2, #db2-sql-error, #sql, #sql0551n, #sqlcode-551, #sqlstate-42501