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 ') 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 ') 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,

This is an excellent article !!!

I had a question and a requirement around this same topic.

I have a job that needs to be run under a newly created Service Account/Authorization_Name.
We know all the objects names and library and object types the job touches while it is run. We also know what kind of access is requested on these objects. The count of these objects is close to 200.

Currently the Service Account is been set with all default minimal security settings.

When we run the job and test it with the newly created account, it fails due to authority issue on the first object that it encounter in its path. We fix it and then run the job again and then it fails on another object requiring different set of authority.

Now we want to avoid keep doing this. As this is time consuming. The Authority Collection is also not able to capture all the object authorities because the job stops and fails the moment it encounters 1st authority issue.

I was hoping to get a solution that would first check what is the current authority set for each of these objects for *PUBLIC and if the newly created service account can be or needs to be explicitly added to each of these objects and with what authority.

Please suggest anything that comes to your mind. Thank you.

Hi,
Thank you for the comments.
For the challenge, I wonder if the following could meet the need.
Run the set of actions from a new, independent user profile that has full authorizations to all objects via private authority. (NOT *ALLOBJ)
Make sure the new profile has no group membership.
Have AC started for that new, independent user profile.
Run the set of actions.
Then, end AC.
Now query the AC data, keeping only those rows where the AC detail is not related to operating system activity AND the AC detail is related to authority passing due to the user having the necessary private authority.
Now you have identified the subset of objects where authorization is needed, and the object specific authorization needed.
You could then create an authorization list.
Make sense?
Scott

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.