This Gist shows how SQL can be used to simplify the task of analyzing Authority Collection runtime authority data.

authority collection - split to the rescue.sql

--
-- Subject: authority collection - split to the rescue
-- Author: Scott Forstie  
-- Date  : September 20, 2021
-- Features Used : This Gist uses split(), right exception join, set session authorization, and authority_collection
--
-- Function - This gist goes beyond visual recognition of the authorization gap, using SQL to compute the different AND 
--            generate the Grant Object Authority (GRTOBJAUT) CL commands needed to bridge the gap.
--
-- In this example, JOEUSER wants to QUERY and UPDATE the TOYSTORE/SALES file.
-- Authority collection can be used to iterate through the process of identifying and granting granular authorities.
--
-- 

-- ===========================  Run these from Run SQL Scripts Connection 1 (Security Admin) =========================== 
cl: crtusrprf joeuser ;
CL: GRTOBJAUT OBJ(TOYSTORE)       OBJTYPE(*LIB) USER(JOEUSER) AUT(*EXECUTE);
CL: GRTOBJAUT OBJ(TOYSTORE/SALES) OBJTYPE(*FILE) USER(JOEUSER) AUT(*OBJOPR);

-- ===========================  Run these from Run SQL Scripts Connection 2 (JOEUSER) =========================== 

set session authorization joeuser;

-- ===========================  Run these from Run SQL Scripts Connection 1 (Security Admin) =========================== 

CL: STRAUTCOL USRPRF(JOEUSER)  LIBINF((TOYSTORE));  --


-- ===========================  Run these from Run SQL Scripts Connection 2 (JOEUSER) =========================== 
select * from toystore.sales;                  -- Fails with [SQL0551] Not authorized to object SALES in TOYSTORE type *FILE.
update toystore.sales set sales = sales + 1;   -- Fails with [SQL0551] Not authorized to object SALES in TOYSTORE type *FILE.

-- ===========================  Run these from Run SQL Scripts Connection 1 (Security Admin) =========================== 

CL: ENDAUTCOL USRPRF(JOEUSER);

-- Authority Collection data
SELECT SYSTEM_OBJECT_NAME, DETAILED_REQUIRED_AUTHORITY,
       DETAILED_CURRENT_AUTHORITY, "CURRENT_USER" as user_name FROM QSYS2.AUTHORITY_COLLECTION
 WHERE AUTHORIZATION_NAME = 'JOEUSER' ;
 
-- JOEUSER's Failure detail
SELECT SYSTEM_OBJECT_NAME, DETAILED_REQUIRED_AUTHORITY,
       DETAILED_CURRENT_AUTHORITY FROM QSYS2.AUTHORITY_COLLECTION
 WHERE AUTHORIZATION_NAME = 'JOEUSER' and
        AUTHORITY_CHECK_SUCCESSFUL = '0';
stop;

-- SPLIT explodes the list of detail and/or current authorities into one row per authority
select ordinal_position, rtrim(element), system_object_schema, system_object_name,
       system_object_type, "CURRENT_USER" as user_name
  from QSYS2.AUTHORITY_COLLECTION, table (
         systools.split(rtrim(DETAILED_CURRENT_AUTHORITY), delimiter => ' ')
       ) b
  where AUTHORIZATION_NAME = 'JOEUSER' and
        AUTHORITY_CHECK_SUCCESSFUL = '0';
stop;

-- SPLIT explodes the list of detail and/or current authorities into one row per authority
select ordinal_position, rtrim(element) as auth_needed, 
       system_object_schema, system_object_name, system_object_type 
  from QSYS2.AUTHORITY_COLLECTION, table (
         systools.split(rtrim(DETAILED_REQUIRED_AUTHORITY), delimiter => ' ')
       ) b
  where AUTHORIZATION_NAME = 'JOEUSER' and
        AUTHORITY_CHECK_SUCCESSFUL = '0';
stop;

