Are you using IBM’s Authority Collection tool to find possible security exposures? If not, you should be! Now you can use IBM Db2 Web Query to quickly and easily analyze the large amount of collection detail.

The Authority Collection feature available in IBM i 7.3 is robust and simple to use. The collection provides incredible insight to your applications without creating a performance impact. Coupled with IBM i Services via SQL, it’s a powerful system administration toolset. Unfortunately, depending on the complexity of your application or the number of active user collections you are running, the collection can contain a massive amount of detail.

How can security administrators quickly analyze this information? Sure, they can execute individual SQL statements against the collection using IBM’s Access Client Solutions (ACS). Unfortunately, this can quickly become overwhelming and complex. Where should they start? If they’re part of a security team, is everyone running the same SQL? How bad is their security model? Do the administrators understand the intricacies of the applications used? What if they need to distribute filtered lists to other staff or management to resolve issues? They need a quick method to view (or allow others to view) the data without having to understand SQL.

Goal

This is part one of a two-part article. We will explore the power of IBM’s Db2 Web Query Business Intelligence and reporting product. The Web Query metadata layer provides a simple way to access the IBM system-provided SQL views, procedures, and functions. Utilizing Web Query’s dashboard and reporting functionality, now we have an easy way to analyze these collections as well as address some security administration tasks.

Scope

The web offers excellent articles on how to start Authority Collection for a user and other related tasks. In addition, you will find examples, videos, and Redbooks on using Db2 Web Query. The Web Query team can be reached via email at This email address is being protected from spambots. You need JavaScript enabled to view it. or by visiting the Web Query resource page. Setup and activation of Web Query are beyond the scope of this article; however, the EZ-Install package is a simple way to get started and even includes a presentation on how to integrate IBM i Services. With respect to using IBM systems services, these are documented in the IBM Knowledge Center and no real installation is required. Utilizing the examples shown, your security team will have another weapon in their security arsenal.

Tightening Security: Where to Start

I assume you already have an active authority collection available. Security administrators can address excessive authority in many ways. Typically, this is done at the user level, but they may wish to do this by schema, authorization list, object type, etc. The coordinated dashboard functionality in Web Query provides a good place to start. The primary dashboard is Collection Dashboard by User (highlighted in Figure 1).

Figure 1: The Authority Collection Analyzer

In Figure 2, the coordinated dashboard displays a summary of the authority collection detail by user profile. You may select any user from the coordinated dropdown to retrieve their specific metrics. This provides a “view” as to where you may wish to start addressing security exposures.

Figure 2: Authority Collection Dashboard by User

Dashboard Overview

The dashboard comprises nine summary graphs and reports. This user currently has 1,611 entries in the authority collection.

• The Authority Source graph summarizes where the system found the authority. Notice that 94 percent of the time, this user accessed objects via *PUBLIC authority. Is this expected for this user, or are there too many objects available to *PUBLIC?
• The Entries by Object Type chart breaks down the types of objects found and an entry count for each.
• The Entries by Schema chart provides a view for the user’s schemas. Selecting an element drills into all entries for the specific schema, regardless of user (see Figure 4).
• The Required Authority summary is a critical component to determine just how much authority the application is requesting. Notice there are five entries where the user needs *OBJEXIST. Select the link and drill into all collection entries for the user.
• The Authority Failures summary indicates the user did not have enough authority. This allows the administrator to quickly determine if too much authority was removed after a security change or if the user is attempting to access things he shouldn’t be. Selecting the link opens a report on these specific failures, thus providing details on the objects affected.
• Entry Totals by Job presents a list of the jobs that created the collection entries for the user. This is helpful to determine whether batch or interactive tasks are creating the most entries.
• The Adopted Authority summary lists the entry counts of the levels of adopted authority given. Along with the Adopting Program list, we can see where the user obtained the authorities. Selecting the link creates an adopted authority summary report for the user by schema and objects.
• The Check Any Authority entries count is typically used by many system commands, APIs, and services (more on this later in the article).
• The Adopting Program summary lists totals for each program that the user used to adopt authority. Selecting the link drills down into all the adopted entries for this user.

