Wednesday, November 11, 2015

Getting information about User Profiles using SQL

sql view user_info

As audit season comes round again there are a standard set of reports I need to prepare for the IBM i I am responsible for. Amongst them would be: a list of all the user profiles with the date they last signed on, user profiles with *ALLOBJ authority, etc.

I would use the Display User Profile command, DSPUSRPRF, with the outfile option to create a file I could then use Query to extract the information I wanted from it. The problem was that the data in the file is static.

Introduced in, I believe, IBM i 7.1 is just what I wanted a View USER_INFO, in the library QSYS2, which contains lots of useful information about user profiles. I can now get the information I want directly from this View. I can also build Views of the information I want over this View.

I am not going to list all the columns available in USER_INFO as I do not want to make this post too long and IBM does a good job in their KnowledgeCenter web site here. In these examples I am going to use the short "System Column Name" to save space in the code panels.

I have created a View, USR_LIST, to give me a list of all the active user profiles, with the last date they signed on:

01  CREATE VIEW MYLIB/USR_LIST
02    (USER_NAME,USRCLS,STATUS,PRVSIGNON)
03  AS SELECT USER_NAME,USRCLS,STATUS,
04            DATE(PRVSIGNON)
05       FROM QSYS2.USER_INFO
06       WHERE STATUS = '*ENABLED' ;

07  LABEL ON COLUMN MYLIB/USR_LIST
08    (USER_NAME IS          'User                profile',
09     USRCLS IS             'Profile             class',
11     STATUS IS             'Profile             status',
12     PRVSIGNON IS          'Previous            signon') ;

For those of you unfamiliar with creating SQL/DDL tables should read How I code source for SQL DDL tables.

Notice that on line 4 I have converted the column PRVSIGNON from a timestamp to a date field.

And, of course, I am only interested in active (*ENABLED) user profiles, line 6.

To get the data I can just execute the following SQL statement:

  SELECT * FROM MYLIB.USR_LIST ORDER BY USER_NAME

For those of you thinking you will be able to see the user profiles that are on this IBM i are going to be disappointed as I am going to obscure the user profiles I show.

User        Profile     Profile     Previous
profile     class       status      signon
AG********  *USER       *ENABLED    09/30/2008
AJ********  *USER       *ENABLED    06/05/2008
AK********  *USER       *ENABLED    07/31/2015
AL********  *USER       *ENABLED    07/14/2008
AM********  *USER       *ENABLED    06/08/2009

Before I give the next example I need to introduce another View: Group Profile Entries, GROUP_PROFILE_ENTRIES. It should come as no surprise that this View is in QSYS2. The View contains one row for each user profile that is part of either the Group Profile (GRPPRF) or Supplemental Group Profile (SUPGRPPRF) for each user profile. I am not going to list the columns in this view as IBM does a good job of doing it here.

If I want to list all the user profiles that have QPGMR as either their Group Profile or Supplemental Group Profile I could just use the following SQL statement:

  SELECT CAST(GROUPNAME AS CHAR(10)) AS GROUP,
         CAST(USERNAME AS CHAR(10)) AS USER
    FROM QSYS2.GROUP_PROFILE_ENTRIES
   WHERE GROUPNAME = 'QPGMR'

I have used CAST as both columns are variable length of 128, and I just want to display them like this:

  GROUP       USER
  QPGMR       AP********
  QPGMR       BS********
  QPGMR       CA********
  QPGMR       DA********
  QPGMR       DW********
  QPGMR       ER********
  QPGMR       FT********

Now for something more complex: a list of user profiles with All Object authority, *ALLOBJ. What makes this complicated is that the user profile can "inherit" authority from a group profile it is a member of. This means that my View will have to check first if *ALLOBJ is found for the user profile, lines 5 to 6, and then check for *ALLOBJ in any group profiles that the user profile belongs to, lines 7 and 10.

01  CREATE VIEW MYLIB/USR_ALLOBJ
02    (USER_NAME,USRCLS,SPCAUT)
03  AS SELECT USER_NAME,USRCLS,SPCAUT
04       FROM QSYS2.USER_INFO
05      WHERE STATUS = '*ENABLED'
06        AND SPCAUT LIKE '%*ALLOBJ%'
07     OR USER_NAME IN (SELECT USERNAME FROM QSYS2.GROUP_PROFILE_ENTRIES
08                       WHERE GRPPRF IN (SELECT USER_NAME 
09                                          FROM QSYS2.USER_INFO
10                                         WHERE SPCAUT LIKE 
11                                                      '%*ALLOBJ%')) ;

12  LABEL ON COLUMN MYLIB/USR_ALLOBJ
13    (USER_NAME IS          'User                profile',
14     USRCLS IS             'Profile             class',
15     SPCAUT IS             'Special             authorities') ;

