SQL Views for Authorization Lists

Many companies that use IBM i secure their objects using Authorization Lists. The Authorization List is its own object type, *AUTL. If you have not used or heard of them before I found this good description of them in IBM's KnowledgeCenter.

Like a group profile, an authorization list allows you to group objects with similar security requirements and associate the group with a list of users and user authorities.

Authorization lists provide an efficient way to manage the authority to similar objects on the system and aid in the recovery of security information.

I am not going to discuss in this post how to create Authorization Lists, how to add objects to the lists, or anything similar. I am just going to describe two new SQL Views that were added in the latest IBM i Technical Refreshes, 7.3 TR2 and 7.2 TR6:

• AUTHORIZATION_LIST_USER_INFO
• AUTHORIZATION_LIST_INFO

In these examples I have created an Authorization List called TEST, and a file called TESTFILE that I have added to the Authorization List TEST.

 

AUTHORIZATION_LIST_USER_INFO

This View's name pretty much describes what it returns, a list of all authorization lists and their authorities. I am not going to list all the columns found in this View here, and I will be using the short/system names for them too to save space in this post. I have placed a link to the appropriate page in IBM's KnowledgeCenter to the documentation for this View where you will find this information.

First I need to check who is authorized within the Authorization List. Before the new TRs I would have had to use the Display Authorization List command, DSPAUTL, to create an outfile that I could then query:

DSPAUTL AUTL(TEST) OUTPUT(*OUTFILE) OUTFILE(QTEMP/@DSPAUTL)

If I had used to display the output from the DSPAUTL to my screen it would look like:

Display Authorization List Object . . . . . : TEST Owner . . . . . : QPGMR Library . . . : QSYS Primary group . : *NONE Object List ----------Object----------- User Authority Mgt Opr Mgt Exist Alter Ref *PUBLIC *EXCLUDE SIMON *ALL X X X X X X

With the latest TRs applied I can use the AUTHORIZATION_LIST_USER_INFO view to get the same results I need just in one step:

01 SELECT * FROM QSYS2.AUTHORIZATION_LIST_USER_INFO 02 WHERE AUTL = 'TEST'

Line 2: The column AUTL is the name of the Authorization List.

When this Select statement is run I can see all of the settings for my Authorization List TEST.

AUTHORIZATION_LIST AUTHORIZATION_NAME OBJECT_AUTHORITY TEST *PUBLIC *EXCLUDE TEST SIMON *ALL AUTHORIZATION_LIST OWNER _MANAGEMENT NO QPGMR YES QPGMR OBJECT_OPERATIONAL OBJECT_MANAGEMENT OBJECT_EXISTENCE NO NO NO YES YES YES OBJECT_ALTER OBJECT_REFERENCE NO NO YES YES DATA_READ DATA_ADD DATA_UPDATE DATA_DELETE DATA_EXECUTE NO NO NO NO NO YES YES YES YES YES TEXT_DESCRIPTION Test Authorization List Test Authorization List

I can also use this View to check if any of the Authorization Lists give *PUBLIC *ALL authority, which would not be a good thing.

01 SELECT AUTL,USER_NAME,OBJ_AUTH 02 FROM QSYS2.AUTHORIZATION_LIST_USER_INFO 03 WHERE USER_NAME = '*PUBLIC' 04 AND OBJ_AUTH = '*ALL'

With this statement I only want to see the following columns:

• AUTL = Authorization List name
• USER_NAME = User profile or *PUBLIC
• OBJ_AUTH = Authority to the objects belonging to this Authorization List

Lines 3 and 4: I only want to select rows for *PUBLIC and where the authority is *ALL.

I find there is just one Authorization List that was returned:

AUTHORIZATION_LIST USER_NAME OBJ_AUTH STEVEO *PUBLIC *ALL

 

AUTHORIZATION_LIST_INFO

