Purpose
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.
Version
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.
Statement
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';
Sample Output
AUTHORITY --------------- BINDADD CONNECT CREATETAB IMPLSCHEMA 4 record(s) selected.
Modifications
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.
References
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.
This approach lists the database authorities directly held by the ID. Another way to look at this information is through the AUTH_LIST_AUTHORITIES_FOR_AUTHID table function (https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0052898.html). The output is a bit more complex but it shows all the indirect access given to the ID and the SYS* authorities as well…
I’ll have to apply a similar approach to simplify the output from it as well. I’ve used AUTH_LIST_AUTHORITIES_FOR_AUTHID before.
Hi Ember,
that’s a very useful query, thanks for sharing!
Also, should it not say “select authority” instead of “select privilege”, with the correlated table defined as “p(authority, held)”?
Regards, Damir
Thanks for the correction, and sorry for the mistake!
Hi Ember
You need to change “select privilege” to “select authority” for your SQL to work.
I’m currently coding a module similar to MONREPORT that does a top down security analysis to help determine Db2 exposure to potential security threats, including determining if the system is NIST/FIPS compliant, exposure to PUBLIC and so on.
Here’s some SQL to find all authorities granted for all Db2 known USERIDs, Groups, Roles
WITH ALL_KNOWN (GRANTEE, GRANTEETYPE)
AS(
SELECT authid, authidtype
FROM sysibmadm.PRIVILEGES
union
SELECT GRANTEE, GRANTEETYPE
from syscat.dbauth
)
SELECT GRANTEE, GRANTEETYPE, AUTHORITY,
CASE
WHEN D_USER = ‘Y’
THEN ‘DIRECT’
WHEN D_PUBLIC = ‘Y’ OR ROLE_PUBLIC = ‘Y’
THEN ‘PUBLIC’
WHEN D_GROUP = ‘Y’ OR ROLE_USER = ‘Y’ OR ROLE_GROUP = ‘Y’ OR D_ROLE = ‘Y’
THEN ‘INDIRECT’
ELSE ‘NO’
END RECEIVED,
D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE
FROM ALL_KNOWN, TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID (GRANTEE, GRANTEETYPE) ) AS T
WHERE
D_USER = ‘Y’ or D_PUBLIC = ‘Y’ OR ROLE_PUBLIC = ‘Y’or D_GROUP = ‘Y’ OR ROLE_USER = ‘Y’ OR ROLE_GROUP = ‘Y’ OR D_ROLE = ‘Y’
ORDER BY AUTHORITY
Cheers
Rob
I was disappointed when they backed off an approach of letting users modify and extend the MONREPORT module. I’d love the ability to have a framework to easily develop and deploy my own stuff in that area.
I analysed MONREPORT and found it poorly written in places. For example, the use of SYSIBM.SQLCAMESSAGECCSID in INITMSGCACHE is a back door method of reading a file without having a dependency on SYSTOOLS (required by UTL_DIR).
The output from INITMSGCACHE is hard coded in relation to its invocation – why not just hard code it in the relevant places in MONREPORT? No less a bad design (potentially) but more readable. (I just found your blog https://datageek.blog/2014/12/04/customizing-monreport-or-not/)
I used Data Studio to export the DDL for MONREPORT and then did similar edits to yours in regards to formatting the code.
Once I understood what they were doing with INITMSGCACHE, I then took a while to get my head around arrays. These are extremely powerful. I make use of arrays to store Db2 config data (REG_VARIABLE, DBMCFG, DBCFG) – among other things.
It wouldn’t be too difficult to create your own module and generate a csv. All you need to do is format your equivalent of REPOUTPUT. It’s not too difficult once you eliminate INITMSGCACHE and the confusion it brings.