When I run the following SQL statement:

  SELECT * FROM MYLIB.USR_ALLOBJ ORDER BY USER_NAME

It gives me:

User        Profile     Special
profile     class       authorities
AM********  *USER       *ALLOBJ    *JOBCTL    *SPLCTL
AP********  *PGMR       *ALLOBJ    *SECADM    *JOBCTL
AT********  *USER
DP********  *PGMR       *ALLOBJ    *SECADM    *JOBCTL

The user profile AT******** does not display the *ALLOBJ as that is in its Group Profile.

I can also just run ad hoc SQL queries to find other information. For example which user profiles use the job description QDFTJOBD:

  SELECT USER_NAME,JOBD,JOBDLIB 
    FROM QSYS2.USER_INFO
   WHERE STATUS = '*ENABLED'
     AND JOBD = 'QDFTJOBD'

Or which user profiles with the class of *USER do not have their capabilities limited:

  SELECT USER_NAME,USRCLS,LMTCPB
    FROM QSYS2.USER_INFO
   WHERE USRCLS = '*USER'
     AND LMTCPB = '*NO'

This is helping me extract the data for the auditors so much quicker, and in the process of writing this post I have found a number of users' who should have their capabilities limited.

 

You can learn more about on the IBM website:

 

This article was written for IBM i 7.2, and should work for earlier releases too.

19 comments:

  1. Thanks, I always look forward to your posts.

    ReplyDelete
  2. You say "USER_VIEW" a couple of times but I think you mean "USER_INFO".

    ReplyDelete
    Replies
    1. Thank you for catching that. Yes it is a mistake (hangs head in shame) it should read "USER_INFO", and I corrected the above post.

      Delete
  3. Hi Simon, good post as usual but there is a typo in the CREATE VIEW MYLIB/USR_ALLOBJ statement, QSYS2.GRPOUP_FILE_ENTRIES should be QSYS2.GROUP_PROFILE_ENTRIES.

    ReplyDelete
    Replies
    1. Thank you reporting that mistake (hangs head again). I have made the correction.

      Delete
  4. Now it says GRPOUP_PROFILE_ENTRIES! I guess it's been a long day :). It's still a great post though, I already used it to find our *ALLOBJ profiles.

    Cheers, Paul

    ReplyDelete
    Replies
    1. I am going to have to stop making these changes late at night. It should now (finally) be right.

      Delete
  5. Hello! I've been following your web site for some time now and finally got the courage
    to go ahead and give you a shout out from Lubbock Tx!

    Just wanted to say keep up the great job!

    ReplyDelete
    Replies
    1. Thank you very much.

      I would love to add this to the Testimonials page.

      What is your name?

      Delete
  6. IBM says "Only *USRPRF objects that the user has *OBJOPR and *READ authority to will be returned." so if I wanted to write an RPG procedure, let’s call it isMemberOfGroup(user:group), how would I best accomplish, that the procedure can read all user profiles? Compiling as QSECOFR with *owner wouldn’t be a good option, I guess.

    Thanks
    Markus

    ReplyDelete
  7. I get SQL7919 error in my RPG program which says "Data conversion required on FETCH or embedded SELECT. Host variable AUTHORIZATION_NAME requires conversion.". Is this due to it being a variable length field? If so, how do I fix that?

    ReplyDelete
  8. Without the SQL statement it is hard to give a definitive answer.

    It could be due to it being a VARCHAR column. Try fetching it into a VARCHAR field.

    ReplyDelete
  9. Hi, good article.
    is there in this table PASSWORD FIELD and is there any way to check user's password?
    Into one of my pgm I need check this:
    SELECT USER_NAME FROM QSYS2.USER_INFO where FIELD_PASSWORD = 'MYPASSWORD'
    thank you

    ReplyDelete
    Replies
    1. There is not a way to query passwords using these views. I would be alarmed if there was as, I could find out everyone's password using it.

      I believe that passwords are stored in some other structure, away from mere mortals like you and I.

      Delete
  10. thank you, im looking for this for audit extraction

    ReplyDelete
  11. Hello Simon,

    Is it as simple as selecting NOT IN QSYS2.USER_INFO file, if my intention is to check if a particular user exists or not ?

    OR

    Is there any other file which keeps track of all users deleted from i-Series

    thanks in advance :)

    ReplyDelete
  12. I would do it the "other way" around and check if the user profile is found in the QSYS2.USER_INFO using a SELECT statement.

    As far as I know there is no file of deleted user profiles.

    ReplyDelete
  13. Thank you very much!!! A very big help for me :)

    ReplyDelete

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.