Wednesday, July 20, 2022

Access System Directory information via SQL

system directory entries

I was asked for a list of all the enabled user profiles that are enrolled in the system directory on a partition. I know I could use the Display Directory Entry command, DSPDIRE, to create an outfile of the information:

DSPDIRE OUTPUT(*OUTFILE) OUTFILE(QTEMP/WOKFILE)
          DETAIL(*FULL) OUTFILFMT(*TYPE1)

Surely I would only want to retrieve the information for enabled profiles, or perhaps have a way to validate if a single user profile is enrolled in the system directory.

After some poking around I found a number of files in the library QUSRSYS that would give me the information I wanted. As the data is in files I can use SQL to get data and format the results in a way I would want, in real time.

I have searched in IBM's Documentation center and can find no mention of these files, therefore, it has been a case of comparing the data from these files with the WRKDIRE command that has allowed me to determine their functionality.

The first file I found useful was QAOKP01A. I found three fields here that would allow me to provide a list of the user ids and user profiles for the system directory entries. These fields are:

  1. WOS1DDEN:  User id, not profile
  2. WOS1DDGN:  System name
  3. WOS1USRP:  Actual user profile

A sample of what I found on the partition I use for writing these posts can be generated using the following SQL statement:

SELECT WOS1DDEN AS "User id",
       WOS1DDGN AS "System",
       WOS1USRP AS "User profile"
  FROM QUSRSYS.QAOKP01A
 ORDER BY 1,2

I have given the three columns in the results column headings as the file's field names are at best unclear.

The results look like:

User id   System    User profile
--------  --------  ------------
QBRMS     THISSYS   QBRMS
QDFTOWN   QDFTOWN   QDFTOWN
QDOC      QDOC      QDOC
QLPAUTO   QLPAUTO   QLPAUTO
QLPINSTL  QLPINSTL  QLPINSTALL
QNETSPLF  THISSYS   QNETSPLF
QPGMR     THISSYS   QPGMR
QRMTCAL   QRMTCAL   QRMTCAL
QSECOFR   QSECOFR   QSECOFR
QSYS      QSYS      QSYS

When I looked at other files I found that the WOS1DDEN and WOS1DDGN are the keys that links all of this data together.

The next useful file I found the file that contains the descriptions, QAOKP08A. In this file there are only three fields I am interested in:

  1. WOS8DDEN:  User id
  2. WOS8DDGN:  System name
  3. WOS8DESC:  Description

The statement I used to view the descriptions for the user ids was:

SELECT WOS8DDEN AS "User id",
       WOS8DDGN AS "System",
       WOS8DESC AS "Description"
  FROM QUSRSYS.QAOKP08A
 ORDER BY 1,2

Which gives me:

User id    System    User profile
--------   --------  ------------------------------------------
QBRMS     THISSYS    QBRMS
QDFTOWN   QDFTOWN    Default owner
QDOC      QDOC       Internal Document Owner
QLPAUTO   QLPAUTO    Licensed Program Automatic User
QLPINSTL  QLPINSTL   Licensed program install
QNETSPLF  THISSYS    Used for remote distribution of spool files
QPGMR     THISSYS    QPGMR
QRMTCAL   QRMTCAL    OV/400 Remote Calendar User
QSECOFR   QSECOFR    Security Officer
QSYS      QSYS       Internal System User Profile

Before I join these two files I need to know the user profile's status, whether it is enabled or disabled. The best place for me to get that information is from the USER_BASIC_INFO view. I only need to use two columns from this view:

  1. AUTHORIZATION_NAME:  User profile
  2. STATUS:  User profile status. Which will be either *ENABLED or *DISABLED

Now I can create one statement that will list all the user profiles, their descriptions from the system directory, user profile status, and user profile description:

01  SELECT A.WOS1USRP AS "User profile",
02         B.WOS8DESC AS "Description",
03         C.STATUS AS "Profile status",
04         C.TEXT_DESCRIPTION AS "Profile description"
05    FROM QUSRSYS.QAOKP01A A 
06    JOIN QUSRSYS.QAOKP08A B
07      ON (A.WOS1DDEN,A.WOS1DDGN) = (B.WOS8DDEN,B.WOS8DDGN) 
08    LEFT OUTER JOIN QSYS2.USER_INFO_BASIC C
09      ON A.WOS1USRP = C.AUTHORIZATION_NAME
10   ORDER BY 1

Line 1: The user profile from the file QAOKP01A.

Line 2: The system directory description from the file QAOKP08A.

Lines 3 and 4: User profile status and description from the USER_INFO_BASIC view.

Line 5 – 7: Defining the first two files and the type of join to combine the data, and on line 7 the fields to join the data with. This is an easier way to enter the join fields, rather than:

07      ON A.WOS1DDEN = B.WOS8DDEN 
07     AND A.WOS1DDGN = B.WOS8DDGN

