IBM Support

Authority Collection - Sample SQL Queries

Question & Answer


Question

Authority Collection - Sample SQL Queries

Answer


Authority Collection

Authority collection is a capability that is provided as part of the base operating system. At a high level, authority collection captures data that is associated with the run-time authority checking that is built into the IBM i system. This data is logged to a repository provided by the system and interfaces are available to display and analyze the data. The intent of this support is to assist the security administrator and application provider in securing the objects in an application with the lowest level of authority that is required to allow the application to run successfully. By using the authority collection capability to remove or avoid excess authority, the overall security of the objects that are used by an application is improved.

This document provides some sample SQL Queries for analyzing the Authority Collection data.

Note 1 : Refer to document Authority Collection - Getting Started for information on how to get Authority Collection Process started
Note 2: Refer to document Authority Collection File Layout for information of the File Layout for the Authority Collection Process


Sample SQL Queries

At the command line use the following command to get SQL Started started:

STRSQL

1) The following SQL Statement will produce a report that will contain the following information:

User Name
Required Authority
Current Authority
Path Name
For user 'HUGO'
  
SELECT USER_NAME,REQAUTH,CURAUTH,PATH_NAME
FROM QSYS2.AUTHORITY_COLLECTION 
WHERE USER_NAME = 'HUGO'

2) The following SQL Statement will produce a report that will contain the following information:

User Name
Required Authority
Current Authority
Object Name
Object Library
For user 'HUGO'
  
SELECT USER_NAME,REQAUTH,CURAUTH,ONAME,OSCHEMA
FROM QSYS2.AUTHORITY_COLLECTION 
WHERE USER_NAME = 'HUGO'

3) The following SQL Statement will produce a report that will contain the following information:

User Name
Required Authority
Detailed Required Authority
Current Authority
Detailed Required Authority
Object Name
Object Library
For user 'HUGO'
  
SELECT USER_NAME,REQAUTH,DTLREQAUTH,CURAUTH,DTLCURAUTH,ONAME,OSCHEMA
FROM QSYS2.AUTHORITY_COLLECTION 
WHERE USER_NAME = 'HUGO'

4) The following SQL Statement will produce a report that will contain the following information:

User Name
Required Authority
Current Authority
Authority Source
Object Name
Object Library
For user 'HUGO'

  
SELECT USER_NAME,REQAUTH,CURAUTH,AUTHSRC,ONAME,OSCHEMA
FROM QSYS2.AUTHORITY_COLLECTION 
WHERE USER_NAME = 'HUGO'

5) The following SQL Statement will produce a report that will contain the following information:

User Name
Required Authority
Current Authority
Object Name
Object Library
For Object 'PAYROLL'
  
SELECT USER_NAME,REQAUTH,CURAUTH,ONAME,OSCHEMA
FROM QSYS2.AUTHORITY_COLLECTION 
WHERE ONAME = 'PAYROLL'

6) The following SQL Statement will produce a report that will contain the following information:

All fields in the file QSYS2.AUTHORITY_COLLECTION
For user 'HUGO'

SELECT * FROM QSYS2.AUTHORITY_COLLECTION
WHERE USER_NAME = 'HUGO'

7) The following SQL Statement will produce a report that will contain the following information:

All fields in the file QSYS2.AUTHORITY_COLLECTION
For user 'HUGO'
For object Name 'PAYROLL'
In Library 'QGPL'

  
SELECT * FROM QSYS2.AUTHORITY_COLLECTION 
WHERE USER_NAME = 'HUGO' AND 
SYSTEM_OBJECT_NAME = 'PAYROLL' AND SYSTEM_OBJECT_SCHEMA = 'QGPL'

8) The following SQL Statement will produce a report that will contain the following information:

All fields in the file QSYS2.AUTHORITY_COLLECTION
For user 'HUGO'
For object Name 'PAYROLL'
In Library 'QGPL'
For object Type 'FILE'

  
SELECT * FROM QSYS2.AUTHORITY_COLLECTION 
WHERE USER_NAME = 'HUGO' AND 
SYSTEM_OBJECT_NAME = 'PAYROLL' AND SYSTEM_OBJECT_SCHEMA = 'QPGL' AND 
SYSTEM_OBJECT_TYPE = '*FILE'


For additional information on the Authority Collection Process, refer to the Security Reference manual Chapter 10.

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0"}]

Document Information

Modified date:
16 September 2020

UID

nas8N1021227

Page Feedback