05
Tue, Nov
5 New Articles

Modernize Your IBM i User Profile Management

IBM i (OS/400, i5/OS)
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.

Carol Woodbury

 

Carol Woodbury is IBM i Security SME and Senior Advisor to Kisco Systems, a firm focused on providing IBM i security solutions. Carol has over 30 years’ experience with IBM i security, starting her career as Security Team Leader and Chief Engineering Manager for iSeries Security at IBM in Rochester, MN. Since leaving IBM, she has co-founded two companies: SkyView Partners and DXR Security. Her practical experience and her intimate knowledge of the system combine for a unique viewpoint and experience level that cannot be matched.

Carol is known worldwide as an author and award-winning speaker on security technology, specializing in IBM i security topics. She has written seven books on IBM i security, including her two current books, IBM i Security Administration and Compliance, 3rd Edition and Mastering IBM i Security, A Modern, Step-by-Step Approach. Carol has been named an IBM Champion since 2018 and holds her CISSP and CRISC security certifications.


MC Press books written by Carol Woodbury available now on the MC Press Bookstore.

IBM i Security Administration and Compliance: Third Edition
Don't miss the newest edition by the industry’s #1 IBM i security expert.
List Price $71.95

Now On Sale

Mastering IBM i Security Mastering IBM i Security
Get the must-have guide by the industry’s #1 security authority.
List Price $49.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: