DB2 Administrative SQL Cookbook: Listing Database Authorities that an ID/Group Holds

Posted by

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.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 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 for XTIVIA, leading a team of Db2 DBAs.

7 comments

    1. 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.

  1. 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

  2. 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

    1. 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.

      1. 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.

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.