Gain efficiency and flexibility using these new techniques for managing user profiles and their attributes.

I realize that it’s hard to change the way you do things when the method you’re using is working. But using the techniques I’m about to describe provide great flexibility and may allow you to streamline some processes. So rather than clicking this article closed, read on to see if you can pick up a helpful tip or two.

Tip #1: Don’t Be Afraid of SQL

My examples use IBM i Services and are accessed using SQL. DON’T YOU DARE STOP READING! I don’t care if you’re an SQL expert or not, these examples are copy-and-paste-ready. You don’t have to be an SQL expert. When I started to use these methods, I couldn’t even spell “SQL,” so I’m not going to accept the excuse that you don’t know SQL and therefore can’t use these examples.

Tip #2: Stop Running Display User Profile (DSPUSRPRF) to an Outfile

Generating a user profile report in the past always required use of the age-old technique of running DSPUSRPRF *ALL OUTFILE(yourlib/yourfile) to generate a file containing information about all profiles on the system and then running a query (*QRYDFN) against that file to get the desired results. I highly recommend this be replaced with running SQL over the USER_INFO view in the library QSYS2. Why? Because the information is always up-to-date. Delete a profile, and the view is updated without any action on your part. Same for creating or changing a profile. How many times have you run a query and realized you were running it against old data? If for no other reason, you need to switch your process so you can be assured you’re running against the most current information.

How exactly do you do that? Yes, you could stick to your green-screen and use the following SQL after running Start SQL (STRSQL), but since you’re changing your ways, let’s be really radical and use Access Client Solutions (ACS) to get the information. Launch ACS and then click on Run SQL Scripts. When the window opens, type this:

SELECT * from QSYS2.USER_INFO;

This Select statement provides information about all of the user profiles on the system. Scroll to the right to see all of the fields that you can report on/analyze. You can download this (and the other examples in this article) to a spreadsheet by going to Options > Enable Save Results. Run the SQL again and then right-click on a field in the results. Click on Save Results. The resulting dialog allows you to select the file type (.xlsx, .csv, .txt, etc.) and location to save the file.

Tip #3: Stop Analyzing Based on User Class (USRCLS) and Use These Instead

I continue to see organizations analyze user profiles based on their user class. This method drives me crazy because the user class is not used when access to an object is checked. So thinking a user is in the “wrong” user class is really a waste of time. Instead, here are examples of some of the analysis I’d perform.

Members of a Group Profile

Reviewing the list of profiles assigned to each group is important in order to catch a user who has changed jobs and perhaps been assigned a new group profile but was left with the original set of groups to facilitate cross-training. To make sure users have access only to the information required to perform their jobs, their group and supplemental group assignment need to be reviewed regularly; I recommend at least once a quarter. To review all members of all groups simply run this:

SELECT * from QSY2.GROUP_PROFILE_ENTRIES;

If you want to focus on the members of one specific group such as QPGMR, add a where clause:

SELECT * from QSY2.GROUP_PROFILE_ENTRIES WHERE GROUP_PROFILE_NAME = 'QPGMR’;

Special Authorities

The following example lists profiles having a specific special authority. In the example below, it’s *ALLOBJ. (Simply replace *ALLOBJ with another special authority, such as *SECADM, to get that list.) The profile will be listed regardless of whether the special authority is assigned to the profile itself or is inheriting the special authority via one of its groups. I recommend this list be reviewed at least once a quarter to ensure profiles still require each special authority assigned. Similar to reviewing the members of each group, reviewing the list of profiles with each special authority will catch users that have changed roles as well as catch special authorities that may have inadvertently been assigned to a group and are now available to all group members.

Note that the code below started out as an example provided with ACS, but I’ve modified it to select different fields that better fit the profile attributes helpful for this review. To see the entire list of examples provided with ACS, open Run SQL Scripts > Edit > Examples > Insert from examples. Use the drop-down and choose IBM i Services. Scroll down to see the Security examples. Click on an example and you’ll see a preview of the code in the right-hand pane. If it’s something that’s close to what you want to do, click Insert and either run it as is or modify to meet your requirements.

SELECT authorization_name, special_authorities,

group_profile_name, supplemental_group_list, text_description FROM QSYS2.USER_INFO

WHERE SPECIAL_AUTHORITIES LIKE '%*ALLOBJ%'

OR AUTHORIZATION_NAME IN (

SELECT USER_PROFILE_NAME

FROM QSYS2.GROUP_PROFILE_ENTRIES

WHERE GROUP_PROFILE_NAME IN (

SELECT AUTHORIZATION_NAME

FROM QSYS2.USER_INFO

WHERE SPECIAL_AUTHORITIES like '%*ALLOBJ%'

)

)

