DB2 Administrative SQL Cookbook: List Privileged IDs that do not Have the Connect Authority

Purpose

To list IDs that have one or more privileges, but do not have the CONNECT authority through a direct grant, a group, or a role, either explicitly or implicitly. I used this to find IDs I might need to grant CONNECT to before revoking connect from PUBLIC.

Version

Tested on 11.1. Should work at least back to 9.7, maybe further.

Statement

select authid
            , authidtype
        from sysibmadm.privileges
        where ((authid, authidtype) not in (select grantee
                                           , granteetype
                                           from syscat.dbauth
                                           where (connectauth='Y'
                                                  or DBADMAUTH='Y'
                                                  or connectauth='G'
                                                  or DBADMAUTH='G')
                                               and granteetype='U'))
            and not exists (select group
                                , connectauth
                                , dbadmauth
                            from table( auth_list_groups_for_authid(authid)) gl
                            join syscat.dbauth da on gl.group=da.grantee and da.granteetype='G'
                            where (connectauth='Y'
                                   or DBADMAUTH='Y'
                                   or connectauth='G'
                                   or DBADMAUTH='G')
                                and group != 'PUBLIC')
            and not exists (select rolename
                                , connectauth
                                , dbadmauth
                            from table( auth_list_roles_for_authid(authid,'U')) gl
                            join syscat.dbauth da on gl.grantee=da.grantee and da.granteetype='R'
                            where (connectauth='Y'
                                   or DBADMAUTH='Y'
                                   or connectauth='G'
                                   or DBADMAUTH='G'))
            and authidtype='U'
        group by authid, authidtype
        with ur