Be it auditors, the recent NIS2 Directive , or just common sense, stepping away from IBM i user profiles with never expiring passwords has always been the better choice.

Since IBM i has been around for quite some time, a lot of the security options have gotten a bit long in the tooth.

For sure, the password length limited to 10 characters is one of them. What to do with that is maybe material for another article, for now I would like to focus on the Password Expiration Interval (QPWDEXPITV).

In order to get rid of IBM i user passwords and worrying about this value, implementing Single Signon using Windows AD and Kerberos is good candidate.

Unfortunately, this will only work for users being logged into the Windows Domain. The truth is that nearly every IBM i server has user profiles used to connect to the IBM i to exchange data.

In those cases, the Password expiration warning interval is not of much help, if connection is through an automated process.

Nonetheless, forcing those user profiles to change their password is a very good idea, but how to keep track of this?

Although something like the gradual database rollover time does on other platforms, in the end, at some stage, the password needs to be changed nonetheless.

The only alternative to that, is to change the user profile and set the Password expiration interval to the value *NOMAX.

Maybe this is something we could get away with in the past, but today, let us face it, it is no longer good practice and acceptable.

With that in mind, a mechanism is needed to be aware of any upcoming password that need to be changed. For this the SQL services ride to the rescue.

The first step is to be able to detect which passwords are about to be expired:

SELECT authorization_name, USER_INFO.TEXT_DESCRIPTION, DATE_PASSWORD_EXPIRES, 
        PREVIOUS_SIGNON, STATUS, LAST_USED_TIMESTAMP
    FROM QSYS2.USER_INFO AS USER_INFO
    WHERE (DATE(DATE_PASSWORD_EXPIRES)) >= CURRENT DATE - (30 DAYS) AND 
          SUBSTRING(AUTHORIZATION_NAME, 1, 1) <> 'Q' AND STATUS = '*ENABLED'
    ORDER BY DATE_PASSWORD_EXPIRES;

Resulting in:

The second step is to create a spreadsheet which can used as an email attachment:

VALUES 
        SYSTOOLS.GENERATE_SPREADSHEET(
            PATH_NAME => '/tmp/User_with_soon_expiring_password',
            SPREADSHEET_QUERY =>
    'SELECT authorization_name, DATE_PASSWORD_EXPIRES, PREVIOUS_SIGNON, STATUS, LAST_USED_TIMESTAMP
    FROM QSYS2.USER_INFO AS USER_INFO
    WHERE (DATE(DATE_PASSWORD_EXPIRES)) >= CURRENT DATE - (30 DAYS) AND SUBSTRING(
              AUTHORIZATION_NAME, 1, 1) <> ''Q'' AND STATUS = ''*ENABLED''
    ORDER BY DATE_PASSWORD_EXPIRES', COLUMN_HEADINGS => 'COLUMN');

Resulting in:

This SYSTOOLS.GENERATE_SPREADSHEET SQL service has been enhanced in IBM i 7.4 Technology Refresh 11 and IBM i 7.5 Technology Refresh 4.

It is now able to generate a spreadsheet with proportional width columns for xls and xlsx formats.

To make use of this enhancement the SQL statement needs to be modified, as CSV is the default:

VALUES 
        SYSTOOLS.GENERATE_SPREADSHEET(
            PATH_NAME => '/tmp/User_with_soon_expiring_password',
            spreadsheet_type => 'xlsx', -- lower case required
            SPREADSHEET_QUERY =>
    'SELECT authorization_name, DATE_PASSWORD_EXPIRES, PREVIOUS_SIGNON, STATUS, LAST_USED_TIMESTAMP
    FROM QSYS2.USER_INFO AS USER_INFO
    WHERE (DATE(DATE_PASSWORD_EXPIRES)) >= CURRENT DATE - (30 DAYS) AND SUBSTRING(
              AUTHORIZATION_NAME, 1, 1) <> ''Q'' AND STATUS = ''*ENABLED''
    ORDER BY DATE_PASSWORD_EXPIRES', COLUMN_HEADINGS => 'COLUMN');

The final step in order to complete this process is to generate an email using the spreadsheet:

VALUES 
        SYSTOOLS.SEND_EMAIL(
            TO_EMAIL => 'r.vanhelvoirt@easi.net', 
                        SUBJECT => 'User with soon Expiring password',
            BODY =>
            'Please find attached the file with the requested information.',
            ATTACHMENT => '/tmp/User_with_soon_expiring_password.xlsx');

Resulting in:

and the spreadsheet looking like this:

The final step is to add the execution of this SQL script to the Advanced Job Scheduler (AJS).

For this, the SQL script used needs to be available on the IBM i in some directory.

The content of that script file looks like this:

 
 BEGIN
    DECLARE SPREADSHEET CHAR(1);
    DECLARE EMAIL CHAR(1);
    VALUES SYSTOOLS.GENERATE_SPREADSHEET(
            PATH_NAME => '/tmp/User_with_soon_expiring_password', 
                        spreadsheet_type => 'xlsx', -- lower case required
            SPREADSHEET_QUERY =>
    'SELECT authorization_name, DATE_PASSWORD_EXPIRES, PREVIOUS_SIGNON, STATUS, 
         LAST_USED_TIMESTAMP
    FROM QSYS2.USER_INFO AS USER_INFO
    WHERE (DATE(DATE_PASSWORD_EXPIRES)) >= CURRENT DATE - (30 DAYS) AND 
    SUBSTRING(AUTHORIZATION_NAME, 1, 1) <> ''Q'' AND STATUS = ''*ENABLED''
    ORDER BY DATE_PASSWORD_EXPIRES', COLUMN_HEADINGS => 'COLUMN') 
        INTO SPREADSHEET;
    IF (SPREADSHEET = 1) THEN
        CALL systools.lprintf(
            'Spreadsheet /tmp/User_with_soon_expiring_password.xlsx generated successfully');
    END IF;
--  The final Step - Mail the generated spreadsheet as an attachment
    VALUES SYSTOOLS.SEND_EMAIL(
            TO_EMAIL => 'r.vanhelvoirt@easi.net', 
                        SUBJECT => 'User with soon Expiring password',
            BODY => 'Please find attached the file with the requested information.',
            ATTACHMENT => '/tmp/User_with_soon_expiring_password.xlsx') INTO EMAIL;
END;

As you may have noticed, the use of values results in output. This blocks the running in batch.

For that reason, I have added some code to get around this.

The content of that AJS task look like this:

The CHGJOB prevents an error when running the SQL Script, due to Coded character set identifier (CCSID) set to 65535. If your IBM i is running with another value, you can try omitting the CHGJOB command.

The details of the command to be run in this AJS task are:

RUNSQLSTM SRCSTMF('/HOME/RUDI/SQL SCRIPTS/CREATE AND MAIL SPREADSHEET - USER WITH EXPIRING PASSWORD.SQL') COMMIT(*NONE) NAMING(*SQL)
 

With this SQL script, I have not taken away the pain of having to change the password for “automated” user profiles, as I call them, but now at least you know when to spring into action.

That is a start, you might even call it a good start.