The Db2 and Navigator for i teams have done it again. And Carol bids her readers farewell.

By Carol Woodbury

As has been the case with most of the recent Technology Refreshes (TRs), Scott Forstie’s Db2 for i and Tim Rowe’s Navigator for i teams have delivered fantastic new features in IBM i 7.5 TR4 and IBM i 7.4 TR10. Two of the IBM i security-related features center around the concept of a data mart. Let’s take a look.

The Special Authority Data Mart

The Special_Authority_Data_Mart is implemented as a Materialized Query Table (MQT) and resides in the SYSTOOLS library. You can see and work with it via Access Client Solutions (ACS) by opening the Schemas function, then host_name > Schemas > SYSTOOLS > Tables. If you right-click on the name, you can Refresh the data (repopulate it) or View the data. But I prefer to work with this via the Run SQL Scripts feature of ACS. After launching, run the following SQL to populate (or repopulate) the table. Note: This information is a point in time, unlike the qsys2.user_info view, which is always up-to-date.

Refresh table SYSTOOLS.Special_authority_data_mart;

Now you can list the contents. Note that the output is ordered by special authority, then authority source. In other words, all of the profiles assigned *ALLOBJ special authority where the source is their profile are listed first, then the profiles assigned *AUDIT, etc. After that are all of the profiles whose special authority originates from one of their groups (either their first group or one or more of their supplemental groups). And yes, I tested all of those scenarios!

select * from systools.special_authority_data_mart;

But to be honest, I don’t find this view all that helpful! I prefer the following view, which is ordered by the profile, then their special authority(s) and source(s), because I tend to think of user profiles and their entire set of capabilities rather than view a profile based on whether it has one specific special authority. Once you play around with this information, you’ll find an order that works for you.

SELECT *
    FROM systools.special_authority_data_mart
    ORDER BY authorization_name, special_authority;

How am I going to use this feature? I think it’s a great way to list the users with a specific special authority and understand very quickly the source of that authority. While a special authority example is provided in the ACS Insert from Examples feature, the SQL provided only lists users with a special authority; it doesn’t provide the source nor does it provide any indication that they may have been assigned a special authority via multiple sources. The following SQL provides a list of all profiles having, in this case, *ALLOBJ and provides the source(s) of that special authority.

SELECT *
    FROM systools.special_authority_data_mart
    WHERE special_authority LIKE '%ALLOBJ%'
    ORDER BY authorization_name;

The Audit Journal Data Marts

I’ve written extensively over the years about the plethora of information and ways to use the audit journal. In recent years, IBM has made it significantly easier to get information out of the audit journal with the introduction of audit journal helper functions. These helper functions allow us to use SQL to directly access the exact information we’re looking for without have to first run Copy Audit Journal Entry (CPYAUDJRNE) to generate a file containing all of the entries for a specific audit journal entry type first. Here's an article describing how to use the audit journal helper functions. While the latest TR added two more helper functions (NA for changes to network attributes, and PS for profile swap), the far more exciting enhancement comes in the form of audit journal data marts and the ability to manage them both in SQL and Navigator for i. Using qsys2.manage_audit_journal_data_mart, I can create a new data mart for an audit journal entry type (e.g., PW for invalid signon attempts, SV for system value changes, CP for user profile creates/changes, etc.). When the procedure runs, it creates a file with a system name of AJ_xx, where xx is the two-letter audit journal entry type. (The long name is audit_journal_xx.) The following SQL creates a file, AJ_CP in library CWOODBURY containing CP audit journal entries for the last 7 days:

CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART(JOURNAL_ENTRY_TYPE => 'CP',                        DATA_MART_LIBRARY => 'CWOODBURY',                        STARTING_TIMESTAMP => CURRENT DATE – 7 days,                        ENDING_TIMESTAMP => CURRENT TIMESTAMP);

Why is this so cool? Because I can continue to add to this file and retain information about the creation of and changes to user profiles (or any other audit journal entry type) without having to keep around my audit journal receivers! Most organizations can keep only 7-10 days’ worth of audit journal receivers on their systems (some even less) before the journal receivers need to be removed from the system due to space concerns. But just because they’re gone from the system doesn’t mean the need to examine the information contained in them goes away. Using this same procedure, I can continue to append new entries to this file. The *CONTINUE value for the starting_timestamp assures that the information will pick up where the last update left off (assuming the audit journal receivers for that time period are still on the system). CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART(JOURNAL_ENTRY_TYPE => 'CP',                            DATA_MART_LIBRARY => 'CWOODBURY',                            STARTING_TIMESTAMP => '*CONTINUE',                            ENDING_TIMESTAMP => CURRENT TIMESTAMP,                            DATA_MART_ACTION => 'ADD'                            );

