To list the database authorities held by an ID or group – authorities, not privileges. This information is not available in sysibmadm.privileges. The format of syscat.dbauth has columns with ‘Y’ or ‘N’ in them, and reading that quickly to answer the question “What permissions does this ID have?” can be a bit frustrating. This SQL could also be union-ed with SQL to query sysibmadm.privileges to include the privileges on database objects in one result set.
Tested on 10.5. Should work other versions, though the columns that exist may vary by version, so may have to be removed for older versions.
select authority from syscat.dbauth d , lateral (values('BINDADD', BINDADDAUTH) , ('CONNECT', CONNECTAUTH) , ('CREATETAB', CREATETABAUTH) , ('DBADM', DBADMAUTH) , ('EXTERNALROUTINE', EXTERNALROUTINEAUTH) , ('IMPLSCHEMA', IMPLSCHEMAAUTH) , ('LOAD', LOADAUTH) , ('NOFENCE', NOFENCEAUTH) , ('QUIESCECONNECT', QUIESCECONNECTAUTH) , ('LIBRARYADM', LIBRARYADMAUTH) , ('SECADM', SECURITYADMAUTH) , ('SQLADM', SQLADMAUTH) , ('WLMADM', WLMADMAUTH) , ('EXPLAIN', EXPLAINAUTH) , ('DATAACCESS', DATAACCESSAUTH) , ('ACCESSCTRL', ACCESSCTRLAUTH) , ('CREATESEC', CREATESECUREAUTH) ) as p(authority, held) where grantee='PUBLIC' and held='Y';
AUTHORITY --------------- BINDADD CONNECT CREATETAB IMPLSCHEMA 4 record(s) selected.
This can easily be modified to also return the user holding the privilege. It can also be union-ed with a query to sysibmadm.privileges to get the permissions that the id has on objects within the database.
Though he left Db2 long ago, I still love Serge Rielau’s blog for things like this. I learned the un-pivot technique used here from his entry on pivoting tables.