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

Posted by

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

Sample Output

Modifications

This statement could be re-written to write the SQL needed to grant or revoke permissions to/from the ids returned.

References

IBM Db2 Knowledge Center entry on the AUTH_LIST_GROUPS_FOR_AUTHID table function
IBM Db2 Knowledge Center entry on the AUTH_LIST_ROLES_FOR_AUTHID table function
IBM Db2 Knowledge Center entry on the SYSIBMADM.PRIVILEGES administrative view
IBM Db2 Knowledge center entry on the SYSCAT.DBAUTH system view

Lead Database Administrator
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.