Limiting When A User Profile Can Be Used

By Rich Loeber

Each user profile on your system is a window, of sorts, into the computing environment for your business.  Some profiles have a very narrow and limited view while others have a panoramic scene before them.  Some profiles can only look while others are allowed to look, pick things up, move them around, make changes and even throw them away.  Some only have access to a single library while others, perhaps even you, have the keys to the Kingdom.

As a security officer, you’ve probably given this a lot of thought already and you already have your profiles set up with the exact permissions necessary.  Users are allowed enough access to fulfill their job descriptions but not so much that they can wreak havoc for your organization either accidentally or intentionally.  And, to a large extent, your trust of the person behind the profile plays a large roll in how much access you give them to your system.

Problems come up, however, when a profile is compromised and is used by someone other than the assigned person.  When this happens to a profile that has the panoramic view of your system, real trouble can ensue.

The IBM i OS on your IBM i system has a nice little feature that can give you improved control in the event of a compromised profile.  This feature, the Activation Schedule, lets you specifically tell the system what days and what hours in the day that a profile can be used.  If a user profile is compromised, the chances are very good that the incorrect use will be attempted during off hours.  If the profile in question has been posted to the system Activation Schedule, the profile will not be available for use during the off-hours time frame.  This extends not only to terminal session signon but to all server activity, such as FTP, the system file server, etc.

There are two commands that you use to maintain the system Activation Schedule.  The “Change activation schedule entry” command (CHGACTSCDE) is the main command for maintaining the schedule.  This lets you add a user profile to the list or change a profile that is already on the list.  Once a profile is on the list, a message will be sent to the user profile that established the entry each time the profile is activated and deactivated.  When you create the entry, you specify the time of day when you want the profile available for use.  The system will activate the profile at the given time and then automatically deactivate it at the closing time that you enter.  You can specify this time for all days of the week or for given days of the week.

The other command that can help you with this is the “Display activation schedule” command (DSPACTSCD).  This command lets you review how your Activation Schedule is set up.  You can look at it interactively or create a report of the schedule.

When you first set this up, nothing will happen right away, so be prepared for that.  The system will post jobs into the IBM i system job scheduler to do the actual activation and deactivation.  The next time one of the time of day thresholds is passed, the activity to activate and/or deactivate users will start and you will begin to receive status messages from the system.

Using this feature of the IBM i OS, you can close the window of opportunity when a compromised profile can be used and make it more difficult for mischief makers to do their thing on your system.  One thing to keep in mind, if you adopt this process, is that you may need to make special arrangements when your users work a different schedule than normal, such as overtime work.  During these times, you may have to update the Activation Schedule to accommodate different work hours.

If you have any questions about anything in this tip, just ask me and I’ll give you my best shot.  My email address is rich at kisco.com.  All email will be answered.

Standardize Security Across Multiple Systems

By Rich Loeber

Nothing supports the popularity of the IBM i as much as the number of customers with multiple systems installed.  These can be either multiple partitions on a single box or separate boxes.  For security officers, this can easily mean a lot of extra work keeping each system setup and configured for company security policies.

While this can be a complex task, IBM has provided an little known capability in the IBM i OS for quite a while now that can help you to enforce standard security configuration setup rules across separate systems.  This is through the use of the CFGSYSSEC (Configure System Security) command.  This command, which has no parameters, calls a CL program named QSECCFGS in the QSYS library.  This program sets the security related system values to standard settings recommended by IBM.

The good news for the security officer with multiple systems to control is that this CL program can be changed to meet your unique setup requirements.  The base program as shipped with IBM i can be retrieved and then modified for your unique needs (not unlike the way the system startup program QSTRUPPGM works).

To retrieve the CL program, just run the following command on your system:

RTVCLSRC PGM(QSYS/QSECCFGS) SRCFILE(mylib/QCLSRC)

This will place a source member in your QCLSRC source physical file named QSECCFGS.  To be on the safe side, you should probably rename this and, when you recompile it, place the new compiled program into QUSRSYS.  Once this is done, just change the IBM i CFGSYSSEC command to run the your modified program from QUSRSYS with the following command:

