Day: April 25, 2022

April 2022 Newsletter

This newsletter includes:

Well from reading Steve Will’s blog this month, we know the next release of IBM i is very, very close to being announced. That is great news for IBM i customers. There are a lot of really cool features that have been added to this next release, and I can’t wait to tell you all that I know on Announcement day. In fact, we will show you just how easy it is and everything you need to know to upgrade to the next release of IBM i as soon as it is announced. I think the other benefit for the whole community, is that the roadmap gets updated and we will still have at least 2 more releases of IBM i still to come out after the one this year. That pushes IBM i support probably out to around 2035ish by simple math applied to the current schedule.…

Consuming-Journal-Receiver-Data-Easily-with-SQL

Consuming Journal Receiver Data Easily with SQL

A few weeks ago we were tasked with helping a customer log all IBM i user profile changes and deletes for audit and historical purposes.

Of course, you can certainly use the security audit journal for this type of task however you may not want to keep journal receivers on the system forever. The security audit journal receivers can be quite large and frequently generated, depending on the busyness of the server.

Truth be told…I absolutely detest interacting with the audit journal data directly. I always have. The DSPJRN command is clunky and the results look like the journal receiver fell off the ugly tree face-first, making them hard to read and comprehend.

Instead, I generated a couple of tables (user_changes and user_deletes) to collect the information, then retrieved information from the audit journal by way of Run SQL Scripts and two new IBM i services contained in the SYSTOOLS schema:

SYSTOOLS.AUDIT_JOURNAL_CP
SYSTOOLS.AUDIT_JOURNAL_DO

Both of these functions pull basic and entry-specific information from the security audit journal and were released in 7.3 TR 11 and 7.4 TR 5. SYSTOOLS.AUDIT_JOURNAL_CP contains user profile changes while SYSTOOLS.AUDIT_JOURNAL_DO contains object delete events. While the AUDIT_JOURNAL_CP table function gives me everything I need, the AUDIT_JOURNAL_DO will do much more than that. It returns information about EVERY object deletion, so I need to ensure that the object type returned is a user profile object (i.e., *USRPRF).

INSERT INTO
profilelog.user_deletes(entry_timestamp,User_name,
qualified_job_name,object_name)
SELECT entry_timestamp,User_name,qualified_job_name,object_name
FROM TABLE (
SYSTOOLS.AUDIT_JOURNAL_DO(STARTING_TIMESTAMP => CURRENT TIMESTAMP – 1
DAY)
)

WHERE object_type = ‘*USRPRF’;

Now, each day there’s a job run on schedule that will collect those specific journal entries for user profile changes and deletes and insert them into the two logging tables. Could you trigger that type of call by way of a trigger like an exit point program? Absolutely. I simply chose the job scheduler option simply because it’s quick and easy. Depending on the time it runs each day, I could get a couple of duplicate records but that’s not the end of the world…they’re timestamped entries.

From a user profile change perspective, the real value is being to determine not only who changed a user profile but WHAT they changed. Did they have no special authorities before? Was *ALLOBJ special authority added? Was it a password change? Does that password change meet password requirements? AUDIT_JOURNAL_CP will tell you all that and more very quickly…and with plain English …

Yes or No to Managed Service Providers?

CIOs are tasked with many important duties and responsibilities, two of which include the management of IT personnel and maintaining IT systems and operations.  The number of IT professionals with experience working on the IBM i (AS/400, iSeries) platform being brought into this environment is far less than the number of individuals retiring in the next several years.  The number of businesses that still utilize the IBM i to run their most critical applications is abundant and many of them do not plan to migrate away from the platform any time soon.  Notably, more than 50% of these companies have just one or two system administrators tasked to maintain this hardware. In the unfortunate event that one or both admins retire, resign, or leave this important position, for whatever reason, the performance and security of the system running your core business applications will be put at risk. Combined with the fact that the talent pool for system administrators on this platform is small, it begs the question: Do you need to consider collaborating with a Managed Service Provider (MSP) sooner than later?

Where is your Level of Concern?

Low Level of Concern – With several administrators on staff, backup support is available should someone need to leave his/her position permanently or for an extended period.

High Level of Concern – With just one or two system admins on staff, backup support is non-existent or minimal at best.  The system maintenance begins to fall behind. This leaves the system, which may run more than half of your core business applications, vulnerable to performance and security issues. In addition, not having the resources to manage mission critical system alerts that can, and do occur, when a system is not being monitored or maintained properly, can cause businesses to stop running.

When & How to Move Forward

When to engage an MSP depends on the level of concern you have about the available resource(s) you employ to help you maintain your system(s) properly now and in the future.  If you have an elevated level of concern, it is time to consider partnering with an MSP.  How to go about engaging an MSP is not difficult. The best resource is talking with other companies that currently utilize the services of an MSP. References are necessary and will put you at ease when it is time to make the final decision.  Be sure to discuss …

Securing Spool Files

Let’s chat a little on the security buzzwords you need to know to access and how it affects Spool files. The good thing is we are seeing policies and laws being put in place to protect clients and organizations. Your company’s spool files can contain privileged information that could cause your clients and/or organization harm. Therefore, if we are going to protect our data using the Security Buzzwords Need To Know Access, we also need to get control over who can print, move, delete or view the spool files.

Let’s have a look at how *SPLCTL special authority assigned to User Profiles on the IBM i(AS/400) System works and how easily we can secure the spool files for Need to Know Access.

*SPLCTL special authorities actually gives the User Profile all object to every spool file(data that can be printed) on the System. Therefore when we assign *SPLCTL; that user can see all data on the system including financial and audit information.

The best way to eliminate this risk is to secure the outq’s that have the spool files in them by Need to Know Access.

The first thing that needs to be performed is testing. To test remove the *SPLCTL from the User profile and follow the below steps:

The below shows how quickly you can secure output queue’s so that only authorized User’s with Need to Know Access can view it, delete, move or print spooled files.

Output can be designated so that some users may not use it at all, some users may view or change only their own spooled files, that is, spooled files they created, and some users may view or change anything in the output queue.

Caution: Any user with *SPLCTL or *ALLOBJ special authority in the user profile can view any spooled file on the system regardless of any other measures taken to secure the output queue. Therefore *SPLCTL and *ALLOBJ special authority should be removed from all Profiles that don’t have a need to know.

*DSPDTA is Display any file.
*OPRCTL is Operator controlled.
*AUTCHK is Authority to check.
*DTAAUT means that authority to spooled files in the output queue is determined by authority to the output queue object itself.

Opt   Object   Type     Library     Attribute   Text
__    RAYOUTQ  *DEVD     QSYS        PRTLCL
__    RAYOUTQ  *OUTQ     QUSRSYS
__    RAYOUTQ  *MSGQ     QGPL

Type 2 next to the object of type *OUTQ to edit authority. You …