Getting information about User Profiles using SQL

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:

• SQL View USER_INFO
• SQL View GROUP_PROFILE_ENTRIES

 

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