Display authority collection data

Authority collection captures a significant amount of information that is associated with the authority checking of an object. SQL views are used to display and analyze this information.

Start of change Example queries for all the authority collection views can be accessed through Access Client Solutions (ACS) Insert from Examples. Enter authority_collection in the search box to find these examples. End of change

Authority collection for a user

The SQL view QSYS2.AUTHORITY_COLLECTION is used to display and analyze the authority information that was collected for a user.

IBM Navigator for i shows the authority collection information for a specific user but not in a form that can be queried. IBM Navigator for i has interfaces for authority collection for a user within the Users and Groups function.

There are tasks in the console navigation area under Manage Collections to start, end, display, and delete authority collection for a user.
There are tasks available for a user within the User list to start, end, display, and delete authority collection.
An Authority Collection tab on the Capabilities page of the User properties panel shows the current authority collection status for the user.
There is a table view of the items included in the authority collection. This can be viewed in a web table, or in a client viewer if IBM i Access Client Solutions (ACS) is installed on the PC. The web table also supports Properties and Permissions actions for each object that appears in the list.

The Run SQL Scripts function in ACS can be used to query the authority collection views. See the following SQL query examples that can be run against the view. Additional examples are built into ACS. Select the Insert from Examples feature and type "authority_collection" in the search bar.

Example queries that use the AUTHORITY_COLLECTION view

View authority collection data for USER1.

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

View authority collection data for USER1 for object PAYROLL in library PAYLIB.

SELECT * FROM QSYS2.AUTHORITY_COLLECTION 
   WHERE USER_NAME = 'USER1' AND 
   SYSTEM_OBJECT_NAME = ‘PAYROLL' AND SYSTEM_OBJECT_SCHEMA = ‘PAYLIB’

View authority collection data for USER1, object PAYROLL in PAYLIB, and object type *FILE.

SELECT * FROM QSYS2.AUTHORITY_COLLECTION 
    WHERE USER_NAME = 'USER1' AND 
    SYSTEM_OBJECT_NAME = 'PAYROLL'  AND SYSTEM_OBJECT_SCHEMA = ‘PAYLIB' AND 
    SYSTEM_OBJECT_TYPE = '*FILE'

Example of saving the authority collection information for a user

Save the authority collection data for USER1 to Db2® table MYLIB.MYFILE. Writing the authority collection data to a Db2 table allows the data to be saved and restored to another partition. The Db2 table can then be analyzed by querying the resulting Db2 table.

CREATE TABLE MYLIB.MYFILE AS 
    (SELECT * FROM AUTHORITY_COLLECTION WHERE USER_NAME = 'USER1') WITH DATA

SELECT * FROM MYLIB.MYFILE

Authority collection for objects

The following SQL views are used to display and analyze the authority information that was collected for objects:

QSYS2.AUTHORITY_COLLECTION_OBJECT
QSYS2.AUTHORITY_COLLECTION_LIBRARIES
QSYS2.AUTHORITY_COLLECTION_FSOBJ
QSYS2.AUTHORITY_COLLECTION_DLO

IBM Navigator for i shows the authority collection information for specific objects but not in a form that can be queried. IBM Navigator for i has interfaces for authority collection for objects.

Within the File Systems function and the Security function there are tasks in the console navigation area under Authority Collection for Objects to manage authority collection for objects.
Within an object list there are Authority Collection tasks for an object to change the authority collection value, display the information collected, and delete the information collected.
The Security tab on the object's properties panel shows whether the object is currently included in the authority collection.

The Run SQL Scripts function in ACS can be used to query the authority collection views. See the following SQL query examples that can be run against the view.

Example queries that use the AUTHORITY_COLLECTION_OBJECT view

View data in the authority collection repository for objects, specific object (PAYROLL) of object type *FILE in library PAYLIB.

SELECT * FROM QSYS2.AUTHORITY_COLLECTION_OBJECT 
    WHERE SYSTEM_OBJECT_SCHEMA = ‘PAYLIB’ AND
    SYSTEM_OBJECT_TYPE = ‘*FILE’ AND SYSTEM_OBJECT_NAME = ‘PAYROLL’

View data in the authority collection repository for objects, all objects of object type *FILE in library PAYLIB that begins with ‘PAY’.

SELECT * FROM QSYS2.AUTHORITY_COLLECTION_OBJECT 
    WHERE SYSTEM_OBJECT_SCHEMA = ‘PAYLIB’ AND SYSTEM_OBJECT_TYPE = ‘*FILE’ AND 
    SYSTEM_OBJECT_NAME like ‘PAY%’

Example queries that use the AUTHORITY_COLLECTION_LIBRARIES view

View data in the authority collection repository for objects, all QSYS.LIB objects.

SELECT * FROM QSYS2.AUTHORITY_COLLECTION_LIBRARIES

View data in the authority collection repository for objects, all objects in selected libraries.

SELECT * FROM QSYS2.AUTHORITY_COLLECTION_LIBRARIES 
   WHERE SYSTEM_OBJECT_SCHEMA IN (‘MYLIB1’, ‘MYLIB2’)

Example queries that use the AUTHORITY_COLLECTION_FSOBJ view

View data in the authority collection repository for objects, all objects in the "root" (/), QOpenSys, and user-defined file systems.

SELECT * FROM QSYS2.AUTHORITY_COLLECTION_FSOBJ

View data in the authority collection repository for objects, specific object in the "root" (/), QOpenSys, and user-defined file systems.

SELECT * FROM QSYS2.AUTHORITY_COLLECTION_FSOBJ WHERE PATH_NAME = ‘/mydir/mystmf’

Example queries that use the AUTHORITY_COLLECTION_DLO view

View data in the authority collection repository for objects, all document library objects (*DOC and *FLR object types).

SELECT * FROM QSYS2.AUTHORITY_COLLECTION_DLO

View data in the authority collection repository for objects, specific document object.

SELECT * FROM QSYS2.AUTHORITY_COLLECTION_DLO WHERE PATH_NAME = ‘/QDLS/QDIADOCS/NEWDOC’

Example of saving authority collection information for objects

Saving the authority collection data for objects requires three tables. Save the authority collection data to tables MYLIB.LIBOBJFILE, MYLIB.FSOBJFILE, MYLIB.DLOOBJFILE. Writing the authority collection data to Db2 tables allows the data to be saved and restored to another partition. The Db2 tables can then be analyzed by querying the resulting Db2 tables.


CREATE TABLE MYLIB.LIBOBJFILE AS (SELECT * FROM AUTHORITY_COLLECTION_LIBRARIES) WITH DATA

CREATE TABLE MYLIB.FSOBJFILE AS (SELECT * FROM AUTHORITY_COLLECTION_FSOBJ) WITH DATA

CREATE TABLE MYLIB.DLOOBJFILE AS (SELECT * FROM AUTHORITY_COLLECTION_DLO) WITH DATA