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
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.
More 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.
TRY 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.
Forms 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.
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:
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.
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:
LATEST COMMENTS
MC Press Online