The final scenario is a situation where you thought you secured an object, but users are able to access it anyway. You’ve looked but can’t determine where the users are getting their authority. As you can see in Figure 9.6, it appears that the HR_INFO/SALARY file should be secure!
Figure 9.6: Permissions on HR_INFO/SALARY file.
Because the Authority Collection includes the source of a user’s current authority, this is the perfect utility to help you debug this issue.
If the system is at IBM i 7.4, you can start the Authority Collection on the HR_INFO/SALARY file itself. If at IBM i 7.3, you can start the Authority Collection on the users that are gaining access but shouldn’t be. In this case, I configured Authority Collection on the file and then displayed the results. Figure 9.7 is a perfect example of my admonition that I gave at the end of chapter 6, where I said that if the results don’t make sense, you need to look at less information or more. This is a case of needing more information. For example, how can DEVELOPER have access to the SALARY file if the current authority source is *PUBLIC yet *PUBLIC is *EXCLUDE?
Figure 9.7: Showing the source of the access of HR_INFO/SALARY file.
Expanding the columns being examined produces the source of access. JOE is getting access by being a member of PROD_OWNER, and DEVELOPER is gaining access via a program that adopts named IADOPT in library CJW.
Figure 9.8: The expanded results show the source of access.
You can then expand the columns further to determine which profile owns the adopting program and that owner’s authority and take the necessary steps to lock down that program. As far as JOE goes, this is an example of the risks you take when application users are a member of the profile that owns the application; they have *ALL authority to all application objects!
Which Objects Have Authority Collection Configured?
To determine which objects have Authority Collection configured, run the following for objects in libraries:
And run this to determine the IFS objects.
Note
Depending on how many IFS objects you have, this may take some time to run. You may wish to modify the starting path name to be something other than ‘/’.
I cover this topic extensively in chapter 17 of IBM i Security Administration and Compliance, Third Edition, but I wanted to mention a few things here. That chapter was originally written prior to Authority Collection being available. Some might wonder if I’d replace that guidance with the use of Authority Collection. The answer is no. To configure Authority Collection on all application objects, even if you limit it to just database files, I believe would be very overwhelming.
However, there is a place in this process for Authority Collection. Specifically, when reworking an entire application, the most likely to fail are processes that access data from outside of application interfaces, such as ODBC access from a Windows server or a secure file transfer to an outside source or a data transfer to a user’s workstation. Once you discover these processes, you have to determine what authority to grant the profile to the database file in use, and that’s not always obvious. If my client was running IBM i 7.4, I’d turn on Authority Collection on the objects identified as being accessed via non-application interfaces (ODBC, DDM, etc.) to determine how much authority is required and to make sure no other profiles need access. If they were running IBM i 7.3, I’d still use Authority Collection and turn it on for the profiles you’ve identified as accessing data outside of application interfaces. (The only difference between these two approaches is that you’re not going to get the full list of profiles accessing the object when you turn on Authority Collection for the profiles versus turning it on for the object itself.)
How do I identify which profiles are accessing application data with some interface other than the application itself? As I describe in IBM i Security Administration and Compliance, Third Edition, it’s a matter of turning on object auditing on the database files and examining the ZR and ZC audit journal entries to find the entries that are using a program other than an application program to read and/or update a database file. The other method you can use (if for some reason you can’t turn on object auditing) is to examine the GR and GS audit journal entries. I don’t describe examining those in the other book, so I’ll describe them here.
This method is far less precise than using object auditing, but it will help you identify profiles using “external” interfaces (connections made from another system), perhaps a Windows server, a user’s workstation, or even another system running IBM i. Then you can use Authority Collection for those profiles to discover the details of what they’re accessing. One warning, however: You should not rely on this method as your sole means of investigation if you have two or more applications on your system that are “intertwined,” meaning their use of data overlaps and they often pass data between them and/or share files and data. If you have this situation, you typically know who you are. This method will not catch a program from Application A accessing a database file in Application B’s libraries, for example, and missing that information could mean that Application A could fail when it tries to access Application B’s data if you haven’t made accommodation for that access.
This method uses the GR (Generic Record) audit journal entries. These entries are generated if you’ve changed the default setting for anything in Application Administration (Function Usage). Little did IBM realize that, when they added the ability to control who can use ODBC using settings in App Admin, they were also providing an easy way to find out who’s using ODBC! Same with FTP and DDM/DRDA.
GR audit journal entries are generated when you have either *SECCFG or *SECURITY specified in the QAUDLVL system value and have modified any Function Usage entry. Even if you simply change the default setting for who can access New Nav, for example, the system generates a GR record to log that the setting of the function was checked. While I couldn’t care less whether it was checked or not, what this provides is a simple way to determine who’s accessing the system via ODBC (function name QIBM_DB_ZDA), FTP (function names QIBM_QTMF%), and DDM/DRDA (function name QIBM_DB_DDMDRDA). The following gets all of these entries. I’ve included the IP address of the request in case you need to determine where the access originated.
Get the distinct set of access by profile.
To discover the use of SSH and QShell, use the GS audit journal entries and look for the job name of ‘SSHD’ and ‘QSHSH’ for QShell. (I describe in chapter 12 how to use the GS audit journal entries to discover who’s using SSH.)
Once you find the profiles accessing the system using these various interfaces, you can turn on Authority Collection (as described in chapter 6) to determine which objects they’re accessing. If it’s files in the application you’re securing, you know to add them to the list of profiles for which you’ll need to grant authority.
Again, if you’re reworking an entire application, I encourage you to read chapter 17 of IBM i Security and Compliance, Third Edition for more hints to help make your project a success.
We’ll use the same methodology for discovering who needs access to a database file as we did when securing a directory. First, identify the file(s) you want to secure. Then configure Authority Collection to start collecting information on who’s accessing the file. Finally, start the collection (STRAUTCOL *OBJAUTCOL) if it hasn’t already been started.
IBM did not provide separate commands for enabling Authority Collection on an IFS object versus an object in a library. You must specify all objects using a pathname. This is not difficult once you understand the convention. It’s /QSYS.lib/your-library-name.lib/object-name.object type. See Figure 9.4 for an example.
Figure 9.4: Configure Authority Collection for a database file.
For objects in a library, you can use either the QSYS2.AUTHORITY_COLLECTION_LIBRARIES or QSYS2AUTHORITY_COLLECTION_OBJECT IBM i Service. One performs better than the other, depending on how many entries have been collected for the whole system as well as the specific entries you’re looking for. Personally, I don’t consider this a performance-critical function, so to me you can choose either one, and it really doesn’t matter; the results will be the same. I’ve used the LIBRARIES Service to get the results in Figure 9.5. For an explanation of why I’ve removed the “adopted authority” and “check any authority” fields, see chapter 6.
Figure 9.5: Distinct list of access of the HR_INFO/SALARY file.
Now that you have the list of profiles accessing the file you want to secure, you need to determine how you’re going to secure it.
Application Access
If the file being secured is associated with an application, you’ll see many, many more entries—those of end users accessing the file via the application. To secure the file, I’d suggest you identify which application programs are accessing the file and change them to adopt authority. In other words, set the User profile attribute of the program to be *OWNER. If the owner of the program is the same as the owner of the file and the program adopts, any caller of the program will have *ALL authority to the file. The easiest way to determine whether any application programs accessed the file would be to turn back to the Authority Collection. The following gets a list of the programs accessing HR_INFO/SALARY that are in the PROD_LIB library.
This is the list of programs you’ll want to consider setting to user profile *OWNER.
Other Access
For access to the file other than through the application, I suggest you attach an authorization list and authorize the profiles accessing the file via non-application interfaces to that list, granting only as much authority as identified in the Authority Collection. While you’ll have to take an outage to attach the list, once attached, you can grant (and revoke) authority to the list at any time. Otherwise, you’ll have to wait until the file isn’t locked to modify the authority to the file. (A database file is locked when it’s opened.) When attaching the list, I’d also set the *PUBLIC authority of the file to *AUTL, meaning that the *PUBLIC authority of the file is to come from the authorization list. That way, when you’re ready to set the *PUBLIC authority to *EXCLUDE, you can do it and not wait for the file to be unlocked. More importantly, if things go terribly wrong for some reason after setting it to *EXCLUDE, you can quickly set it back to the previous value and, again, not have to wait until the file isn’t in use.
Now that you have the results—in other words, the list of profiles accessing the directory—how do you set the authorities on the directory? Obviously, the profiles currently have access, but the goal is to secure the directory (as in set the directory to be *PUBLIC *EXCLUDE). Unless the profile has *ALLOBJ special authority and you intend to leave it with *ALLOBJ, you’re going to have to ensure these profiles have sufficient authority. Here are some options:
If you find the directory is accessed by only one profile, change the ownership of the directory to that profile and *PUBLIC to DTAAUT(*EXCLUDE) OBJAUT(*NONE), and you’re done. Seriously. It’s that easy. This is often the case with that “transitory file” scenario.
If it’s a situation where the directory contains six months (or choose your timeframe) of data and several people read that data, have the directory be owned by the process writing the information into the directory. This will help eliminate excess private authorities when the owner of the directory and objects in the directory don’t match. Then, create an authorization list for the directory, put the users accessing the objects in a group, and authorize the group with only the authority required, which is likely to be *USE. Assuming the application was written to inherit authorities from the directory, as objects are created they will be secured with the authorization list to which the group is authorized, again reducing private authorities using an authorization list. If you grant a private authority to each individual requiring access, each of the profiles will have a private authority to each of the objects created into that directory—in other words, lots of private authorities, which you want to avoid so that your Save Security Data (SAVSECDTA) time doesn’t get out of control. Also, this approach is terribly difficult to maintain, especially when you need to add a new user. If you use the “group profile authorized to an authorization list” approach, all you have to do when someone new needs access is to place them into the group. Once you’ve changed the ownership and secured the directory with the authorization list, don’t forget to set *PUBLIC to DTAAUT(*EXCLUDE) OBJAUT(*NONE).
If you find that several users are creating, deleting, and reading the information (in other words, the results are showing that they need the equivalent of *ALL authority), put those users in a group profile and change the ownership of the directory to the group. Then set *PUBLIC to DTAAUT(*EXCLUDE) OBJAUT(*NONE).
I rarely take the proactive approach to secure the objects in the directory. It’s usually sufficient to secure the directory and leave its contents as is.
Most of my clients have one or more directories that contain confidential information. It may be image files of canceled checks or medical images that are kept permanently. Or a directory that contains a running year’s worth of receipts with Personally Identifiable Information (PII) or invoices or a directory that holds what I call a “transitory” file. It’s usually a stream file (*STMF) that’s regularly created by a scheduled job. The file typically contains payroll or tax or bank information that’s then sent off the system via some method of secure file transfer. Do any of these examples sound familiar?
To successfully reduce access to a directory, we must answer two questions: Who’s accessing the directory? How much authority do they need?
For those of you running IBM i 7.4 and later, this exercise couldn’t be easier. For this scenario, we’re going to use Authority Collection. Earlier I discussed how you can use Authority Collection to reduce a specific user’s authority to an object by configuring Authority Collection for that user and determining how much authority they require to the objects they touched. In IBM i 7.4, IBM gave us another option and that’s to configure Authority Collection on a specific object. This option allows us to determine all of the users accessing the object as well as the authority required.
Configuring Authority Collection on an object is a two-step process. First, you must configure Authority Collection on your object. Figure 9.1 shows how to configure Authority Collection for the ‘/home/carol’ directory. I’ve just configured the collection for the directory itself, but you can start it for the objects in the directory as well as subdirectories if you specify *ALL for the Directory subtree parameter.
Figure 9.1: Configure Authority Collection for objects using the CHGAUTCOL command.
Next, if the Collection isn’t already started for objects, you must run the Start Authority Collection (STRAUTCOL) command. IBM i 7.4 added a new parameter to this command to specify whether you’re working with user profiles or objects. In this case, we’re (obviously) working with objects. See Figure 9.2.
Figure 9.2: Start the Authority Collection if you haven’t done so previously.
Now you wait and let the Collection collect. How long do you wait before you look at the results? Again, as with user profiles, it depends. If you’re securing a directory that you believe is only being written to by a scheduled job, let the job run and then look. But if you don’t know, I’d let the collection run at least over a month-end before you can conclude your investigation is complete. (Obviously, you can look at the collection prior to month-end; just don’t end it prior to that.)
To view the results of the Collection, you’ll use QSYS2.AUTHORITY_COLLECTION_FSOBJ (You need to use the view that corresponds to the type of object you’ve been collecting information on.)
Figure 9.3 shows the results. Notice that it only shows information for the directory you started the collection on, not the higher-level directories such as ‘/’ or ‘/YYY’ as the collection did when you were analyzing a profile’s authority requirements via Authority Collection for profiles. The collection is very literal. If you said to start on the path ‘/directory/subdirectory’, it will only collect for that specific path.
Figure 9.3: Distinct accesses of the ‘/home/carol’ directory.
Finally, dynamic SQL is used quite frequently today, and it’s often the case that the dynamic SQL statement within the program should adopt authority just as the program it’s in adopts authority. The problem is that that doesn’t happen by default. Even when the program’s User profile attribute is set to *OWNER, the dynamic SQL doesn’t adopt. To cause it to adopt, you must set the Dynamic user profile attribute to *OWNER when the program is compiled. To discover a program’s Dynamic user profile attribute, run this for OPM programs:
And use this for modules bound into ILE and service programs:
The Dynamic user profile attribute defaults to *USER, meaning that the Dynamic SQL statement doesn’t adopt. If you want the statement to adopt, it used to be that your only opportunity to change it from *USER to *OWNER was when you compiled the program. If you didn’t set it on the compile, you were forced to recompile the program. For many reasons, organizations may not want or be able to recompile. Thanks to the team at IBM Rochester, you now have another option. The QSYS2.SWAP_DYNUSRPRF procedure takes the current Dynamic user profile setting of the program that you pass in and flips it to be the other setting.
For example, if you discovered that DYNSQL200 in PROD_LIB has the Dynamic user profile set to *USER and you need it to be *OWNER, you could run the following and the result would be that the Dynamic user profile attribute would be set to *OWNER.
A nuisance that you may not be aware of (I wasn’t until writing this book!) is the fact that the naming convention used in your static SQL (as opposed to Dynamic SQL) determines whether the program or procedure containing the static SQL adopts. This is true whether it’s created using SQL or a CL command such as Create SQL RPG Program (CRTSQLRPG).
As shown in Figure 8.1, the User profile parameter defaults to *NAMING. A little-known fact is that if the static SQL uses SQL naming (objects are specified using the library.object naming convention), the User profile attribute of the program or procedure will be set to *OWNER, meaning that the program or procedure will adopt! If the static SQL uses the System (*SYS) naming convention, the User profile attribute will be set to *USER, meaning that the program or procedure will not adopt. When running the CRTSQLxxx commands, you can override the User profile parameter, but most people don’t.
Figure 8.1: Whether the program adopts authority depends on the naming convention used by the static SQL contained in the program when using the default of *NAMING for User profile.
I hope this raises the awareness of the importance of having objects owned by the proper profiles. It should also underscore my recommendation that application-owning profiles and developers should not have *ALLOBJ special authority! I also hope that this drives home the importance of monitoring for new programs that adopt authority and that appropriate code-promotion procedures be in place and followed.
Likewise, objects shouldn’t be created into production libraries except via change management. I had a client that looked for programs being created on production by anyone other than their change-management profile, and whoever did it had to have an emergency change-management ticket or justify their action! But let’s look at a different scenario. The IBM-provided library QGPL has been fondly called Q Garbage Pail Library because it ships with *PUBLIC set to *CHANGE and a lot of “stuff” gets created into it and rarely cleaned up. So this client decided to take control of QGPL. As part of that project, they started to monitor the *FILE, *PGM, and *DTAARA objects created into QGPL so they could determine who created them and how the objects were getting created. To get this information, they examined the CO (Creation of Object) audit journal entries. (To generate these entries, you’ll need to have *CREATE specified in the QAUDLVL system value.) This gave them the programs running when the object was created as well as the creator. The entry also contains an Entry Type field. A value of N means it’s a new object. R indicates the object replaced an existing version.
Deletion of Objects (DO)
When an object gets deleted (accidentally or intentionally), it’s often difficult to determine how it happened. The DO audit journal entry is just the ticket to get the information you need. (To generate these entries, you’ll need *DELETE in the QAUDLVL system value.) I’d much rather use the audit journal table functions, and although the systools.audit_journal_do table function does exist, I’m going to use a different technique to underscore how easy these table functions are to use compared to what I’m about to show you.
This example shows how you can run CL commands within Run SQL Scripts. You can run any command simply by specifying CL: at the beginning of the line. Pretty cool, huh? While that’s cool, the field names that you have to use and the fact that this is a two-step process rather than one swift one underscores why you should use the audit journal entry table functions whenever they’re available. Note that some fields aren’t available when using CPYAUDJRNE (such as the combined job field and the fields that provide the detailed description of the entry type). Also, I had to modify my JDBC settings in Run SQL Scripts (specifically the Translation settings) to enable the pathname to get translated into a readable format. I hope you see that it’s much easier to use the audit journal table functions rather than CPYAUDJRNE when the table function is available. (I’ll get down off my soapbox now.)
This example shows how you can find who deleted a *STMF (specified in the WHERE clause using the DOPNM field). As such, I’m looking at the pathname field rather than the object and object library fields.
More Information
I’ll be discussing how you can automate checking and changing the authorities on objects in a later chapter. Please continue to the next chapter to see how to use IBM i Services to manage adopted authority. I’ll discuss using Authority Collection for Objects to help you reduce the permissions assigned to objects after that. Finally, if you need best-practices recommendations for object authority settings, see chapter 6 in IBM i Security and Compliance, Third Edition, and for a step-by-step approach for reworking an entire application’s security scheme, see chapter 17
The commands Print Adopting Objects (PRTADPOBJ) and Display Program Adopt (DSPPGMADP) provide lists of programs and service programs that adopt a specific profile. PRTADPOBJ only goes to print, so I find that command to be of limited use. DSPPGMADP provides more options for output, including an outfile, but why not get the information you need all at once rather than sending it to an outfile and then running a query over it? QSYS2.PROGRAM_INFO provides the mechanism to do just that.
For example, you may want to get a list of all objects that adopt QSECOFR.
And while examining the objects that adopt QSECOFR is good, you’ll likely want to examine the objects that adopt any profile with *ALLOBJ. Note that I don’t select profiles whose group has been assigned *ALLOBJ, just profiles directly assigned *ALLOBJ. When the authority-checking algorithm checks for adopted authority, it only checks for sufficient authority of the program owner, not the program owner’s group(s).
It’s quite possible to have vendor applications on your system that have programs that adopt QSECOFR. While you can ask your vendor to justify why they’ve configured their application that way, you really have no control over them. But what you do have control over are your own applications. So you may want to focus on programs that adopt authority that are being promoted into your own application libraries. I’ve had clients that require special review of programs that are owned by and adopt a profile with *ALLOBJ and without approval are not allowed to be promoted. To ensure this process is being followed, you can look for programs that have been created in the last week (for example) that adopt an *ALLOBJ profile and make sure there’s a corresponding promotion request/approval. What you may want to do on a weekly basis then is to look for programs promoted to your production libraries.
Application Programs
Just because a program is configured to adopt authority doesn’t mean that the program owner has to have *ALLOBJ special authority. In fact, I encourage those who are reworking their applications to provide authority via adopted authority to configure the application’s owner profile with no special authorities and especially not *ALLOBJ. Having all application objects owned by the same profile and configuring the application programs to adopt should provide sufficient authority. Therefore, you’ll want to regularly check to ensure all programs in the application library are owned correctly and configured to adopt.
As you may see in this previous example, there could be exceptions to this, such as the initial program that puts up the menu and command line. I encourage organizations to not have the initial program adopt to prevent adopted authority from flowing out to the menu’s command line and to commands that should be run using the end user’s authority, not adopted authority.
In that case, the programs should be configured with the User profile attribute set to *USER (so the program doesn’t adopt) as well as the Use adopted authority parameter set to *NO (so the program doesn’t inherit the adopted authority from a previously called program). You can verify those values with this:
Several other audit journal entry types can also help you debug issues or help you verify that processes are being followed when it comes to objects. These include OW (Ownership Changes), CA (Authority Changes), CO (Creation of Objects), and DO (Deletion of Objects). How you use these are limited only by your imagination, but here’s an example of each to get you started.
Ownership Changes (OW)
Perhaps your earlier analysis via OBJECT_STATISTICS showed that one or more objects weren’t owned correctly and you need to determine who changed the ownership and when. To determine that, use the OW audit journal entry. (To generate these entries, either *SECRUN or *SECURITY must be specified in the QAUDLVL system value.) Of course, the change would have had to happen within the timeframe of the audit journal receivers that you have on the system, but assuming that’s the case, this will give you the information you need. The following lists the entries where the previous owner is PROD_OWNER but was changed within the last week.
Authority Changes (CA)
As with ownership changes, if authorities are changed, you’ll want to know. I didn’t discuss this for the OW entries, but you’ll typically want to narrow down your search because the operating system generates OW and CA entries every time you create an object. In other words, there are lots of OW and CA entries and you won’t want to wade through them all to get to the information you’re looking for. (To generate CA audit entries, you’ll have to include either *SECRUN or *SECURITY in the QAUDLVL system value.)
The following will list the changes to authority for objects in the PROD_LIB for all profiles except the profile running the change-management process. Most organizations that I’ve worked with want authorities configured such that developers can’t change authority to production objects. All changes should come through an approved change-management process; therefore, you probably don’t want to list the changes made by the change-management software. I strive to eliminate “known” or “approved” entries and focus on the exceptions so that it’s easier to identify things that shouldn’t have occurred, so I’ve eliminated the authority changes made by the profile running change management.
Getting to the right audit journal entry in this case is usually very easy. You have the timeframe in the joblog, so you can narrow down which audit journal entries are collected. You may not even have to add a WHERE clause to your SQL to get to the right entry.
You have three options for getting to the information you need:
Option #1: Green-screen commands
The object receiving the first authority failure in the failing process is in the AFONAM field and the library in the AFOLIB field. The program running at the time of the process is AFPGMLIB/AFPGM. With this information, you should be able to resolve the authority failure.
Option #2: The systools.audit_journal_af table function
AF is one of the audit types provided as an SQL table function, so alternatively (and preferably) from Run SQL Scripts you can run this:
Option #3: New Nav
Click on the padlock icon and choose Audit Journaling. In the Configure view window, click on Detail View, and then select Authority Failure (AF) and specify the date/time range. Click OK. The entries created during that timeframe will be displayed. See Figure 7.5. The Object Name and Object Library columns will provide the object on which the failure occurred, and the Program Library and Program Name columns will tell you what program was running at the time the audit journal entry was generated.
Figure 7.5: The Detail View of the AF entries should provide the details you need to debug the issue.
Insufficient Special Authority
In addition to this example, another situation where you might get an AF entry is due to a user not having sufficient special authority. In this case, the AFVIOL (or violation_type) will be K. See Figure 7.6.
Figure 7.6: If the Violation_Type is K, it’s an indication that the user doesn’t have the correct special authority.
The action that the user was trying to take will be the OBJECT_NAME. In this case, the user tried to run CRTUSRPRF. Unfortunately, the audit entry doesn’t list which special authority the user is lacking. The easiest way to determine that is to look in Appendix D of the IBM i Security Reference manual. Appendix D lists all CL commands provided by the operating system along with the authorities required to run them. When a special authority is required, there will be a footnote associated with the command that documents the special authority(s) required.
What if the authority failure occurred on an IFS object? No problem; we just need to look at different fields to get the path name. Path names are rarely 10 characters long, so rather than show a truncated name, the object name field will contain *N when you’re looking at the audit journal via CPYAUDJRNE and STRSQL but will be NULL if you’re using Run SQL Scripts. Here’s the SQL for Run SQL Scripts:
