Admin Alert: Building a Better Experimental Automatic Deletion Technique

Published: May 3, 2006

by Joe Hertvik

Recently, I posted an experimental technique for the automatic deletion of inactive profiles. I cautioned my readers, however, to approach this task carefully, lest they accidentally delete needed profiles. I also invited any willing reader to fill in the gaps in the technique, and to point out where it needed improvement.

Several readers responded, uncovering a few nasty trap doors in what I had created. Here's what they had to say and how I think the technique can be improved to meet their concerns.

The technique itself is too long to reprint here, so be sure to read the original article to get the full gist of what I was driving at. To summarize, here are the technique's main points.

1. I run the Save Security Data command (SAVSECDTA) on a weekly basis to back up security data, including user profiles, private authorities, and configuration objects. This allows me to restore user profiles in case of accidental deletion.
2. I set up and maintain an Active Profile List, so that the ANZPFRACT command mentioned in point 3 does not automatically disable needed user profiles that never sign on and so that it does not disable group profile user IDs.
3. I set up the Analyze Profile Activity command (ANZPRFACT) to automatically disable any profiles that a) are not on the Active Profile List; and b) have not signed on to the system in x number of days. This command runs every day.
4. I set up and run a nightly profile deletion routine that automatically deletes disabled user profiles that have not signed on to the system in x number of days.

For the purposes of our articles, this technique was set up to automatically delete user profiles that have been inactive for 120 days or more. The entire technique can be found by clicking here. After you read the original article, check out these reader comments about the flaws in this technique and some possible solutions for getting around these problems.

Readers Who Just Say "NO!!!!"

A few readers wrote in to tell me that the biggest flaw in the technique was that--like Frankenstein's monster--it should never have been brought to life. Or as reader R. Harman put it:

"I would never trust an automated process to delete user profiles. . . . A reasonable, and common, audit requirement would be to review unused profiles and possibly disable/delete them. To automatically delete them is a recipe for disaster. In our case, we automatically generate a monthly report showing profile activity and, if a profile has gone unused in 90 days, we investigate. If the profile is deemed "dead", we disable it and comment its text description in case we need to clone it for another user in the near future. Periodically, old disabled profiles are deleted--manually."

Harman has a point here, and I was careful to present this technique as experimental and full of pitfalls, so as not to unnecessarily imperil user profiles. However, the nice thing about what I presented is that, with a little modification, system administrators can use it exactly for what Harman recommends. With a little bit of effort, you can activate steps one through three above (to automatically identify user candidates who have not signed on in a set number of days) and then modify step four to create a report of disabled users who have not signed on for more than the set number of days, rather than to automatically delete the inactive profiles. That report could then be reviewed for deletion candidates, and deletion would occur manually.

This hybrid approach would automate the identification features of the technique, while maintaining manual control over the actual user profile deletion process. So, there could be some value in what I presented, even if the technique is never used for its original purpose.

Solving the Fatal Assumption

And then there were the readers who pointed out the one fatal flaw in my technique, which totally messes everything up. The problem lies in the following piece of critical code that is used in step 4, when I actually delete a user profile that hasn't been used in more than 120 days.

/* Only delete non-group user profiles that are disabled + 
and that have not signed on for more than 120 days */
IF         COND(&UPGID *NE 0) THEN(DO)              
      IF         COND(&UPPSOD *LE &DATE120) THEN(DLTUSRPRF +    
                          USRPRF(&UPUPRF) OWNOBJOPT(*CHGOWN QDFTOWN))     
ENDDO          

In this case, the code checks to make sure that the user profile being examined isn't a group ID (where the &UPGID field is not equal to 0) and that the Previous Sign-On date field (&UPPSOD) is more than 119 days before the current date (by comparing it to the calculated field, &DATE120). If it meets both conditions, the user profile is deleted.

The problem is that, as reader Krister Karlsson pointed out, most iSeries TCP/IP servers do not update the Previous Sign-On date field in a user profile. IBM only updates the profile's UPPSOD field when the user profile signs on as a 5250 green-screen terminal user. For every other type of system access using that user profile--including ODBC, JDBC, FTP, and other type of client/server access--the UPPSOD field is not updated.

Because of this, if a user FTPs to your i5 or iSeries box each day and never signs on to the green-screen, the UPPSOD field will contain blanks. This means that the automatic deletion technique will delete the FTP user profile when it passes the inactivity threshold, because, according to this code, a blank UPPSOD field would definitely be considered more than 120 days old.

But there is a solution that readers Mike Flatt and Jim Rothwell pointed out, where both of them emailed to remind me that while the Display User Profile command (DSPUSRPRF) does not show previous sign-on dates for non-5250 users, the Display Object Description command (DSPOBJD) does show a last used date, as well as a creation date, for user profiles. And DSPOBJD's last used date is updated whenever the user profile is accessed on the system by any means, even if it's accessed for group profile authority checking. For more information on how the last used date differs from the previous sign-on date, see this article on safely deleting user profiles.

Given the knowledge that the DSPOBJD command allows me to confidently determine if a user profile has been used or accessed in the last x days, I decided that I could modify the date comparison routine in the previous program to get around this fatal flaw. Here are the code corrections I would make to solve this problem.

First, in my automatic deletion program from the prior column, I would add a Y/N deletion flag variable that will be used to tell the program if the user profile is eligible for automatic deletion. This variable definition would look like this:

DCL        VAR(&DELFLAG) TYPE(*CHAR) LEN(1) VALUE('N ')

Next, I would modify the comparison code listed above to send the user profile name being considered, the deletion flag variable, and the inactivity date (the &DATE120 variable, which is calculated to be 120 days before the current date) to another program for processing. The other program will perform user profile date checking against the inactivity date and pass back a 'Y' in the &DELFLAG variable, if it is okay to delete the user profile because it hasn't been used in x number of days. Here's the new code to call a date comparison program, CHECKUSER, to determine if the profile is eligible for deletion, and to automatically delete the user profile, if it is.

/* Only consider non-group profiles for deletion. 
If the date checking program passes + 
back a 'Y' in the delete flag variable, delete the user profile */ 
IF         COND(&UPGID *NE 0) THEN(DO)        
     CHGVAR     VAR(&DELFLAG) VALUE('N')                     
     CALL  PGM(CHECKUSER) PARM(&UPUPRF &DATE120 &DELFLAG)
      IF         COND(&DELFLAG *EQ 'Y') THEN(DLTUSRPRF +    
                          USRPRF(&UPUPRF) OWNOBJOPT(*CHGOWN QDFTOWN))     
ENDDO          

This code replaces the IF statement listed at the top of this section.

Finally, I would create the following CL program, called CHECKUSER, which determines whether or not a user profile should be deleted due to inactivity.

PGM        PARM(&UPUPRF &DATE120 &DELFLAG)     

DCL        VAR(&UPUPRF) TYPE(*CHAR) LEN(10)              
DCL        VAR(&DATE120) TYPE(*CHAR) LEN(6)              
DCL        VAR(&DELFLAG) TYPE(*CHAR) LEN(1)              
DCL        VAR(&COMPDATE) TYPE(*CHAR) LEN(6) /* +        
             Comparison date */                          
                                                         
DCLF       FILE(QADSPOBJ)               

/* RETRIEVE AND STORE THE USER PROFILE'S OBJECT +
INFORMATION INTO THE QADSPOBJ FILE IN QTEMP */
DSPOBJD    OBJ(&UPUPRF) OBJTYPE(*USRPRF) + 
DETAIL(*FULL) OUTPUT(*OUTFILE) +
OUTFILE(QTEMP/QADSPOBJ)    
MONMSG     MSGID(CPF0000) EXEC(GOTO + 
CMDLBL(ENDPGMLAST))  

OVRDBF     FILE(QADSPOBJ) TOFILE(QTEMP/QADSPOBJ) +       
             MBR(*FIRST) SECURE(*YES) 
                                                         
/* READ THE USER PROFILE'S OBJECT INFORMATION */ 
RCVF                                                     
MONMSG     MSGID(CPF0864) EXEC(GOTO +
 CMDLBL(ENDPGMLAST))           

/* IF THERE IS A LAST USED DATE, COMPARE IT TO THE INACTIVITY DATE */ 
IF         COND(&ODUCEN *NE ' ') THEN(DO)                         

/* IF THE PROFILE WAS LAST USED IN THE 20TH CENTURY + 
(&ODUCEN = 0) MARK IT FOR DELETION */ 
IF   COND(&ODUCEN *EQ '0') THEN(DO)      
CHGVAR  VAR(&DELFLAG) VALUE('Y')       
GOTO    CMDLBL(ENDPGM)                              
ENDDO                                                             
                                                                  
/* IF THE LAST USED DATE IS EARLIER THAN THE + 
INACTIVITY DATE, MARK THE PROFILE FOR DELETION */

CHGVAR VAR(&COMPDATE) VALUE((%SST(&ODUDAT 5 2)) + 
  *CAT (%SST(&ODUDAT 1 4))) 
/* Convert the last used date from mmddyy +
format to yymmdd format for comparison */   

IF         COND(&COMPDATE *LE &DATE120) THEN(DO)
IF         COND(&COMPDATE *NE *BLANKS) THEN(DO)        
CHGVAR     VAR(&DELFLAG) VALUE('Y')
ENDDO
ENDDO
ENDDO
           
/* IF THE USER PROFILE HAS NEVER BEEN USED (BLANK + 
LAST USED CENTURY), COMPARE THE INACTIVITY DATE TO +
THE CREATION DATE */
ELSE       CMD(DO)
/* IF THE PROFILE WAS CREATED IN THE 20TH CENTURY + 
MARK IT FOR DELETION */   
IF         COND(&ODCCEN *EQ '0') THEN(DO)   
CHGVAR     VAR(&DELFLAG) VALUE('Y')       
GOTO       CMDLBL(ENDPGM) 
ENDDO                    
             
             
/* IF THE LAST USED DATE IS EARLIER THAN THE + 
INACTIVITY DATE, MARK THE PROFILE FOR DELETION */
CHGVAR     VAR(&COMPDATE) VALUE((%SST(&ODCDAT 5 2)) +
*CAT (%SST(&ODCDAT 1 4))) /* Convert the + 
last used date from mmddyy format to yymmdd +
format for comparison */
IF         COND(&COMPDATE *LE &DATE120) THEN(DO)
IF         COND(&COMPDATE *NE *BLANKS) THEN(DO)
CHGVAR     VAR(&DELFLAG) VALUE('Y')          
ENDDO
ENDDO
           
ENDDO
           
 ENDPGM:     DLTOVR     FILE(QADSPOBJ)
                             
 ENDPGMLAST: ENDPGM                                  

And the program code works this way:

1. The program receives the user name to check, the inactivity date, and a flag that is set if the user profile is eligible for deletion.
2. The program takes the passed-in user profile name and uses the Display Object Description command to retrieve user profile information into the QADSPOBJ file in the QTEMP library. It then overrides all calls to QADSPOBJ to that file.
3. The program checks to see if there is a last used date for the user (field &ODUCEN is not blank), which indicates whether or not the user has ever done any work with this profile. If the user has never used the profile, the program moves on to the code described in point 8.
4. If the last used date is in the 20th century, the program sets the &DELFLAG variable to 'Y', and returns control back to the calling program.
5. The program reformats the last used date to YYMMDD format because the last used date contained in the user profile object description carries the date in MMDDYY format.
6. If the reformatted last used date occurred before the inactivity date, the program sets the &DELFLAG variable to 'Y' and returns.
7. If the last used date falls after the inactivity date, the &DELFLAG variable is not set.
8. If there is no last used date for the user (the user profile has never been used for any kind of work), the program performs the exact same processing listed in points 3 through 8, only it performs that processing on the creation date for the user profile, rather than the last used date. If the creation date falls before the inactivity date, the &DELFLAG is set to 'Y', and the user profile is considered eligible for deletion.

In my opinion, this makes for a better automatic deletion technique because it user the last used date to determine deletion eligibility, not the last sign-on date for a 5250 green-screen sign-on, as the previous column's version did.

However, my earlier warning about using an automatic deletion technique still holds true. You should only put this code into production after extensive testing and verification, as any mistakes in the code could result in the accidental mass deletion of many user profiles on your system.

RELATED STORIES

An Experimental Technique for Automatically Deleting User Profiles

More on Safely Deleting User Profiles