Having seen the authorization configuration for the Authorization List I now need to see what objects belong to it. Fortunately there is a view for that, AUTHORIZATION_LIST_INFO. I will just be using a few columns from this View, and be using the short/system column names. If you want to learn more about this view I am providing a link to IBM's KnowledgeCenter page for this View at the end of this post.

Let me see which objects belong to my Authorization List TEST.

01 SELECT AUTH_LIST AS AUT_LIST,SYS_DNAME AS LIB, 02 SYS_ONAME AS OBJ,SYS_OTYPE AS OBJ_TYPE, 03 OBJATTR AS OBJ_ATTR,OWNER, 04 TEXT AS DESCRIPTION 05 FROM QSYS2.AUTHORIZATION_LIST_INFO 06 WHERE AUTH_LIST = 'TEST'

I am giving the columns different names just to make results more easily understood when they are displayed. The columns I am using are:

• AUTH_LIST = name of the Authorization List
• SYS_DNAME = name of the library the object is within
• SYS_ONAME = name of the object
• SYS_OTYPE = object type, *FILE, *PGM, etc.
• OBJATTR = object attribute, PF, LF, RPGLE, etc.
• OWNER = owner user profile
• TEXT = object text (description)

Line 6: I am only interested in objects belonging to the Authorization List TEST.

The result from this Select statement is:

AUT_LIST LIB OBJ OBJ_TYPE OBJ_ATTR OWNER TEST MYLIB TESTFILE *FILE PF QPGMR DESCRIPTION File used for testing

Now you can see why I wanted to rename the columns.

Or how about what Authorization list my file belongs to:

01 SELECT AUTH_LIST AS AUT_LIST,SYS_DNAME AS LIB, 02 SYS_ONAME AS OBJ,SYS_OTYPE AS OBJ_TYPE 03 FROM QSYS2.AUTHORIZATION_LIST_INFO 04 WHERE SYS_ONAME = 'TESTFILE' 05 AND SYS_DNAME = 'MYLIB' 06 AND SYS_OTYPE = '*FILE'

Lines 4 - 6: By using the WHERE clause I can select just the object(s) I want.

 

I can, of course, combine the two Views to give a list of all the objects that are allocated to a Authorization List where *PUBLIC has *ALL rights.

01 SELECT A.AUTL AS AUTL,A.USER_NAME AS USER, 02 A.OBJ_AUTH AS AUTH, 03 B.SYS_DNAME AS LIB ,B.SYS_ONAME AS OBJ, 04 B.SYS_OTYPE AS TYPE,B.OBJATTR AS ATTR 05 FROM QSYS2.AUTHORIZATION_LIST_USER_INFO A 06 LEFT OUTER JOIN QSYS2.AUTHORIZATION_LIST_INFO B 07 ON A.AUTL = B.AUTH_LIST 08 WHERE A.USER_NAME = '*PUBLIC' 09 AND A.OBJ_AUTH = '*ALL' 10 ORDER BY A.AUTL,B.SYS_DNAME,B.SYS_ONAME

Lines 1 – 4: I am renaming the columns to make them easier to display below.

Line 6: A left outer join gives me all the rows that match the selection from the first View. If you want to learn more about types of SQL joins I have a SQL joins cheat sheet.

When I execute this statement I get:

AUTL USER AUTH LIB OBJ TYPE ATTR STEVEO *PUBLIC *ALL STEVELIB STEVEFILE1 *FILE PF STEVEO *PUBLIC *ALL STEVELIB STEVEFILE2 *FILE PF STEVEO *PUBLIC *ALL STEVELIB STEVEPGM1 *PGM RPGLE

 

While the information available in these Views has been available from various commands and APIs it is, in my opinion, good to have this information so easily available in a SQL View. I cannot wait to see what other kinds of system type information IBM will gives us as Views in the future.

 

You can learn more about this from the IBM website:

• AUTHORIZATION_LIST_USER_INFO view
• AUTHORIZATION_LIST_INFO view

 

This article was written for IBM i 7.3 TR2, and should work for 7.2 TR6 too.