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

Thank you Scott for your prompt response.

Could you elaborate a little more on this private authority stuff. We did run a test with a different profile that had *ALLOBJ authority to kind of pass all authorities on all objects. It did give us the "required authority" field data but the "current authority" had everything mentioned in it just bcoz the profile had *ALLOBJ.

So how does a private authority profile differ from an *ALLBOJ account?

Hey Scott,

I understood what you meant by private authority. I'll give it a try and let you know if it worked.

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.