Changing password level (QPWDLVL system value) from 0/1 to 2/3 with the help of USER_INFO view

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 value
Journal 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:

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 QPWDLMTCHR system value, if used).

References

Password Level (QPWDLVL)

Considerations for changing QPWDLVL from 0 or 1 to 2

Considerations for changing QPWDLVL from 0 to 3

IBM i and OpenSource: cannot retrieve repository metadata (repond.xml)

IBM i open source environment installation:

From version 1.1.8.0 of the Access Client Solutions, the Open source package management is available which allows you to manage the installation and update of the open source packages available for IBM i from 7.2 onwards. From the ACS main window, from the Tools menu choose the Open Source Package Manager item.

The first time you try to open this item on an IBM i server, you are prompted to install the open source environment and you must continue by clicking on YES YES.

Only then is the package management window shown, divided into 3 tabs: packages installed, packages installed with available updates, packages available on the IBM i repository that can be installed.

This feature requires the service to be active SSHD -> Port 22.
If it is not started on IBM i you can run the command STRTCPSVR SERVER (* SSHD).

If the installation finishes successfully, you should be able to upgrade, and install the open source packages directly from the ACS GUI, but unfortunately this is not always the case.

Case study:

In my case for example, after installing the open source environment on one of my IBM i servers, I was unable in any way to install update any package via the ACS GUI and everything ended with the following error: cannot retrieve repository metadata (repond.xml)

At this point, I decide to google and then interface with the IBM technical support who recommends me to try to change the baseUrl of the IBM i repository.

After opening the /QOpenSys/etc/yum/repos.d/ibm.repo in edit mode, we can change the baseUrl for the repositoryThen, from a 5250 session, type WRKLNK ‘/QOpenSys/etc/yum/repos.d/ibm.repo’ and change the baseUrl in one of two ways:baseurl = http://public.dhe.ibm.com/software/ibmi/products/pase/rpms/repobaseurl = ftp://public.dhe.ibm.com/software/ibmi/products/pase/rpms/repo

Press F3 twice to save and test the connection to the Open Source Package Manager again.

Once this is done, the problem persists and I try to go deeper and deeper, verifying that the IBM i server did not have any outgoing limits behind firewalls or the like. The counter-proof, I got it, when I decide to activate SSH tunneling from ACS, but in vain.

After a lot of arguing with my GUI, I decide to open from a 5250 session, a QP2TERM session and in order I execute the following commands:

yum update yum clean metadata yum install python3 -> (I install any package to verify reachability and its download from the repository)

Everything starts to take shape, and I realize that it is a problem related only to the GUI, because the packages are installed correctly and I can use them without any problem. So, the IBM i server, has no output limit and in this regard, not happy, I try again at this point to run the installation of a package from the GUI, getting a much more detailed error message that deserves to be explored:

ERROR: getaddrinfo () thread failed to start

Below is the explanation that I was able to find:

IBM I PASE provides a version of cUrl that speeds up common tasks such as DNS lookups by using additional threads. When threading support is not available, however, cUrl fails -> The getaddrinfo () thread was not started.

So … to be able to install packages from the GUI without problems, I had to set threading support before calling a QSHELL or QP2TERM session!

ADDENVVAR ENVVAR (QIBM_MULTI_THREADED) VALUE (Y) REPLACE (* YES)

After that, I tried to download an open source package from the ACS GUI again with success!

References:

For more information, the official IBM documentation is available at the following link with all the parameters: https://www.ibm.com/support/pages/open-source-package-manager-acs-updatechange-repository-configuration-https-http-ftp

ncdu – IFS Disk Space Analyzer

If we want to check the disk space occupied by IFS files and directories, we have the possibility to install a great utility, NCDU, which allows us to quickly navigate inside IFS (but also inside QSYS.LIB) in search of the largest directories and therefore of the bigger files

The operation is very simple … just install ncdu with yum from an SSH session (putty or ACS SSH Terminal)

yum install ncdu

then go to the main directory ‘/’, always in the SSH environment and type the following command (where I exclude the directories that I do not want to scan with ncdu)

ncdu -x / –exclude /QSYS.LIB –exclude /QOpenSys –exclude /QIBM –exclude /QNTC –exclude /mount

The result is a screen like the one below where you can immediately see the largest directories at a glance and go down with the arrows inside them to navigate in the sub-directories up to the files, always in order of size:

Warning: from ncdu it is also possible to delete files or folders with the ‘d’ option … pay attention …

For example, I discovered very large dump files in the / home directories of developer users … and the ‘d’ option was very useful!


Roberto De Pedrini
Faq400.com

Verified by MonsterInsights