Wednesday, June 14, 2017

SQL Views for Authorization Lists

views for authorization lists to view user authorities and objects belonging to them

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:

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:

 

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

5 comments:

  1. another great post, appreciate your efforts

    ReplyDelete
  2. Great examples, but don't forget about the stream files AUTL objects can control. Add PATH_NAME to see these.

    -Matt

    ReplyDelete
  3. Here is my problem, I want to list all user profiles on the system that are on authority lists. Now, using DSPAUTL does not have an outfile option where I can send the file to a spread sheet. There is the DSPOBJAUT command that does have an outfile option, but when I use it in a CL program using a variable that would contain the authorization list the program fails so I am guessing I am using it incorrectly. The person before me had been able to produce a listing of all the profiles on the system and the authority list they have access to with the read, write, etc.... privileges. Can you suggest a way to create what I need? It can't be all that difficult, but I'm just not seeing it. Thanks!

    ReplyDelete
    Replies
    1. Why not use the Views described above?
      You can select the columns you want in your outfile and then use the CREATE TABLE SQL statement to create it.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.