Compare All Object Authorities in a Library

Comparing Object Authorities across different libraries is easier than you think!

Using the magical powers of IBM i SQL we can easily create a spreadsheet containing all the objects in any given library and showing all their authority settings. You can run this SQL statement using green screen STRSQL or from your favorite desktop SQL interface (I like to use the IBM i ACS SQL functions)

Using  IBM i SQL Service OBJECT_PRIVILEGES we can see a lovely SQL result set very similar to the old fashioned green screen DSPOBJAUT command.

SELECT *
    FROM QSYS2.OBJECT_PRIVILEGES
    WHERE SYSTEM_OBJECT_SCHEMA = 'mylibrary'

of course you could run this for multiple libraries like this:

SELECT *
    FROM QSYS2.OBJECT_PRIVILEGES
    WHERE SYSTEM_OBJECT_SCHEMA = 'mylibrary' or
        SYSTEM_OBJECT_SCHEMA = 'mylibrary2' or
        SYSTEM_OBJECT_SCHEMA = 'mylibrary3'

Or use a nice tool like IBM i ACS to download the whole thing as a spreadsheet:

Caveat Emptor

Authorization: All authorized users are returned for an object when at least one of the following is true:

• The caller has *OBJMGT authority.
• The caller is the owner of the object.
• The object is an authorization list.
• The caller is authorized to the Database Security Administrator function of IBM i. The Change Function Usage (CHGFCNUSG) command, with a function ID of QIBM_DB_SECADM, can be used to change the list of users allowed to use the function.

Otherwise, only authorizations for the caller are returned.