--
-- What authority needs to be added?
--
with cur_auth (ord, auth, lib, obj, objt) as (
       select ordinal_position, rtrim(element), system_object_schema, system_object_name,
              system_object_type
         from QSYS2.AUTHORITY_COLLECTION, table (
                systools.split(rtrim(DETAILED_CURRENT_AUTHORITY), delimiter => ' ')
              ) b
         where AUTHORIZATION_NAME = 'JOEUSER' and
               AUTHORITY_CHECK_SUCCESSFUL = '0'
     ),
     req_auth (ord, auth, lib, obj, objt) as (
       select ordinal_position, rtrim(element), system_object_schema, system_object_name,
              system_object_type
         from QSYS2.AUTHORITY_COLLECTION, table (
                systools.split(rtrim(DETAILED_REQUIRED_AUTHORITY), delimiter => ' ')
              ) b
         where AUTHORIZATION_NAME = 'JOEUSER' and
               AUTHORITY_CHECK_SUCCESSFUL = '0'
     )
  select r.lib, r.obj, r.objt, r.auth
    from cur_auth c
         right exception join req_auth r
           -- A right exception join includes only the rows from the right table
           -- that were missing from the inner join.
           on c.auth = r.auth
   group by  r.lib, r.obj, r.objt, r.auth;
stop;


--
-- What authority needs to be added?
--
with cur_auth (ord, auth, lib, obj, objt) as (
       select ordinal_position, rtrim(element), system_object_schema, system_object_name,
              system_object_type
         from QSYS2.AUTHORITY_COLLECTION, table (
                systools.split(rtrim(DETAILED_CURRENT_AUTHORITY), delimiter => ' ')
              ) b
         where AUTHORIZATION_NAME = 'JOEUSER' and AUTHORITY_CHECK_SUCCESSFUL = '0'
     ),
     req_auth (ord, auth, lib, obj, objt) as (
       select ordinal_position, rtrim(element), system_object_schema, system_object_name,
              system_object_type
         from QSYS2.AUTHORITY_COLLECTION, table (
                systools.split(rtrim(DETAILED_REQUIRED_AUTHORITY), delimiter => ' ')
              ) b
         where AUTHORIZATION_NAME = 'JOEUSER' and AUTHORITY_CHECK_SUCCESSFUL = '0'
     )
  select r.lib, r.obj, r.objt, r.auth, 
    'QSYS/GRTOBJAUT OBJ(' concat r.lib concat '/' concat r.obj concat ') OBJTYPE(' concat r.objt 
               concat ') USER(JOEUSER) AUT(' concat r.auth concat ')' as grant_cmd
    from cur_auth c right exception join req_auth r on c.auth = r.auth
   group by  r.lib, r.obj, r.objt, r.auth;
stop;

--
-- A function allows us to hide the complexity and parameterize the query for flexibility of use
--
create or replace function coolstuff.qsys_whats_needed(p_user_name varchar(10) for sbcs data)
returns table (grant_command varchar(1000) for sbcs data)
not fenced 
return
with cur_auth (ord, auth, lib, obj, objt) as (
       select ordinal_position, rtrim(element), system_object_schema, system_object_name,
              system_object_type
         from QSYS2.AUTHORITY_COLLECTION, table (
                systools.split(rtrim(DETAILED_CURRENT_AUTHORITY), delimiter => ' ')
              ) b
         where AUTHORIZATION_NAME = p_user_name and AUTHORITY_CHECK_SUCCESSFUL = '0'
     ),
     req_auth (ord, auth, lib, obj, objt) as (
       select ordinal_position, rtrim(element), system_object_schema, system_object_name,
              system_object_type
         from QSYS2.AUTHORITY_COLLECTION, table (
                systools.split(rtrim(DETAILED_REQUIRED_AUTHORITY), delimiter => ' ')
              ) b
         where AUTHORIZATION_NAME = p_user_name and AUTHORITY_CHECK_SUCCESSFUL = '0'
     )
  select 'QSYS/GRTOBJAUT OBJ(' concat r.lib concat '/' concat r.obj concat ') OBJTYPE(' concat r.objt 
      concat ') USER(' concat p_user_name concat ') AUT(' concat r.auth concat ')' as grant_cmd
    from cur_auth c right exception join req_auth r on c.auth = r.auth
   group by  r.lib, r.obj, r.objt, r.auth;

stop;
--
-- What GRTOBJAUT commands would bridge the authorization gap?
--
select *
  from table (
      coolstuff.qsys_whats_needed('JOEUSER')
    );

stop;

--
-- Discard the authority collection data for JOEUSER
-- 
CL: DLTAUTCOL USRPRF(JOEUSER);

Hello Scott,

The private authority stuff did not work. Like *ALLOBJ, this time all access went thru USER PRIVATE as we provided *ALL access as private authority to the new user profile.