Consider Modernizing Your Approach to IBM i Security, Too
November 10, 2021 Alex Woodie
Many IBM i users are facing the reality that they need to modernize their IBM i applications, especially those based on monolithic blocks of fixed-format RPG. But other aspects of the IBM i experience can use some renovating too, including how organizations manage their security settings. Luckily, in the last few Technology Refreshes to IBM i, IBM has provided a slew of new SQL-based services for doing just that.
Traditionally on IBM i, administrators had two main approaches for viewing and changing security settings. They could view and control the settings directly using commands, often automated through CL programs. Or they could call an API that IBM created to view or change a security setting, often through RPG or another programming language. If you’re using a third-party vendor tool to manage security on the IBM i server, it likely uses those APIs.
But recently, IBM has been offering a third way: short SQL programs called IBM i Services that can be accessed and executed using the Run SQL Scripts component of Access Client Solutions (ACS) interface as well as through the New Navigator.
In the most recent Technology Refreshes for IBM i 7.3 TR11 and 7.4 TR5 unveiled in early September, IBM added 14 new IBM i Services, including an even dozen that target the audit journal. Similarly, the TRs issued in April included 10 new IBM i Services and half-a-dozen enhanced ones, while the TRs in October 2020 included nearly 30 new or improved services. You get the picture.
The good news is that IBM has added hundreds of these IBM i Services over the years, which lower the barrier of entry for managing various aspects of the IBM i server. Previously complex processes that involve commands or APIs can now be executed using SQL that users can change to adapt to their specific needs. And the good news is that you don’t have to be an SQL expert to use them.
One enthusiastic adopter of the new security controls available through IBM i Services is Carol Woodbury, a longtime IBM i expert who is now the CTO, president, and co-founder of DXR Security. Woodbury shared her views on the SQL methods during a session at the recent POWERUp conference hosted by COMMON.
“One of the things that I like about these interfaces is that they are typically faster, especially faster than getting information via CL,” Woodbury said during the session. “It facilitates getting information via various languages, not just RPG, which is great because the 20-somethings know these other languages, and it’s easier to have them write to these SQL views. And quite frankly people who have no knowledge of IBM i can pick this up and get this information.”
The SQL approach also provides security information in a better and more usable format, she said. For example, instead of having security information sent to an outfile (as some commands do), where it’s up to the user to parse it and figure out what it says, the new SQL methods will run the query and provide the results directly. The new SQL services also support long-name fields as well as the cryptic 10-character fields, whereas CL approaches only support the 10-character fields, she said.
Woodbury provided an overview of some of the top SQL services that give systems administrators and security professionals a modern approach to IBM i security. She structured her presentation according to the three-legged stool that comprises security on the IBM i server: user profiles, object level security, and system values.
IBM provides a number of SQL-based methods for monitoring and managing various attributes of user profiles, including the existence of special authorities, membership in group profiles, and password settings.
For these activities, Woodbury likes to turn to the USER_INFO service, which is in the QSYS2 library and has been around for a few years. Running this select statement can return a number of details, including the number of users with default passwords, the number of profiles with special authorities, such as ALLOBJ.
The User_Info statement also allows the administrators to filter the views, such as listing the profiles with ALLOBJ who have default passwords. “So I get a much better picture and I can start addressing things in a much more intelligent and risk-based way,” she said.
This approach also has the added benefit of always being up-to-date. With the traditional approach to analyzing user profiles, the administrator is required to periodically run the command and have the results pushed to an outfile. If the command hasn’t been run lately, or it failed to run at night, then the data will be out of date.
“The cool thing about using these views is that the data is up to date–always up to date and immediately up to date,” she said. “You don’t have to worry about working with old data.”
Woodbury also spoke highly of the CHANGE_USER_PROFILE table function that debuted in SYSTOOLS in the spring TRs that allows the administrator to change a user profile, which is handy in the context of disabling inactive profiles. “What you do with this is actually change the user profile from the status of enabled to the status of disabled, based on a range of whatever you say for your where clause,” she said. You can find more information about that table function here.
There is also a SQL alternative to the Display Authorized User command, which is called the GROUP_PROFILE_ENTRIES view. This view will allow the administrator to see which user profiles are associated with certain group profiles, which can be important if an employee is promoted within the company or changes jobs.
“If you’re not reviewing on a regular basis, it’s likely that users are staying in the groups for their past jobs, and now have access or continue to have access to information that they really no longer have access to in their new position,” Woodbury said. “So that’s why you really want to review group membership on a regular basis.”
The USER_INFO service is a real time-saver. “It’s up to you what all you can get out of USER_INFO. Really the sky’s the limit,” she said. But if it’s taking a bit long to return, Woodbury recommended trying USER_INFO_BASIC, which IBM introduced with the spring TRs.
The standard command for getting started with object-level security is a command called Display Object Security. The new SQL-based alternative to that is a view called OBJECT_PRIVILEGES, which displays the same information as the command. Similarly, the IFS_OBJECT_PRIVILEGES can provide the same information for the IFS.
One task this view can be used for is finding where libraries are not owned by the user for which it is named for, which could be a sign that something is amiss. The OBJECT_PRIVILEGES view can help the administrator track those down, Woodbury said.
Authorization lists can also be tracked using views. For example, the new AUTHORIZATION_LIST_USER_INFO and AUTHORIZATION_LIST_INFO views can return the same information as the Display Authorization List command.
“It just makes it much, much easier to process this information and review it then if I’m looking at a spool file or a 5250 screen and trying to decipher it that way,” Woodbury said. “It’s so much easier to look at the information, and then if I needed to, I could filter it out and just see things like files or I can sort in different orders.
Woodbury highlighted the SECURITY_INFO view that IBM introduced this April as a more effective way to view detailed information about security settings. “It’s really the equivalent or the aggregation of the data that you would get if you ran the Display Security Attributes command,” she said.
However, there are some values that can be found nowhere else but in the SECURITY_INFO view. For example, if the Q security level or minimum password level has been changed but has not yet taken effect because it requires an IPL, that information will show here, Woodbury said. “So that’s the only place on the system that you can tell if there is actually a change is going to be made after the next IPL,” she said.
SECURITY_INFO also includes information that used to be gathered using the Display Security Auditing command, including what the auditing system values are set to, what the currently attached receiver is, as well as the network attributes, Woodbury said.
“Now, they were kind of missing a few of the security-relevant system values in the first round, so this most recent technology refresh now has all the security relevant attributes,” she said. “It does produce a quite interesting and quite complete list of those security-relevant system values and in settings. I know that auditors like to review system values settings. That might be a way to get the list of the security relevant system values and network attributes, auditing values and so forth in an easy format for your auditors to consume.”
Woodbury also highlighted the new table views having to do with the audit journal that IBM added to its SYSTOOLS offering in the fall 2021 TRs. The new views provide table functions unique to specific audit journal entry types, including use of command strings (CD type), create object (CO), user profile changes (CP), delete operation (DO), environmental variable (EV), generic record (GR), action to system value (SV), and several audit journal entry types related to Db2 Mirror operation (MO, M6, M7, M8, and M9).
“So in total IBM has provided us with…11 of those two letter code audit journal entry types for us,” Woodbury said. “There’s a lot more that they could do. They’re looking for input as to what to do next. They want to give the most priority to the ones that are used the most so I encourage you to send feedback to Scott Forstie on this issue and help him prioritize what he and his team should be providing for us next.”
The Authority Collection, which IBM added in IBM i 7.3, is another area where a modern SQL-based gives the IBM i administrator the power to know about the objects that users touch and what authority they need. In fact, the Authority Collection requires the use of SQL, Woodbury points out. Users should follow the examples that IBM provides in ACS, and customize them to their liking, she said.
IBM has provided a similar capability for determining authority needed for IFS objects, “which again is incredibly handy because, number one, the IFS seems to be a bit of a mystery to a lot of people. I get it,” Woodbury said. “And so again it will list out exactly what authority is required for people to touch a particular directory.”
Woodbury mentioned several other SQL views and functions that can help the modern IBM i administrator keep things running smoothly on her platform. The important thing to note, she said, is that administrators don’t have to be SQL experts to use these new tools.
“I want to encourage you, if you’re not an SQL expert, don’t run away,” she said. “Don’t be frightened by this because there are lot of resources that we have available to us that will help us even if we are not SQL experts. I am not an SQL expert in any way shape or form. I’m always looking for help when I want to do something new.”
This is also a good reason to stay up on the latest ACS release, she added.
Db2 PTF Group Enhancements Target Web Services, Audit Journal
Surprise! It’s IBM i Technology Refresh Time
Db2 And SQL Services Get Upgrades With TRs
I’m so glad to see this article. Finally, someone talking about modern approaches to system administration. It was so refreshing to see this instead of yet another article full of 5250 screencaps. Please.. more content like this is important if the platform is to survive and thrive! IBM is doing it’s part, finally, and now it’s up to us to adopt new tools.