Now, I can go back and investigate how and when a profile got added to a new group (for example) without having to restore a single audit journal receiver! The information is stored in a physical file, so all I have to do is run an SQL select statement over the cwoodbury.audit_journal_cp file to view the audit journal entries for the profile under investigation. This method of retaining entries from the audit journal will be beneficial for any type of audit information you wish to retain on your system for a long period of time but currently can’t due to the space that audit journal receivers consume.

Notes:

• You need *AUDIT special authority and authority to QAUDJRN and its receivers to run the manage_audit_journal_data_mart procedure.
• Files created as a result of running this procedure are created as *PUBLIC *EXCLUDE. If other teams, such as your compliance team, or individuals need access, grant them a private authority of *USE; don’t change *PUBLIC. This file contains information that should be considered confidential and shouldn’t be viewed by just anyone.

Don’t want to run this SQL yourself? The data mart feature has also been added to Navigator for i. Simply log on and then click on the open padlock icon. A new option has been added under the Audit Journal category. See Figure 1.

Figure 1: To manage and view data marts, click on the padlock and then Audit Journal and select Manage Data Mart.

If you’ve already created data marts using the data mart procedure described earlier, they’ll be listed here. If not, choosing Actions > Create New Data Mart will walk you through the creation process. See Figure 2. Note that it defaults to populating the data mart starting with the oldest journal receiver on the system. If you’re one of the lucky organizations that has been able to keep a number of journal receivers on the system, the creation process may take a bit to run if you leave the starting timestamp as the default.

Figure 2: Determine the journal entry type and how far back you want to go when creating and populating your new data mart.

Once the data mart is created, right-click on the name to view your options. See Figure 3.

Manage allows you to add or remove entries. That’s right: remove entries. Perhaps you have the requirement to keep 13 months’ worth of a particular audit type on your system. You can manage (get rid of) older data right from this interface. You can also view the information in a number of ways. Detailed view allows you to view all of the entries in the file. You can then filter on the column to get to the information you’re looking for. You can also view the number of entries for one day or over several days in graph form. (The option is Weekly View, but in reality, you can put any timeframe and the number of entries for that date range will be shown in bar graph form.) I really like the weekly view. Why? To spot trends. For example, I would expect to see a higher number of invalid signon attempts on Mondays. But if I see the number rise on a Friday or right before a holiday, I’m going to be concerned that we’ve experienced some type of attack since this is often when bad actors strike. The last action is the coolest, I believe. “Schedule” provides the ability to schedule the addition of records to this data mart. Think of scheduling an addition to occur every week before you save and remove your audit journal receivers. The job will be scheduled in the IBM i traditional job scheduler. If you wish to use a different job scheduler, simply copy the command string shown into the job scheduler of your choice.

Figure 3: Right-click to see your options for working with a data mart.

Note that the data mart does not take the place of the actual audit journal. For compliance and forensic reasons, you’ll still want to save your audit journal receivers and ensure you have a complete set saved in such a way that you can easily restore them if a breach occurs or an auditor demands it. But using data marts for audit journal information opens up many new ways to use this information since it can be easily retained and examined. Thank you to Navigator for i Team Lead Lora Powell and developer extraordinaire Ann Wilkerson for getting this feature implemented! Now it’s just up to our imaginations how to make the best use of it.

Thank You and Farewell

You’ve just read my final article for MC Press Online. The only reason I’ve been able to write for so long is because of you—my faithful readers. Thank you for indulging me for all these years and allowing me to explain various aspects of IBM i security. Hopefully, I’ve made the topic more approachable. I’ve thoroughly enjoyed bringing you updates and explaining new features from IBM, so I think the subject of this last article is pretty fitting. In addition to you, my readers, I need to thank David Uptmor, owner and publisher of MC Press Online as well as the publisher of my books, for giving me this voice and outlet for my desire to educate. But the person I owe a HUGE thank you to is my longtime editor Victoria Mack. She has corrected many dangling participles, mismatched pronouns, and incomprehensible sentences! I’ve learned so much from Victoria and have had a longtime goal of sending her an article that she didn’t have to correct. I never managed to do that, but I know you’ve had a better experience reading my articles because of Victoria. Thank you, girlfriend!

I’m not retiring (yet), but I’m entering a new phase of my career and I’m looking forward to the new opportunities that being an IBM i Security Subject Matter Expert and Senior Advisor for Kisco Systems will bring. But if you don’t mind, I’d like to provide one final piece of advice: Don’t get stuck. DO SOMETHING to secure your IBM i. Remember: It’s secureable, not secure! Even the smallest change you implement today makes your system more secure than it was yesterday. Happy securing, everyone!