Either way is acceptable.

Lines 8 and 9: I am using a left outer join to join the system directory data to the USER_INFO_BASIC. If there is not a matching user profile in USER_INFO_BASIC, the profile has been deleted but the system directory entry still exists, then the last two columns will be null.

Line 10: And I am sorting my results by the first column, WOS1USRP, user profile.

This is a sample of the results produced when I run the SQL statement:

User profile  Description     Profile status  Profile description
------------  --------------  --------------  -------------------
ACOX          Alexander Cox   *DISABLED       Cox, Alexander
CEVANS        Craig Evans     *ENABLED        Evans, Craig
DCOLLINS      Daniel Collins  <NULL>          <NULL>
DMITCHELL     David Mitchell  *ENABLED        Mitchell, David
RGUNN         Rob Gunn        *ENABLED        Gunn, Robert

As I said before Daniel Collin's user profile status and description are null as there is no longer a profile of DCOLLINS in this partition.

There was one more file I found that might be useful, QAOKPSRA. This file appears to contain the details of the system directory entry. If I wanted to see my system directory entry I would use the following command:

DSPDIRE USRID(SIMON THISSYS)

This shows all of my detailed information, over several screens:

                        Display Directory Entry Details

User ID/Address . . . . :   SIMON     THISSYS
Description . . . . . . :   Hutchinson, Simon
System name/Group . . . :   THISSYS
User profile  . . . . . :   SIMON
Network user ID . . . . :   SIMON    THISSYS

Name:
  Last  . . . . . . . . :   Simon
  First . . . . . . . . :   Hutchinson
  Middle  . . . . . . . :   D
  Preferred . . . . . . :   Simon
  Full  . . . . . . . . :   Simon Hutchinson

Department  . . . . . . :   IT
Job title . . . . . . . :   Author
Company . . . . . . . . :   RPGPGM.COM
Telephone numbers . . . :   512-000-0000


FAX telephone number  . :   N/A

Location  . . . . . . . :   Headquarters
Building  . . . . . . . :   Main
Office  . . . . . . . . :   Main

Mailing address . . . . :   123 Main Street
                            Austin, TX 78700

I only need three fields from the file QAOKPSRA:

  1. WOKSDEN:  User id
  2. WOKSFLD:  Field name
  3. WOKSVAL:  Field value

With the following statement, containing those three columns, I can get the same information as from the DSPDIRE command:

SELECT WOKSDEN AS "User id",
       WOKSFLD AS "Field",
       WOKSVAL AS "Value"
 FROM QUSRSYS.QAOKPSRA
WHERE WOKSDEN = 'SIMON'

The results are:

User id  Field      Value
-------  ---------  ------
U4142SH  USER       SIMON
U4142SH  USRID      SIMON
U4142SH  USRADDR    THISSYS
U4142SH  SYSNAME    *LCL
U4142SH  USRD       HUTCHISON,SIMON
U4142SH  FSTNAM     HUTCHINSON
U4142SH  PREFNAM    SIMON
U4142SH  MIDNAM     D
U4142SH  NETUSRID   SIMONTHISSYS
U4142SH  ALWSYNC    0
U4142SH  DLOOWN     0
U4142SH  LSTNAM     SIMON
U4142SH  FULNAM     SIMONHUTCHINSON
U4142SH  TITLE      AUTHOR
U4142SH  CMPNY      RPGPGM.COM
U4142SH  TELNBR1    512-000-0000
U4142SH  FAXTELNBR  N/A
U4142SH  LOC        HEADQUARTERS
U4142SH  BLDG       MAIN
U4142SH  OFC        MAIN
U4142SH  ADDR1      123MAINSTREET
U4142SH  ADDR2      AUSTIN,TX78700

I am not sure when I would want this level of detail, but if I ever do I now know where to find it.

 

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

7 comments:

  1. Hi, it's very interesting to make a regular review of these access rights. thanks for sharing

    ReplyDelete
  2. My results are showing in HEX on 7.3. Might be a CCSID issue.

    ReplyDelete
    Replies
    1. That what I would guess the issue is.

      Delete
    2. Select CAST(WOS1DDEN as char(100) ccsid 277) AS "User id" .... - our system is on 277 and I needed to do this.

      Delete
  3. Don't see what tables you where querying to get this information:

    USRD HUTCHISON,SIMON
    FSTNAM HUTCHINSON
    PREFNAM SIMON
    MIDNAM D
    NETUSRID SIMONTHISSYS
    LSTNAM SIMON
    FULNAM SIMONHUTCHINSON

    ReplyDelete
    Replies

    1. Simon HutchinsonJanuary 19, 2024 at 1:16 PM
      They are rows returned from the table QAOKPSRA. It could be you are not seeing the table as you are not authorized to it.

      Delete
    2. My bad. Got it working, thanks! :)

      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.