IBM has been leveraging SQL services to gain access to various system functions for several versions now. Often, these services give you access to system administration functions that do not exist in CL commands. For instance, there are many CL commands to view information, but those often only offer a print option to save data for comparison or review later. But there is often an IBM I service offering that will let you run an SQL statement and get that same data into a file or exportable format.
These SQL services are being added to, or enhanced, all the time with the DB2 PTF groups and the Technology Refresh (TR) updates.
https://www.ibm.com/support/pages/ibm-i-services-sql
This series will give a tech note on utilizing some of these IBM I services as a systems administrator.
Today’s topic is about some additional security information that you can gather using SQL
A few weeks ago, we looked at some security settings and information that you might need to gather for an audit. We included some user profile and object authority examples in that article as well. Today, we will take a deeper dive into some of the security offerings with the IBM i SQL services that can help you to investigate authorities on your system.
Let’s take another look at the QSYS2.USER_INFO service again to gather some deeper information about our users on the system. You might need to gather a list of users to evaluate their authority levels, looking for any users that might have too much. The USER_INFO service gives you all the details about user profiles user class, any special authorities granted to that user, and the group profile information.
Here’s an example statement:
SELECT AUTHORIZATION_NAME,
USER_CLASS_NAME,
SPECIAL_AUTHORITIES,
GROUP_PROFILE_NAME,
SUPPLEMENTAL_GROUP_LIST,
OWNER,
GROUP_AUTHORITY,
GROUP_AUTHORITY_TYPE,
GROUP_MEMBER_INDICATOR,
USER_OWNER
FROM QSYS2.USER_INFO;
Using this statement, you can get an output about the privileges of every user on the system (assuming you have at least *SECADM authority). Here is a sample output, which can be saved into a spreadsheet for review if desired:
Taking that example a bit further, you could include a WHERE clause to select just *SECADM or *SECOFR users if you only want to see those classes. Another option might be to list users that have certain special authorities, such as *IOSYSCFG. (This could help to determine if any users have more authority than their specified user class.)
Now, we will review gathering object authority information on your system. In our previous article, we had an example of looking at the authority of *PUBLIC to objects within a specific library. What about checking that same thing for user profiles? You don’t necessarily want *PUBLIC to have permissions to user profiles, as that potentially opens up security risks. To validate that *PUBLIC is excluded, you could run a statement like this:
SELECT *
FROM QSYS2.OBJECT_PRIVILEGES
WHERE SYSTEM_OBJECT_SCHEMA = 'QSYS' AND
OBJECT_TYPE = '*USRPRF' AND
AUTHORIZATION_NAME = '*PUBLIC';
The sample output shows a user profile, and the authority that *PUBLIC has on that user profile:
Try adding another AND in the WHERE clause to exclude any selections where *PUBLIC is already set to ‘*EXCLUDE’. That will give you a narrower result set, and is a good practice exercise!
Extra Credit: Check out the SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART for some additional functionality to review special authorities on your user profiles!
Dealing with security in the IBM i, authorization lists are a powerful method to secure objects. It also helps to ease securing a lot of objects across the system, as you can simply manage the authorization list attribution on an object versus listing user profiles (or group profiles) and the specified authority on objects. We will look at a few examples around authorization lists, starting with checking your objects for the existence of an authorization list.
SELECT SYSTEM_OBJECT_SCHEMA,
SYSTEM_OBJECT_NAME,
AUTHORIZATION_NAME,
OWNER,
AUTHORIZATION_LIST
FROM QSYS2.OBJECT_PRIVILEGES
WHERE SYSTEM_OBJECT_SCHEMA = 'LIBRARY_NAME_HERE';
This gives us a listing of the objects in the specified library and shows whether they are secured by an authorization list. This is a good review to see if all the objects in a library are secured by the same (or perhaps none at all) authorization list.
Word of warning: While you can change the “SYSTEM_OBJECT_SCHEMA” into a LIKE type of clause with a wildcard, it should be done with caution. If you use a wildcard that is too generic (such as ‘A%’ for instance), you might have a long-running query that is going to produce more results than you can really handle (or desire). Instead, try groups of libraries with something akin to ‘LIBRA%’, where you fill in more of the library grouping name to create a smaller subset.
You have reviewed your application libraries and noted that you have an authorization list on your objects. Let’s learn more about that authorization list with a couple of examples below. (Besides, who needs the WRKAUTL command on the command line anyway?)
We know the name of the authorization list, but we don’t know anything else about it yet. But we can get a list of all the objects that authorization list is securing and review them to see if there are potentially more than the libraries you just reviewed.
SELECT *
FROM QSYS2.AUTHORIZATION_LIST_INFO
WHERE AUTHORIZATION_LIST = 'AUTL_LIST_NAME';
This statement gives us a list of all the objects secured by that authorization list.
That might be a lot in your case, so we can change that list up a bit to get a list of libraries that might be on the authorization list (without listing every single object):
SELECT OBJECT_SCHEMA,
COUNT(OBJECT_SCHEMA)
FROM QSYS2.AUTHORIZATION_LIST_INFO
WHERE AUTHORIZATION_LIST = 'AUTL_LIST_NAME'
GROUP BY OBJECT_SCHEMA;
This gives you an output of how many times the authorization list is used in a library. It makes a shorter list but gives you a count on the objects and a list of the libraries containing objects secured by this authorization list.
Now you might ask: What users are listed in the authorization list, and what authorities are they granted? We have another service to review that portion of an authorization list: QSYS2.AUTHORIZATION_LIST_USER_INFO .
Using this statement, we can take a look inside a specific authorization list for the users, as well as their authorization granted by the authorization list. This is a good review to make sure that the users are not granted too much (or too little) authority for their role in the usage of objects on the system.
SELECT *
FROM QSYS2.AUTHORIZATION_LIST_USER_INFO
WHERE AUTHORIZATION_LIST = 'AUTL_LIST_NAME';
In our examples, we have been limiting to a specific authorization list, but these services will return the info for all authorization lists on the system as well. This might be a good list to keep for reference documentation perhaps.
We are just scratching the surface of what these services, as well as several other security related services can gather. Try some other options, and experiment with different SELECT statements to see what else you can gather!