CHGCMD CMD(QSYS/CFGSYSSEC) PGM(QUSRSYS/myseccfgs)

When you review the CL program source that has been retrieved, there is some housekeeping that takes place early in the program.  You can review the settings imposed by the program after the housekeeping to see how IBM recommends your security setup and make changes that will implement standard security settings for your own requirements.

To implement the standard security setup across your multiple system environment, simply install your custom setup program on each system in your network and modify the CFGSYSSEC command on each system to call your modified program in place of the IBM default program.  To guard against possible changes being made to the setup, you can even add this to your automatic schedule to run on a weekly or even daily basis to keep these setting enforced.

The retrieve CL program is a little tough to read, but perseverance will prevail.  If you need help with it, let me know what you need specifically and I’ll see if I can help.  If you have any questions about anything in this tip, just ask me and I’ll give you my best shot.  My email address is rich at kisco.com.  All email will be answered.

Using Authorization Lists

By Rich Loeber

When designing security for your IBM i system, there are lots of things you can do to simplify the task and keep maintenance of security settings to a minimum.  Some of these include implementing group profiles and supplemental group profiles.  Another important one is using Authorization Lists.

An Authorization List is a special system-level object that resides in the QSYS library with object type *AUTL.  Your system can contain multiple Authorization Lists and it is recommended that they be created along application boundaries.  So, one list could be used for Payroll while another list can be used for Inventory, and so on.

An Authorization List simply defines user authority for objects that belong to the list.  When an object is created, rather than creating individual private authorities to the object, just associate it with the appropriate Authorization List.  The List, in turn, will control individual and *PUBLIC authority to all of the objects in the list.

Using an Authorization List will simplify setting up new users and maintaining object authority on your system.  It also has some nice side benefits.  The individual size of user profiles is kept much lower using Authorization Lists.  Also, system performance is improved when running SAVSYS backups and when saving security information on your system with the SAVSECDTA command.  But, one of the most significant advantages is that security changes can be made to a list even when objects in the list are open and active on your system.  This means that you can make security adjustments on your system even when the applications are active and running.  Conversely, when using private object authorities, you can only make security changes when the file is not in use.  This alone can save you loads of headaches and late night sessions on your system.

To get started with an Authorization List, you first need to create the list using the Create Authorization List (CRTAUTL) command.  Be sure to set the *PUBLIC authority level using the AUT parameter.  Once it is created, you can work with it using the Edit Authorization List (EDTAUTL) command.   Using this command, you can add individual users who will need more authority than is allowed by the *PUBLIC authority setting.

Once you have the list configured, it is time to use the list on the objects you want it to control.  You can grant authority via the list using the Grant Object Authority (GRTOBJAUT) command.  Remember that only one authorization list can be used to secure a specific object.  If you are implementing Authorization Lists on a system where private authorities were previously used, you might want to also use the Edit Object Authority (EDTOBJAUT) command so that you can also remove any private authorities that are now taken care of by the Authorization List.   You can also add new users to the list by a simple Add Authorization List Entry (ADDAUTLE) command.  Each individual entry in the list will control the level of authority that user is given to the object.

Remember that a private authority to an object will override the authority provided by the Authorization List.  If you are converting your security setup over to use Authorization Lists, this will be an important consideration.  A private authority will also override a group profile setup, so this is something that will be important to review and check as well.

If you have any questions about anything in this tip, just ask me and I’ll give you my best shot.  My email address is rich at kisco.com.  All email will be answered.

Search For The Guilty Party

By Rich Loeber

Part of computer security is prevention, and a good access prevention policy along with good controls will go a long way to protect your data from improper access and use.  But, people with proper authorization make incorrect data changes that can wreak havoc on a system.  At times like that, it is sometimes nice to be able to backtrack and see who did what and when they did it.

Here is an easy way to implement a tracking system on an IBM i database without any significant programming on your part.  This technique takes advantage of the IBM i database audit journal features.

First, you have to create a Journal Receiver and a Journal.  In this example, we’ll set up to track data changes to a file named CUSTMSTR in library MYLIB.  Use the following commands to create these objects:

CRTJRNRCV JRNRCV(MYLIB/CUSTMSTR)

CRTJRN JRN(MYLIB/CUSTMSTR) JRNRCV(MYLIB/CUSTMSTR)

Now, you can activate tracing on your physical file by processing the following Start Journal Physical File command:

STRJRNPF FILE(MYLIB/CUSTMSTR) JRN(MYLIB/CUSTMSTR) IMAGES(*BOTH)

At this point, the database journal is active for your file and all activity against that file will be tracked and captured in the Journal Receiver.  Now, use your application program to work with your database file.  Make some file inquiries and also perform some record changes.  When you’re done, use the following command to take a look at the file tracking information:

DSPJRN JRN(MYLIB/CUSTMSTR)

You will see several different forms of tracking information displayed.  Each time the file is opened, closed, read and updated, an entry will be inserted.  The first few entries just track the fact that journaling has been started for the file.  After those, you will see the results of the testing transactions that you performed.

To review file changes, look for the audit records with type codes “UB” and “UP”.  The UB record shows you a record image BEFORE an update was posted and the UP record shows the record image AFTER an update was posted.

In your search for the guilty party, the DSPJRN command leaves a lot to be desired.  To give you a better way to look at before/after data changes, you can transfer the DSPJRN information into a database file and then use your favorite database tool to create more usable information.

To create the database for your query tool, run the following form of the DSPJRN command:

DSPJRN JRN(MYLIB/CUSTMSTR) OUTPUT(*OUTFILE) +
OUTFILFMT(*TYPE4) OUTFILE(MYLIB/CUSTMSTRJ)

In this sample, the database version of the Journal now resides in the database file named CUSTMSTRJ.  Use your query tool to select records with type codes UB and UP (the field name to select on is JOENTT).  If your database file record length is exceptionally long, you may have to parse the record data information to get at what you are looking for, but the report generated should point you to all of the record changes posted to the file and you should be able to sort out who did what and when they did it.

If you prefer an easier way to handle all this, take a look at iFileAudit from Kisco Information Systems.  iFileAudit will automate this entire process and show you file updates on a field by field basis telling you all of the who, how, when and where information as you search for the guilty party.

If you have any questions about anything in this tip, just ask me and I’ll give you my best shot.  My email address is rich at kisco.com.  All email will be answered.

 

“i Spy” – Checking up on a specific user

By Rich Loeber

For some reason, my career in Information Technology frequently included responsibility for the company telephone system.  Oftentimes, I would end up with a department head closeting himself in my office to obtain a report of telephone activity for an employee that they suspected of some malfeasance.   Most of the time when this came up, I had the luxury of having the data already collected in a call accounting software package and could just run a report and send the department head on their way.

In this day of heightened security consciousness, I can fully expect to see this scenario played out with data access as the expressed concern of this same department head.  Unfortunately, software for full implementation of security reporting can be very pricey.  But, if you just want to check up on someone, the IBM i has very good auditing capabilities that you can use down to the individual user level.  And, you can do this without a major headache.  You can target a specific user and leave the others out of it.

Setting Up Security Journaling

The IBM i OS lets you track a lot of different system security events and you can explore all of these in the OS Security Reference manual.  For our purposes, however, all we’re looking at it how to set up security auditing for an individual user.

If you have already used audit journaling, you can skip to the next heading in this tip.

To start security audit journaling, you can use the IBM i CHGSECAUD.  For our purposes, you can use the following command:

CHGSECAUD QAUDCTL(*OBJAUD *NOQTEMP *AUDLVL)

If your audit journal does not exist, this command will set up the journal receiver and create the system audit journal.  Two system values will also be updated.

Journaling Your User

Now to start tracking your suspicious user.  First, you’ll want to start auditing for the specific user.  You can do this with the following command:

CHGUSRAUD USRPRF(USERNAME) OBJAUD(*ALL)

You should key in this command and prompt it to see if there are additional events that you want to track in the AUDLVL parameter.   You should probably include *CREATE and *DELETE options at a minimum.

The final step in your setup is to specify user audit tracking for the set of objects that you want to track for this user.  You can do this with the CHGOBJAUD command.  For user profile audit journaling to work, the objects must be set to OBJAUD(*USRPRF).

Viewing the Audit Information

Once you have the audit journal active, you can view the tracking information on-line using the following command:

DSPJRN JRN(QAUDJRN)

If you have an active audit journal with a lot of activity for many users, you can limit the information displayed by using the USRPRF parameter.

As with all security matters within the IBM i OS, this is tip just scratches the surface.  You can see more information in the IBM i Security Reference manual.

If you have any questions about anything in this tip, just ask me and I’ll give you my best shot.  My email address is rich at kisco.com.  All email will be answered.

Drive The Boat!

By Rich Loeber

Sometimes, we get so wrapped up in what we’re doing down in the security trenches, that we loose sight of the overall objectives.  When that happens, bad things can occur.

I have mentioned before that we live in a remote community deep in the Adirondack Park in upstate New York.  Our house is on one of the larger lakes and that lake is interconnected with a huge system of creeks, streams, smaller lakes and ponds.  It is a canoeist’s dreamworld.  On a recent weekend, my wife and I packed off for an 8 mile paddle with a break for lunch in the middle.  It was a beautiful day and the trip was great, until the end.  On our return, when we left the protected creek that we’d been traveling on, we saw every canoeist’s nightmare; a powerboat pulling a water skier headed straight for us.  The boat had the required two people in it, but they were both looking backwards at the skier.  It was the skier who finally saw us (as I was waving my paddle furiously in the air) and motioned to the driver to look around.

What was wrong is that the driver forgot his primary objective.  He should have been driving the boat, not watching the skier.

After I settled down from this, and survived the wake from this close call, it occurred to me that when we get down in the trenches in our job as security officer, that we too can easily forget to “drive the boat”.

What do I mean by this?  I mean your overall corporate objectives.  Why are you in business?  What’s your end product?  How is it getting delivered?  What is your place in the process?  Is what you’re doing helping to meet the objectives?  Or, if you’ve forgotten to “drive the boat”, is what you’re doing making it harder for everyone else to keep on track?

Often, in our zeal to keep things safe, we make it hard for everyone else to just do their job.  If that’s happening in your shop, I’d take a second look at what you’re doing.  Security should be done in such a way that legitimate users (your customers) should be able to do their job without having to jump through any hoops, not even little ones.  At the same time, you need to be able to identify risk areas and set up an environment where unauthorized users cannot easily get to automated company assets.

So, how do you know how you doing?  I’d start with checking on your phone calls and end user email.  What’s the most common complaint in the last few weeks?  If you get a lot for the same reason, then this might be an area where you need some attention. For example, do you have situations where you require multiple logons to gain access to your system.  Each additional logon takes time and is not very productive.  If your risk assessment is fairly low, look at ways to implement a single signon.  Do you often get calls from users who can’t access some bit of data that they need?  If so, it might be time to reassess your data access policies to bring them up to date.  Every time someone has to call to get a security change implemented, they aren’t “driving the boat” and the results could be very bad.

I’d love to hear your war story along these lines.  If you have a good one, send it in and I’ll collect and publish them at some point down the road.

If you have any questions about anything in this tip, just ask me and I’ll give you my best shot.  My email address is rich at kisco.com.  All email will be answered.

The Enemy Within

By Rich Loeber

For many, this has been a hard year.  In many IBM i shops, we have been forced to find new ways to save on expense while still being asked to provide the same level of computing support as in past years.  This is not always easy, and I have seen evidence that some shops are sacrificing security to conserve on their budgets.

One significant area where I’ve seen this is on data asset protection.  More than one shop that I’ve been in touch with this year has decided to put their complete trust for data asset protection into their firewall at the expense of all other ways of protecting their data.

With your system attached to a network on a full time basis, and with the network interconnected to the Internet on an around the clock basis, trusting your data protection to a single piece of technology is just a bad idea.  Imagine yourself living in a neighborhood with a high crime rate.  Would you have a single lock on your door?  Like most people in this situation, wouldn’t you use two or three (or even more) methods to keep your doors and windows secured?

When your system is connected to the Internet, you are in a high crime neighborhood and you need to use the same approach to protecting your data.  When someone breaches your single point of protection, that could leave your entire system open to malicious abuse.

Also, trusting your data protection to just a firewall completely ignores the issues of intrusion from sources within the “protected” network.  In a small shop, where you can see who is in and who is doing what, maybe this is not much of a concern, but in today’s large shops with widespread deployment of networks, you cannot keep an eye on what everyone is doing.  Anyone who is within the “secure” network can find access to your system using a variety of tools available to today’s savvy computer users.

If you have deployed your firewall as your primary defense against intrusion, you are completely ignoring the enemy from within your organization.  Most security experts will tell you that at least half of all intrusions today (some say more) come from within your organization.  With the ease of downloading data and storing it in a convenient portable form, anyone in your organization could easily take home critical data assets from your organization on a laptop or even on a USB drive that looks like a key fob.

The question you need to ask yourself is, am I saving money wisely or am I thinking short term just to look good.  In today’s environment, you simply cannot put all of your eggs in one basket.  To adequately protect your system, you need to present multiple hurdles for your enemies to overcome.  If they get past one, there is a good chance that the next one they encounter will defeat them.

The good news is that your IBM i comes with a lot of tools available to you so you can build these additional lockouts.  To protect yourself from the enemy within, you will need to build strong object level access controls.  You will need to rigorously enforce a policy of no user profiles with *ALLOBJ authority.  You will need to also enforce a policy of password rotation on a frequent basis with password controls that prevent the reuse of passwords and the use of passwords that are easy to guess.  Lastly, the strong lock of exit point controls will also help keep your data safe.

All of these options, and more, are open to you.  Some may cost you some money, but the alternative of seeing your organization on the front page of the paper for data theft would be much more expensive in the long run.

If you have any questions about this topic, you can reach me at rich at kisco.com, I’ll give it my best shot.  All email messages will be answered.

Are Your Backups Complete?

By Rich Loeber

No security plan is complete without addressing the issue of safe and complete backups.  When the AS/400 was first announced, the process was fairly simple and straight forward.  All you needed to do was save all the libraries on your system, save OS/400 in a way it could be reloaded and then make arrangements to backup your security and configuration settings.

As the AS/400 has morphed into the IBM i, things have gotten more complex.  Additional items needed to be saved and new command structures needed to be learned.  With today’s systems, it is not as simple as the original plan, but the tools are all there.  If you haven’t reviewed your backup plan recently, it would be a good idea to dust it off to make sure that everything you need is being saved on your system.

A backup plan generally has two objectives.  First, to be able to recover the entire machine in the event of a catastrophic loss of your processor.  This recovery can be either on your own machine, following repair, or at an off-site location.  The second objective is to be able to recover an individual object in the event of accidental or purposeful object loss.  Your backup plan needs to support both of these objectives.

To get a complete backup of your system today, your backup plan has to provide for the following components of your system:

1. The IBM i operating system
2. Your machine configuration
3. Your systems security settings
4. All native IBM i libraries
5. All shared folder files
6. All files in your Integrated File System (IFS)

The more recent additions to this list are these last two and this is where I want to concentrate efforts for this article.  If you’ve been around the IBM i for a while, you’re probably already quite familiar with items 1 through 4.  If not, feel free to shoot me an Email and I can help you out off-line.

It could be argued that numbers 5 and 6 on this list are both part of the IFS, but the IBM i provides some commands specifically designed for the shared folder system, so you can consider it separately if you want to.

