Last Updated on 4 September 2021 by Roberto De Pedrini
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
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.
QPWDLVL0/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.
QPWDLVL2/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
QPWDLVLfrom 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
QPWDLVL2/3, no intervention is required.
- When you change
QPWDLVLfrom 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
QPWDLVL2/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.
QPWDVLDPGM system value on your IBM i system.
QPWDVLDPGM system value must specify either
*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
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
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
PWD_0_1) equal to YES and
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'
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
PRVSIGNON) field, I also insert the
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,
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'
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
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:
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).
QPWDLVL system value to 2 (or 3):
CHGSYSVAL SYSVAL(QPWDLVL) VALUE(2)
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:
... 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
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:
... Password level . . . . . . . . . . . . . . : 2 ...
Put the system in normal state:
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
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
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)
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|
|Journal entry type||Model database outfile||Detailed entry||Description|
|*AUTFAIL||PW||QASYPWJE/J4/J5||A||APPC bind failure|
|D||An incorrect service tool user ID was entered|
|E||An incorrect service tool user ID password was entered|
|P||An incorrect password was entered|
|Q||Attempted sign-on (user authentication) failed because user profile was disabled|
|R||Attempted sign-on (user authentication) failed because password was expired|
|S||SQL decrypt a password that was not valid|
|U||User name not valid|
|X||Service tools user is disabled|
|Y||Service tools user not valid|
|Z||Service tools password not valid|
Alternatively, since you are working on an IBM i V7R3 operating system, you can use the user defined table function
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:
- when the user signs on, they will need to enter either an all upper or an all lower case version of the password (mixed case password is not allowed because case was not recorded at levels 0/1 and the system only has single-case passwords);
- when the password is changed to a mixed case, the system will record the new mixed-case version of the password because case now matters;
- it is possible to use any character (including special characters, unless otherwise specified in
QPWDLMTCHRsystem value, if used).