Compare All Object Authorities in a Library

  • Home
  • /
  • Blog
  • /
  • Compare All Object Authorities in a Library

September 12, 2023

Compare All Object Authorities in a Library

By NickLitten

September 12, 2023

SQL, authority, IBM i

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'
Compare all object authorities in a library

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:

Compare all object authorities in a library 1

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.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

>