Managing Inactive IBM i User Profiles

One part of an effective security strategy is to ensure you’re effectively managing user accounts. On IBM i that means managing user profiles. One aspect of this management is to have a process in place that identifies and removes profiles that are no longer in use. At least two reasons demand this. First, a popular social engineering attack is to contact an organization’s help desk and, with a sense of urgency, asks for a profile to be re-enabled. The profile often belongs to a former employee. Once the profile has been re-enabled and the password reset, the bad actor uses the profile to exploit whatever privileges the former employee had. In addition, eliminating unused profiles will save time when you run SAVSECDTA (Save Security Data) and more importantly, save critical time restoring profiles should you have to recover your system.

While you could pour through all of the user profiles and analyze their use manually, IBM i Services have made this task much easier.  

Step #1: Identifying inactive profiles

The first step in this process is to identify the inactive profiles, that is, profiles that haven’t been used in a specified time. Many organizations have an ‘aging’ process. For this article we’ll use the following (yours may be different): Profiles not used in 30 days will be set to disabled and profiles not used for 60 days will be removed from the system. Of course, there are exceptions and we’ll discuss those along the way.

SELECT user_name,
       date(last_used_timestamp) as last_used,
       timestamp(previous_signon, 0) as last_signon,
       timestamp(creation_timestamp, 0) as create_time,
       status,
       text_description
    FROM QSYS2.USER_INFO
    WHERE (last_used_timestamp IS NULL
              OR last_used_timestamp < CURRENT TIMESTAMP - 1 MONTHS)
          AND (creation_timestamp < CURRENT TIMESTAMP - 1 MONTHS);

Figure 1

Notes regarding Figure 1:

• You MUST look at the Last used date NOT the Last sign on date for this analysis! Many people look at the wrong date and, as a result, take action on profiles that are active. The Last sign on date isn’t updated for all connections and is never updated when the profile is used for batch jobs. However, the Last used date is; therefore, that’s the date to examine when analyzing inactivity. 
• The Creation date is also considered so profiles created within the last month aren’t included in the list
• Note the blank Last used and Last sign on dates. This indicates that the profiles haven’t been used since being restored to this hardware.
• If you’re doing this exercise for the first time, it’s highly likely that you’ll find profiles that were created long ago with the intent that they be deleted after the test was completed but never were. (Clearly the profiles listed meet that criteria!)

Step #2: Disable the Profiles

Now that the inactive profiles have been identified, the next step is to take action so they can’t be used without some type of intervention. The typical action is to set the profile to status of *DISABLED. You can do that ‘by hand’ using CHGUSRPRF (Change User Profile) but IBM has provided a function that allows us to take action using SQL.

SELECT cup.*
    FROM QSYS2.USER_INFO u,
         TABLE (
             SYSTOOLS.CHANGE_USER_PROFILE(P_USER_NAME => USER_NAME, 
             P_STATUS => '*DISABLED', 
             P_TEXT      => 'Careful - do not re-enable', 
             PREVIEW => 'YES')
         ) cup
    WHERE (u.user_name NOT IN (SELECT aidprf
                      FROM QUSRSYS.QASECIDL))
          AND ((last_used_timestamp IS NULL
                  OR last_used_timestamp < CURRENT TIMESTAMP - 1 MONTHS)
              AND (creation_timestamp < CURRENT TIMESTAMP - 1 MONTHS));

Figure 2

Notes regarding Figure 2:

• This function defaults to PREVIEW ‘YES’, meaning that you’ll get the list of profiles that would be changed but the change is not performed. We HIGHLY recommend that you start with this setting and take the time to review the list of users prior to changing the function to PREVIEW ‘NO’. With PREVIEW ‘NO’, running the SQL causes the command listed under CHGUSRPRF_COMMAND to be run.
• You’ll see that we are changing the Text description of the profile. We highly encourage you to change some attribute of the profile as a reminder to your help desk to never enable the profile except through normal profile change processes (rather than an urgent phone call) to help thwart social engineering attacks. 
• You’ll likely have some profiles against which action should never be taken. We’re utilizing the file (QUSRSYS/QASECIDL) that ships with one of the SECTOOLS that comes populated with the IBM-supplied profiles against which action should not be taken. You can add your own profiles to this file using the CHGACTPRFL (Change Active Profile List) command. (View the Help for this command to see the list of IBM profiles omitted.) Display the profiles you’ve added using DSPACTPRFL (Display Active Profile List.)
• Documentation for the SYSTOOLS.change_user_profile table function can be found here.

Step #3 – Deleting the Profiles

After letting the profiles stay on the system another 30 days (60 inactive days total), it’s time to remove them from the system.  You may want to delete profiles ‘by hand’ using DLTUSRPRF (Delete User Profile) or you can use this SQL

DO NOT RUN THIS SQL until you’re ready to delete profiles!!!

--
-   Delete User profiles that are at least 2 months old and have not been
--  used in the last 2 months
--  Reassign ownership of any object to the user's first group
--  If the user has no group assign object ownership to the profile TEMP_OWN
--  If the user profile is in the "do not delete list", skip it
--
SELECT qsys2.qcmdexc('DLTUSRPRF USRPRF(' concat user_name concat ') ' 
                concat 'OWNOBJOPT(*CHGOWN ' concat 
                case GROUP_PROFILE_NAME when '*NONE' then 'TEMP_OWN' 
                  else GROUP_PROFILE_NAME end concat ')' )
    FROM QSYS2.USER_INFO u 
    WHERE (coalesce(last_used_timestamp, previous_signon, current timestamp - 2 months) < CURRENT TIMESTAMP - 2 MONTHS)
      AND (creation_timestamp < CURRENT TIMESTAMP - 2 MONTHS)
      AND (user_creator not in ('*IBM', 'QLPINSTALL', 'QSYS', 'QSECOFR'))
      AND u.user_name not in (select aidprf from QUSRSYS.QASECIDL);

Figure 3: DO NOT RUN THIS SQL until you’re ready to delete profiles!!!

Notes regarding Figure 3:

• This SQL runs the DLTUSRPRF profile command – once again, DO NOT RUN it until you’re ready to delete the actual profiles – there is no ‘preview’ mode!
• The system prevents a profile from being deleted if it owns objects. Organizations use several approaches to solve this issue. Some analyze the list of objects and ‘clean them up.’ Meaning, unused objects are deleted and the rest are shifted to the correct owner. Obviously, this requires time and effort. Organizations that wish to automate inactive profile deletion will typically choose the option when deleting the profile to transfer the owned objects to a specific profile – perhaps the user’s group or to a profile created specifically to own deleted profiles’ objects. This example automatically transfers the profile’s owned objects to the user’s first group profile. If the profile doesn’t have a group, it transfers the objects to the profile TEMP_OWN.
• The QASECIDL file is once again utilized to eliminate profiles that shouldn’t be deleted (including IBM-supplied profiles.) 
• If *IBM, QLPINSTALL, QSYS or QSECOFR is listed in the profile’s Created by user field, the profile will not be deleted. This eliminates profiles created during the OS and vendor product install process from being deleted. However, you may want to remove QSECOFR if you use it as your sign on to do normal work (not recommended!) as you’ll be eliminating profiles that you may want to be deleted.

If you’ve never sorted through inactive profiles or it’s been a while, this may be a bit of a project. But we encourage you to persevere and then set this up as a regular task to protect your system from an attacker abusing one of these profiles.

iEventMonitor from Kisco Systems

With iEventMonitor, you can automatically receive alerts for expiring user profiles as well as alerts for all activity related to specific user profiles.
 

Contributed by:
Carol Woodbury
IBM i Security SME
Kisco Systems