Wed, Feb
3 New Articles

TechTip: Streamline Authority Collection with IBM Db2 Web Query, Part 1

IBM i (OS/400, i5/OS)
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.


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.


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).

TechTip: Streamline Authority Collection with IBM Db2 Web Query, Part 1 - 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.

TechTip: Streamline Authority Collection with IBM Db2 Web Query, Part 1 - Figure 2

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.

TechTip: Streamline Authority Collection with IBM Db2 Web Query, Part 1 - Figure 3 

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.

TechTip: Streamline Authority Collection with IBM Db2 Web Query, Part 1 - Figure 4 

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.

TechTip: Streamline Authority Collection with IBM Db2 Web Query, Part 1 - Figure 5 

Figure 5: Display Object Authority

TechTip: Streamline Authority Collection with IBM Db2 Web Query, Part 1 

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.

TechTip: Streamline Authority Collection with IBM Db2 Web Query, Part 1 - Figure 7 

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.

TechTip: Streamline Authority Collection with IBM Db2 Web Query, Part 1 - Figure 8 

Figure 8: Authority Collection Statistics report


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.


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.

Bill Vientos is Sr. Systems Admin/IBM i Engineer for Scoular, Inc. He is a specialist in IBM i and IBM Power Systems solutions, including virtualization and storage solutions. In addition, he's an experienced programmer utilizing ILE RPG, SQL, CL, and other languages. In the past, Bill was an active participant in the IBM LUG (Large User Group) and held different positions on the LUG board. His passion is system integration and leveraging IBM i and the IBM Power platform. Feel free to contact him directly at This email address is being protected from spambots. You need JavaScript enabled to view it..



Support MC Press Online

$0.00 Raised:

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More 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.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY 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.

  • SB HelpSystems ROBOT GenericForms 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.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA 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:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA 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.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra 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: