Admin Alert: Building a Better Experimental Automatic Deletion Technique
May 3, 2006 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.
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:
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.