DB2/dashDB Security: Implicit Privileges Through Group Membership

DB2 Data Security

I recently saw an interesting DB2 question on Stack Overflow. Someone asked how it is possible to find out privileges for a user when the privileges were granted to a group the user is member of. DB2 does not manage group membership within the database, it is done in the operating system. But DB2 offers functions and views to retrieve that information and to simplify analysis of the security-related metadata. And remember that this applies to IBM dashDB as well.

To look up which groups a specific user belongs to, DB2 offers the table function AUTH_LIST_GROUPS_FOR_AUTHID. The returned groups are not necessarily used within the database and can be any operating system group. The following query returned several of those typical Linux groups:

SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('HLOESER')) as T

An administration view that comes handy is SYSIBMADM.AUTHORIZATIONIDS. It lists all authorization IDs along their respective type, i.e., groups, users and roles. When combined with another view, SYSIBMADM.PRIVILEGES, which lists all explicit privileges for all authorization IDs (that is for users, groups and roles), it allows to filter, e.g., for specific group or role privileges. Joining in the groups for a specific user and not forgetting to factor in PUBLIC privileges, I came up with the following query. It should list all the implicit privileges I have.

SELECT distinct p.AUTHID, p.PRIVILEGE, p.OBJECTNAME, p.OBJECTSCHEMA, p.OBJECTTYPE
FROM SYSIBMADM.PRIVILEGES P, SYSIBMADM.AUTHORIZATIONIDS A,
     TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('HLOESER')) as U 
WHERE p.privilege='CREATEIN' AND a.authidtype='G'
      AND a.authid=p.authid
      AND (u.group=a.authid or a.authid='PUBLIC')


If you want to know all your privileges, just UNION the result above with a query on SYSIBMADM.PRIVILEGES for your authid:

SELECT distinct p.AUTHID, p.PRIVILEGE, p.OBJECTNAME, p.OBJECTSCHEMA, p.OBJECTTYPE
FROM SYSIBMADM.PRIVILEGES P, SYSIBMADM.AUTHORIZATIONIDS A, TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('HLOESER')) as U 
WHERE p.privilege='CREATEIN' and a.authidtype='G' and a.authid=p.authid
AND (u.group=a.authid or a.authid='PUBLIC')
UNION
SELECT distinct p.AUTHID, p.PRIVILEGE, p.OBJECTNAME, p.OBJECTSCHEMA, p.OBJECTTYPE
FROM SYSIBMADM.PRIVILEGES P
WHERE p.authid='HLOESER'