ORDER BY AUTHORIZATION_NAME;

Limited Capabilities

You may also want to look at attributes such as the limited capability setting. Your review would be to make sure that any profile set to limited *PARTIAL or *NO really has a job requirement to use a command line.

SELECT authorization_name, special_authorities,

group_profile_name, supplemental_group_list,

limit_capabilities, text_description

FROM QSYS2.USER_INFO

WHERE LIMIT_CAPABILITIES <> '*YES';

Inactive Profiles

Finally, inactive profiles need to be managed so they can’t be used as a target of abuse. Some organizations use the Analyze Profile Activity function from the SECTOOLS menu to set profiles to status of disabled after a period of time. But often, organizations want to manage the disabling and deletion of profiles themselves. Even if you have automated these processes, reviewing the list produced by the SQL below can assure that your processes are working. In addition, automated processes usually have some type of “omission” list so that profiles will never be touched by the automated process. It’s good to review that omission list so you don’t continue to omit profiles that really should now be set to status of disabled or even removed from the system. The following SQL lists all profiles that were created over three months ago and haven’t been used in that timeframe.

SELECT authorization_name, last_used_timestamp, previous_signon, creation_timestamp,

status, text_description

FROM QSYS2.USER_INFO

WHERE (last_used_timestamp is NULL or

   last_used_timestamp < current timestamp - 3 months ) and

   (creation_timestamp < current timestamp - 3 months );

You can do all sorts of fun things with timestamps. For example, some organizations want to disable a profile if, after creation, the user doesn’t change their password within the first week. So you could do something similar to the previous example using the password_change_date and use “days” as the timeframe rather than “months.”

Tip #4: Don’t Settle for Information Provided by CL Commands

Don’t get me wrong: Commands such as Analyze Default Password (ANZDFTPWD) and Print User Profile (PRTUSRPRF) are great, especially if you’re just getting acquainted with IBM i, but I prefer to get information formatted in a way that helps me more easily analyze risk. Let’s look at ANZDFTPWD. In addition to the name of the profile with a password that’s the same as the user profile name, the report provides the profile’s status (enabled or disabled), indicates whether the password is expired, and shows the text description, which is somewhat useful information, but if I have profiles with default passwords, especially a long list of them, I want more information so I know where to focus my efforts in setting that password to something other than the default. I want the special authorities so I can address the most powerful profiles first, group and supplemental groups so I can know if it has been assigned to a powerful group, status so I can focus on those that are enabled, last-used timestamp so I can understand if it’s currently in use, creation timestamp so I know when it was created (recently or years ago), the creator so I can educate them on the need to create profiles with a strong password, and the text description so (hopefully) I can have some idea what this profile is used for.

SELECT authorization_name, password_expiration_interval, special_authorities,

group_profile_name, supplemental_group_list,status, last_used_timestamp,

creation_timestamp, user_creator, text_description

from QSYS2.USER_INFO

WHERE user_default_password = 'YES'

ORDER BY STATUS;

Yes, I could have gotten all of this information by first starting with ANZDFTPWD and then running Display User Profile (DSPUSRPRF) on each individual profile…but why?

Tip #5: Automate Your Analysis

I realize that much of the management of profiles is automated or you want to have a report appear in your inbox and not have to remember to generate it yourself. Not a problem. You can add SQL to a CL program using the Run SQL (RUNSQL) command explained here. Numerous implementation tips and examples can be found on the Internet. If I can write a CL program to run several SQL statements, generate reports, and send them to my Inbox, you can too!

Tip #6: Learn SQL by Copying Others

I admit it. The way I learned to use SQL was by copying an example someone else wrote. Most of my initial SQL experience started by using the examples provided within ACS. Then I got a bit more brave and modified them to meet my needs. But by far, most of my SQL inspiration has come from Scott Forstie from IBM. You, too, can get tips and inspiration from Scott as well as Tim Rowe here.

Summary

Why modernize the management of your user profiles? One reason is to make sure you’re always analyzing the most current information. Another is to make analysis easier by combining relevant information rather than having to manually combine the output from several IBM commands. Finally, modernizing your processes using IBM i Services often allows you to tap into resources that may not be familiar with IBM i. I worked with a Python programmer at one client who was very happy to write a script to access QSYS2.USER_INFO to gather user profile information from multiple partitions and consolidate it into one database. The consolidated database was then used by their compliance group to perform user profile analysis, which had previously been a very arduous and often error-filled task. I understand change is hard, but I encourage you to give these methods a try.