After about 7 years since my proposal, the only one of my customers with password level (QPWDLVL system value) still set to 0 has finally decided to change to level 2. In 2014, the operating system in use on this system was i5/OS V5R4, and to verify the password situation of the user profiles, as per IBM instructions, I would have used the DSPAUTUSR and PRTUSRPRF TYPE(*PWDLVL) commands. Now, that the operating system is IBM i V7R3, I decide to use the USER_INFO view to analyze in more detail the user profiles status before changing the QPWDLVL system value.
Target operating system: IBM i V7R3 (SF99730 level 20310 and SF99703 level 23).
Before proceeding, it is useful to remember the following about passwords.
At QPWDLVL 0/1 the system supports user profile passwords with a maximum length of 10 characters (the allowable characters are A-Z, 0-9 and characters $, @, # and underline) and it doesn’t keep track of the password case (it doesn’t know if the password is upper, lower, or mixed case). Note. On a system with primary language 2932 Italian, the allowable special characters are $, §, £ and underline.At QPWDLVL 2/3 the system supports user profile passwords with a maximum length of 128 characters. Upper and lower case characters are allowed. Passwords can consist of any character (but cannot begin with an asterisk and cannot be all blanks) and the password will be case sensitive.Before changing QPWDLVL from 0/1 to 2/3 it is necessary to check user profile passwords because some passwords may be not usable at the new level. These passwords must be updated before changing the system value. For user profiles with a password usable at QPWDLVL 2/3, no intervention is required.When you change QPWDLVL from 0/1 to 2/3 and IPL, the system will store both an all upper case and an all lower case version of the password. After the IPL, when the user signs on, they will need to enter either an all upper or an all lower case version of the password. A mixed case password is not allowed because case was not recorded at levels 0/1 and the system only has single-case passwords.After the system is at QPWDLVL 2/3, if the password is changed to a mixed case, the system will record the new mixed-case version of the password because case now matters.
Let’s start with the analysis phase.
Check the systems that communicate with the IBM i system and the communication services used.
If your system is communicating with other IBM i platforms in a network, check the operating system release and password level of those systems and which communication services are in use.
If those systems are running an operating system release less than V5R1M0, QPWDLVL 2/3 cannot be used.
If those systems are running with a QPWDLVL value of 0 or 1, it depends on the communication services used.
Communication services using implicit authentication based on user and password require that user profile and password must match between the source and target IBM i systems. This could be a problem when systems have different password levels (the system with a higher password level should follow the password rules of the system with a lower password level). In addition, some of those services, e.g. QFileSvr.400, also require that password level must match between IBM i systems.
On the other hand, communication services using explicit authentication, e.g. FTP, should have no problem.
If you are using IBM i Support for Windows Network Neighborhood (IBM i NetServer), you can upgrade to password level 3 only if on your farm there are no Windows 95/98/ME clients using that service, otherwise, you can only change to password level 2. This is because at level 3 the IBM i NetServer LAN manager passwords for Windows 95/98/ME clients are removed from the system.
Note. The LAN manager passwords have been disabled by Windows since Vista so removing them will not affect current versions of Windows.
Check QPWDVLDPGM system value on your IBM i system.
QPWDVLDPGM system value must specify either *REGFAC or *NONE before the system allows QPWDLVL to be changed to 2/3 (otherwise, message CPD2294 is issued). Therefore, if you use a password validation program, you might want to write a new one that can be registered for the QIBM_QSY_VLD_PASSWRD exit point, format VLDP0100, by using the ADDEXITPGM command.
Locate all IBM i user profiles which do not have a password usable at password level 2/3.
Since the operating system in use is IBM i V7R3, instead of using the DSPAUTUSR or PRTUSRPRF TYPE(*PWDLVL) commands, as suggested by IBM, I prefer to use the USER_INFO view so that SQL can do my job. The selection criteria are the same as suggested by IBM for the two previous commands, that is PASSWORD_LEVEL_0_1 (or PWD_0_1) equal to YES and PASSWORD_LEVEL_2_3 (or PWD_2_3) equal to NO:
SELECT USER_NAME
FROM QSYS2.USER_INFO
WHERE PWD_0_1 = ‘YES’ AND PWD_2_3 = ‘NO’
Note. Instead of USER_INFO view, you can use the output file of the DSPUSRPRF USRPRF(*ALL) TYPE(*BASIC) OUTPUT(*OUTFILE) OUTFILE(QTEMP/DSPUSRPRF) command to make the same search:
SELECT UPUPRF
FROM QTEMP/DSPUSRPRF
WHERE UPENPW = ‘Y’ AND UPENPH = ‘N’
The USER_INFO view contains a lot of useful information for further analysis of the selected user profiles. For example, you can investigate whether a user profile is still being used or not:
SELECT USER_NAME, STATUS, PRVSIGNON, LASTUSED
FROM QSYS2.USER_INFO
WHERE PWD_0_1 = ‘YES’ AND PWD_2_3 = ‘NO’
Note. In addition to the PREVIOUS_SIGNON (or PRVSIGNON) field, I also insert the LAST_USED_TIMESTAMP (or LASTUSED) field to take into account any user profiles used for connections other than 5250 emulations (ODBC, JDBC, FTP, etc.).
In this way, I can identify user profiles with passwords not usable at password level 2/3 that are no longer used. These user profiles could be removed from the system, instead of changing their passwords.
Now I complete our SELECT by adding other fields that help to clarify the situation of the selected user profiles as, for example, GROUP_MEMBER_INDICATOR (o GRPMBR), PASSWORD_EXPIRATION_INTERVAL (o PWDEXPITV), PASSWORD_CHANGE_DATE (o PWDCHGDAT), LOCAL_PASSWORD_MANAGEMENT (o LCLPWDMGT), TEXT_DESCRIPTION (o TEXT):
SELECT USER_NAME, GRPMBR, STATUS, LASTUSED, CASE WHEN PWDEXPITV = -1 THEN ‘NOMAX’ WHEN PWDEXPITV = 0 THEN ‘SYSVAL’ ELSE CHAR(PWDEXPITV) END AS PWDEXPITV, PRVSIGNON, PWDCHGDAT, LCLPWDMGT, TEXT
FROM QSYS2.USER_INFO
WHERE PWD_0_1 = ‘YES’ AND PWD_2_3 = ‘NO’
I conclude the analysis of the user profiles trying to write a SQL statement that suggests what to do with user profiles with passwords not usable at level 2/3, based on the last use date and last password change date, taking this “algorithm” as a starting point:
IF PWD_0_1 = ‘YES’ AND PWD_2_3 = ‘NO’
IF LASTUSED > 365 days THEN /* user profile not used in the last year */
DLTUSRPRF /* remove user profile */
ELSE /* user profile perhaps still in use */
IF PRVSIGNON > 365 days THEN /* previous sign-on older than 1 year */
CHGUSRPRF /* password change by admin */
ELSE /* previous sign on in the last year */
CHGPWD /* password change by user */
Here is the SQL statement:
SELECT USER_NAME, GRPMBR, STATUS, LASTUSED, CASE WHEN PWDEXPITV = -1 THEN ‘*NOMAX’ WHEN PWDEXPITV = 0 THEN ‘*SYSVAL’ ELSE CHAR(PWDEXPITV) END AS PWDEXPITV, PRVSIGNON, PWDCHGDAT, LCLPWDMGT, TEXT, CASE WHEN DAYS(CURRENT_DATE) – DAYS(DATE(COALESCE(LASTUSED, ‘2001-01-01’))) > 365 THEN ‘DLTUSRPRF USRPRF(‘ CONCAT USER_NAME CONCAT ‘)’ ELSE CASE WHEN DAYS(CURRENT_DATE) – DAYS(DATE(COALESCE(PRVSIGNON, ‘2001-01-01’))) > 365 THEN ‘CHGUSRPRF USRPRF(‘ CONCAT USER_NAME CONCAT ‘) PASSWORD(…)’ ELSE ‘CHGPWD’ END END AS SUGGESTED_ACTION
FROM QSYS2.USER_INFO
WHERE PWD_0_1 = ‘YES’ AND PWD_2_3 = ‘NO’
Note. For LASTUSED and PRVSIGNON fields that may have no values (or null values) I use the COALESCE SQL function to set a default date (for example, January 1, 2001) in case of null value returned.
Now that the analysis phase has been completed, I can start with the implementation phase.
First of all, I need to notify users about the actions they need to perform on their user profiles (change password) and verify the correct execution using USER_INFO view.
Before changing the QPWDLVL system value, put the system in restricted state:
ENDSBS SBS(*ALL) OPTION(*IMMED) ENDSBSOPT(*NOJOBLOG)
Save security data to save file so you will be able to reset the passwords for all user profiles, should you need to return to a lower password level:
CRTSAVF FILE(library/SAVSECDTA)
SAVSECDTA DEV(*SAVF) SAVF(library/SAVSECDTA) DTACPR(*MEDIUM) OUTPUT(*PRINT)
Note. In addition to saving security data (SAVSECDTA), my advice is to also save user profile attributes to a database file with the command:
DSPUSRPRF USRPRF(*ALL) TYPE(*BASIC) OUTPUT(*OUTFILE) OUTFILE(library/DSPUSRPRF)
Work on user profiles according to the suggestions elaborated in the analysis phase (change password or remove).
Change QPWDLVL system value to 2 (or 3):
CHGSYSVAL SYSVAL(QPWDLVL) VALUE(2)
Note 1. QPWDLVL 2 is viewed as a compatibility level. This level allows for a move back to QPWDLVL 0 or 1. When QPWDLVL is 3, all user profile passwords that are used at QPWDLVL 0 and 1 are removed from the system. Therefore, changing from QPWDLVL 3 back to QPWDLVL 0 or 1 requires a change to QPWDLVL 2 before going to 0 or 1 (CPD2291 message is sent when you try to change from password level 3 to 0 or 1 ) because QPWDLVL 2 allows for the creation of user profile passwords that can be used at QPWDLVL 0 or 1 (as long as the password created on QPWDLVL 2 or 3 meets the length and syntax requirements of a password valid on QPWDLVL 0 or 1).
Note 2. Password level 3 has more restrictions than level 2, although some of these, such as incompatibility with OS/400 V5R1 systems (also valid for QPWDLVL 2) and with the IBM i Support for Windows Network Neighborhood product (or IBM i NetServer, see above), is now outdated due to the obsolescence of the involved operating systems.
Changing the system value QPWDLVL will take effect the next time the system is rebooted. The Display Security Attribute command is used to verify the current and pending values of password level:
DSPSECA
…
Password level . . . . . . . . . . . . . . : 0
Pending password level . . . . . . . . . : 2
…
Modify the display file of the QINTER subsystem sign-on screen to allow passwords case mixed and longer than 10 characters:
CHGSBSD SBSD(QSYS/QINTER) SGNDSPF(QSYS/QDSIGNON2)
Note. Here is the IBM default sign-on screen for QPWDLVL 2/3 is set. If you have your own, remember that the password field must be128 characters long (or at least equal to the length specified in QPWDMAXLEN system value or set using *MAXLENnnn in QPWDRULES system value) and to permit the user to type lowercase a through z:
A 01 9 17’Password . . . . . . . . . . . .’
A 01 PASSWRD 128A I 9 53CHECK(LC)
A DSPATR(ND)
Set the system start to restricted state:
CHGIPLA KEYLCKPOS(*NORMAL) STRRSTD(*YES)
Reboot the system:
PWRDWNSYS OPTION(*IMMED) RESTART(*YES *IPLA) IPLSRC(B) ENDSBSOPT(*NOJOBLOG) CONFIRM(*NO)
After the system starting, check the current value of the password level:
DSPSECA
…
Password level . . . . . . . . . . . . . . : 2
…
Put the system in normal state:
STRSBS SBSD(QCTL)
Check that everything works fine then proceed with password policies changes if needed.
Note. It would be better to avoid changing password system values, such as QPWDMINLEN, QPWDMAXLEN, or QPWDRULES, until you have tested the new QPWDLVL value. This makes easier to transition back to previous QPWDLVL value, if necessary. For example, if passwords greater than 10 characters are specified, the password level 0 and 1 password is cleared. Also, if passwords contain special characters or do not follow the composition rules for simple object names (excluding case sensitivity), the password level 0 and 1 password is cleared. In such cases, a user profile will not be able to sign on if the system is returned to password level 0 or 1.
Finally, here is the audit phase.
You can use the audit journal to check for incorrect passwords.
To extract information about incorrect passwords, you can use the DSPJRN command:
CRTDUPOBJ OBJ(QASYPWJ5) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(QTEMP) NEWOBJ(FILE)
DSPJRN JRN(QAUDJRN) RCVRNG(*CURCHAIN) FROMTIME(ddmmyyyy hhmm) JRNCDE(*ALL) ENTTYP(PW) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE5) OUTFILE(QTEMP/FILE)
STRSQL
SELECT PWTSTP AS TIMESTAMP, PWNBR CONCAT ‘/’ CONCAT RTRIM(PWUSER) CONCAT ‘/’ CONCAT RTRIM(PWJOB) AS JOB_NAME, PWTYPE AS ERROR_TYPE, PWUSRN AS USER_NAME, PWDEVN AS DEVICE_NAME, PWRADR AS REMOTE_IPA
FROM QTEMP/FILE
WHERE PWTYPE NOT IN (‘D’, ‘E’, ‘X’, ‘Y’, ‘Z’)
Note. Entries related to Service Tool events are ignored (Security auditing journal entries):
Action or object
auditing valueJournal entry typeModel database outfileDetailed entryDescription*AUTFAILPWQASYPWJE/J4/J5AAPPC bind failureCCHKPWD failureDAn incorrect service tool user ID was enteredEAn incorrect service tool user ID password was enteredPAn incorrect password was enteredQAttempted sign-on (user authentication) failed because user profile was disabledRAttempted sign-on (user authentication) failed because password was expiredSSQL decrypt a password that was not validUUser name not validXService tools user is disabledYService tools user not validZService tools password not valid
Alternatively, since you are working on an IBM i V7R3 operating system, you can use the user defined table function DISPLAY_JOURNAL:
SELECT
entry_timestamp AS TIMESTAMP,
job_number CONCAT ‘/’ CONCAT RTRIM(job_user) CONCAT ‘/’ CONCAT RTRIM(job_name) AS JOB_NAME,
remote_address AS REMOTE_IPA,
RTRIM(CAST(CAST(SUBSTRING(entry_data, 2, 10) AS VARCHAR(10) FOR BIT DATA) AS VARCHAR(10) CCSID 37)) AS USER_NAME
FROM TABLE(qsys2.display_journal(journal_library => ‘QSYS’,
journal_name => ‘QAUDJRN’,
starting_receiver_name => ‘*CURAVLCHN’,
journal_entry_types => ‘PW’,
starting_timestamp => CURRENT TIMESTAMP – x HOURS))
You need to remind customers that from now on:
References