You can save just the Shared Folder files (also known as the QDLS file system in the IFS) using the IBM i command SAVDLO.  To save all files in the Shared Folder system, you can use the following command:

SAVDLO DLO(*ALL) DEV(TAP01) OUTPUT(*PRINT) SAVACT(*YES)

Note, this command will produce an audit report of what was saved which you can print and keep with the backup or just hold in an output queue.  This report tends to be quite lengthy on most systems.  When you run this command, all document library objects on your system will be saved along with all folders and files in the entire Shared Folder system.  The SAVACT(*YES) parameter helps to make sure that the backup is complete by saving objects that may be in use.  If you run your backup in a restricted state, this parameter will not be necessary.

To save the files in your Integrated File System, you will need to use the IBM i SAV command.  This command can actually be used to save your entire system, but its use for native IBM i objects can be confusing so most shops limit its use to the IFS objects.  To save all files in your IFS, use the following command:

SAV DEV(‘/QSYS.LIB/TAP01.DEVD’)
OBJ((‘/*’) (‘/QSYS.LIB’ *OMIT) (‘/QDLS’ *OMIT))
SAVACT(*YES) OUTPUT(*PRINT)

Note that the output device naming method is quite different from standard IBM i command formats.  Also, if you are using the plan outlined above, you want to make sure that you don’t save objects again that have already been saved at points 1, 4 and 5.  The OBJ parameter controls this function.  In this SAV command, the OBJ parm contains three elements.  The first element specifies that everything is to be saved.  The next two sub parameters modify this by specifying that the QSYS file system and the QDLS file system be omitted from the backup.  By leaving these out, you will end up saving just the IFS objects you need.

If some of this appears strange to you, then it means you need to dust off your backup plan and bring it up to date.  Today would probably be a good day to do that.

If you have any questions about this topic, you can reach me at rich at kisco.com.  I’ll give it my best shot.  All email messages will be answered.

Security Incident Response

By Rich Loeber,

As the security officer for your IBM i shop, you’ve done all you can to lock down your systems and implement your organization’s security policy.  Once you’re all set up and configured, you think you’re looking good, but there may be a piece of the puzzle that is still missing.  One thing we know for certain with computers is that no system is perfect.  So, there is still the possibility that something will break down in the security design for your shop.  This tip will take a look at what you should do when something goes wrong.

Security incident response consists of several steps and processes that you should document for your organization.  There are all sorts of event types that you may be faced with and each one can have a number of responses or actions that you will need to consider.

A security incident is something that has, at its root cause, the action of a person or group of people.  Incidents can have varying degrees of severity and the first step in a response is to decide how serious the incident is for your organization.  Generally, incidents can fall into three categories:

  • Ordinary or Normal – these do not affect your organization’s operations nor do they require notification of management.  They can be contained and dealt with easily within the security group or help desk function in IT.
  • Elevated or Serious – these can affect operations and will require an implementation in order to deal with them.  Management will have to be notified and may have to be involved in the resolution.
  • Emergency – these can affect people’s health and well being, breach normal business controls on your operations, affect your financial performance or may even place your organization in violation of public law.  Management must be informed and others may also have to be drawn along with vendors, customers and public officials as needed.

Each type of incident will need a tailored response.  Ordinary incidents can be logged and dealt with during the normal course of business.  The logs should be reviewed periodically, but these should not end up taking a lot of time.

On the other hand, serious incidents and emergency incidents need to have a response plan in place.

The first thing you want to do is identify the specifics of the incident and determine if it is still on-going or if it was a one time event.  If it is on-going, your response should first attempt to identify the source and then stop the incident from continuing.  An example of this might be that you discover that someone has breached your system via the FTP server function.  If they are still logged on to your system and taking actions, you should first get as much identifying information as you can as to who is the behind the action, then cut them off by shutting down the FTP server function on your IBM i.  This may affect other users on your system, but the integrity of your system is at risk and you need to take action to protect your organization’s assets.

Once the event has been identified and suspended, you then need to analyze the event to determine how the incident was accomplished and what security safeguards can be put in place to prevent it from happening again.  If at all possible, the affected system should not be placed back into normal use until steps have been taken to prevent a repeat of the incident.

As soon as the severity of the incident has been determined, you will also have to notify management and even your organizations principals.  If the incident includes law violations, such as data theft of identity information, then public officials will also have to be notified.  During this process, it may also be important for you to contact and work with your organization’s public relations staff to make sure that the facts that are made public are correct and not overstated.

Each step along the way must be documented and permanent records kept for future reference.  This will demonstrate how the event was handled and it will provide a clear path towards showing how a repeat of the event will be prevented.

These days, it is not enough just to have a security policy in place, you need to be prepared for what might be needed when the security policy breaks down.

If you have any questions about this topic, you can reach me at rich at kisco.com.  I’ll give it my best shot.  All email messages will be answered.

Tracking Changes to User Profiles

By Rich Loeber

A blog reader recently contacted me and asked how they could track changes to user profiles.  They had an audit requirement to be able to prove that when a user was dismissed, their profile was disabled or removed from the system on their IBM i server.

When I got the call, I was up to my eyeballs in work and did not give them a good response.  I suggested that they code and test exit programs to attach to the system exit points for user profile maintenance.  While that solution might work, eventually, it is like wielding a sledge hammer to crack open an egg.

When I had more time to think about it, the easy solution came to mind.  Use the information in the system security audit journal!

When the security audit journal is active on your system (a topic for a different blog post), then whenever a user profile is created, updated or deleted, records are added to the journal to record the fact.

So, if you want to track the history for a user profile, it is entirely possible to get all of the significant changes to the profile using the audit journal.  There are two specific journal audit records that you will need to consider when reporting on these events.

Audit journals are identified by a Journal Code (a one character alpha code) and by an Entry Type (a two character alpha code).  For our purposes here, we are going to look at journal records for Journal Code T (Audit Trail records) and Entry Type CP (Create, change, restore user profiles).  If you also want to look at profile deletes, you can look at the T-DO records for objects with object type *USRPRF.  The rest of this tip shows you how to work with the T-CP records, but a similar approach can be used for the T-DO records.

To trace user profile history using the T-CP records in the system security audit journals, I simply extracted them to a database file and then ran a report on them, sorting them by user profile and time stamp.

To produce the database file, first create an empty database file on your system using a system provided shell file for the CP journal records.  There are several of these available depending on the level of detail that you want, I used the shell file named QASYCPJ4.  You will find this in the QSYS library.  You can create your shell file using the following command:

CRTDUPOBJ OBJ(QASYCPJ4) FROMLIB(QSYS) OBJTYPE(*FILE)
TOLIB(QTEMP) NEWOBJ(TCPFILE)

This will create the file in your QTEMP library, but you can place it anywhere that is convenient for you.

To populate this database file with the right information from the system audit journal, it is a simple matter of using the Display Journal command (DSPJRN) selecting the right filter information.  The following command should work for most systems:

DSPJRN JRN(QAUDJRN) RCVRNG(*CURCHAIN) JRNCDE((T))
ENTTYP(CP) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE4)
OUTFILE(QTEMP/TCPFILE)

Some things to note.  Using the *CURCHAIN parameter will pull all information from all journal receivers for the security journal that are currently available.  If you want to limit the extract to a specific period of time, there are additional filter parameters available on the DSPJRN command.  The output file format of *TYPE4 will format the data correctly for the shell file that we are using.  If you want more information, try a different format, but you will also have to use a different shell file.

Lastly, all you need now is to review the database file or list it.  Select the fields that you want to report on; there are a lot to choose from.  I’m old school and I created a query report using WRKQRY.  If you want a copy of it, just let me know and I’ll send it to you.

If you have any questions about this topic, you can reach me at rich at kisco.com, I’ll give it my best shot.  All email messages will be answered.