Wednesday, August 3, 2011

System-reserved roles in DB2 version 9.7

DB2 9.7 added a few built-in roles like SYSROLE_AUTH_DBADM, SYSROLE_AUTH_SECADM.  These roles show up in the syscat.roles and syscat.roleauth catalog views as regular roles, but DB2 treats them as   special roles reserved exclusively for certain database authorities. For example, SYSROLE_AUTH_DBADM is granted automatically whenever DBADM is granted to someone. And it is revoked automatically when DBADM is revoked. These  roles can not be granted/revoked manually (using grant/revoke statement for example). These roles hold system chosen object privileges. For example, SYSROLE_AUTH_DBADM role hold EXECUTE privilege over many (all?) procedures/functions under the SYSPROC, SYSIBMADM, SYSIBMINTERNAL schemas.


  1. why do we need Role Based privilges to SYSROLE_AUTH_DBADM and how are these privileges different from the DBADMIN ones, if these privileges cannot be granted (and Revoked) automatically from SYSROLE_AUTH_DBADM .. Can't DBADMIN hold execute privileges on procedures/functions as you stated in your earlier post.

  2. I may not have understood your comment correctly so please correct me in that case. I think the reason DB2 decided to have such reserved roles as SYSROLE_AUTH_DBADM, is simply to make it easy for them to internally manage privileges for system objects.