You may select from several of the user profile hyperlinks to drill into a detail report to show all collection entries for this profile (see Figure 6).

In Figure 3, the coordinated dashboard shows a summary of the authority collection detail by schema. You may select a schema from the coordinated dropdown.

Figure 3: Authority Collection Dashboard by Schema

In this example, the schema has 6,171 entries within the authority collection. Clicking on the schema name hyperlink allows us to drill into a detail report to show all entries for this schema. Click on the Entries by Authorization Name chart elements to drill into all entries for the specific user profile (see Figure 6). These can be easily customized to meet specific needs.

In Figure 4, the coordinated dashboard shows a summary of the authority collection detail by objects secured by an authorization list. You may select an authorization list from the coordinated dropdown.

Figure 4: Collection Dashboard by Authorization List

In the example, this authorization list has 6,680 entries in the authority collection. Click on the authorization list name hyperlink to drill into a detail report to show all entries for this list, or click on the Entries by Schema chart elements to drill into all entries for the specific schema.

It’s in the Details

From any of the dashboards, you can drill down into tailored Authority Collection detail reports. These reports are created as “Active” reports (a unique report type in Web Query). This allows easy filtering and sorting as well as other functionality. In addition, the reports have further drilldown capabilities, thus providing quick access without disrupting your flow.

Reporting drilldowns Include…

• Authority collection detail for a user
• Authority collection detail for an object
• Authority collection detail for a schema
• Authority collection detail by authorization list
• Authority collection failure detail for a user
• Authority collection adopted detail for a user
• Adopted authority summary for a user
• Adopted authority summary for a user and object
• Required authority summary for a user
• Required authority summary for an object
• Required authority summary for user and object
• Required authority summary for a table’s dependents (index, view, and logical)
• Required authority summary for a table’s dependents by user only
• Objects secured by authorization list
• Users in an authorization list
• Users in a group profile
• Display database relations (i.e., DSPDBR)
• Display column attributes (i.e., DSPFFD)
• Display table key detail
• Object authority (i.e., DSPOBJAUT) (Figure 5)

Each of these then in turn allow you to open additional pages, providing different avenues to view collection data in the way that works best for your specific environment.

Figure 5: Display Object Authority

Figure 6: Authority Collection entries for a user

Direct Options

If you prefer to go directly to the data rather than use the dashboards, this report (Figure 7) shows all users who have any collection entries and whether there is an active collection running for them. Clicking the hyperlink will drill into all entries for the specific user profile.

Figure 7: User Profiles with Authority Collection detail

The Authority Collection Statistics report provides summary detail for all of the collection entries by schema, object, and type. Drilldowns allow for displaying the object’s authority, displaying all of the object’s collection entries, and retrieving the required authority summary for all users.

Figure 8: Authority Collection Statistics report

Note

Reviewing the collection entries, I noticed several entries where “Required Authority” was equal to *ALL. I ended up finding more of these than I anticipated. Additional research revealed that a number of these are related to IBM adopting authorities. These are typically used by many system commands, APIs, and services. So, on some of the reports, I chose to filter out the Check Any Authority column. This may help you determine if these items need to be addressed as the user may not really need *ALL authority.

Summary

IBM continues to add system interfaces that are accessible via SQL. Now you can use these same interfaces by defining metadata for them in Web Query.

Together, IBM i 7.3 and Db2 Web Query provide a very effective way to help you secure your system. I’ve shown you some examples available for your use. With a little effort, it is possible to create a complete, custom-tailored security portal for your company by just accessing the system services APIs.

What’s Next?

In part two, we will explore the Authority Collection Analyzer toolset. I will provide an overview of each summary and detail report as well as some of the system services. In addition, I will present some security scenarios and ways to quickly resolve each.

The “Code”

I have created the requisite metadata, dashboards, and reports for these examples. These will all be provided in part two. They are essentially plug and play. I trust these will provide you with a great starting point. Once you become familiar, you can easily modify or enhance them to suit your needs.