The idea of this Gist is to take a step beyond access to a live view of who has *ALLOBJ user special authority, to also being able to see how the topic is changing over time. With the addition of a time dimension, clients can more easily focus on the delta changes.

Tracking ALLOBJ users through time

-- ===============================================
-- Title: iSee how to track *ALLOBJ users
-- ===============================================
-- Date  : November 5, 2020
-- Author: Scott Forstie
-- Use   : iSee video series with Tim Rowe
--

-- ======================================================================================================
--
-- Start of 1 time setup
--
-- ======================================================================================================

create schema coolstuff;

create or replace table coolstuff.allobj_users for system name allobjusrs as (
select authorization_name as user_name, user_creator, status,
       no_password_indicator, previous_signon, user_default_password,
       text_description
  from qsys2.user_info
  where special_authorities like '%*ALLOBJ%' or authorization_name
        in (select user_profile_name
            from qsys2.group_profile_entries
            where group_profile_name in (select authorization_name
                  from qsys2.user_info
                  where special_authorities like '%*ALLOBJ%'))
  order by authorization_name
) with data on replace delete rows;

--
-- At this point, allobj_users will contain all users who currently have *ALLOBJ directly or indirectly via a Group Profile
--
select *
  from coolstuff.allobj_users;
stop;

--
-- Temporalize the *ALLOBJ tracking table
--
alter table coolstuff.allobj_users
  add column row_change timestamp not null implicitly hidden generated
  always for each row on update as row change timestamp
  add column row_birth timestamp(12) not null implicitly hidden
  generated always as row begin
  add column row_death timestamp(12) not null implicitly hidden
  generated always as row end
  add column transaction_time timestamp(12) implicitly hidden
  generated always as transaction start id
  add period system_time (row_birth, row_death);

create table coolstuff.allobj_users_hist like coolstuff.allobj_users;

alter table coolstuff.allobj_users
  add versioning use history table coolstuff.allobj_users_hist;
stop;

--
-- SQL DML that will insert users have been granted *ALLOBJ
--
MERGE INTO coolstuff.allobj_users au USING 
  (select authorization_name, user_creator, status, no_password_indicator, 
          previous_signon, user_default_password, text_description
  from qsys2.user_info
  where special_authorities like '%*ALLOBJ%' or authorization_name
        in (select user_profile_name from qsys2.group_profile_entries
            where group_profile_name in (select authorization_name
                  from qsys2.user_info where special_authorities like '%*ALLOBJ%'))
  order by authorization_name) live ON 
     au.user_name = live.authorization_name
  WHEN NOT MATCHED THEN 
    INSERT VALUES (live.authorization_name,    live.user_creator, live.status,
                   live.no_password_indicator, live.previous_signon, 
                   live.user_default_password, live.text_description) ;

--
-- SQL DML that will remove users that no longer have *ALLOBJ (good going!)
--
delete from coolstuff.allobj_users where user_name in (
  select a.user_name
    from coolstuff.allobj_users a
         left exception join (
  select authorization_name from qsys2.user_info
    where special_authorities like '%*ALLOBJ%' or
          authorization_name in (select user_profile_name from qsys2.group_profile_entries
                                 where group_profile_name in 
                                 (select authorization_name from qsys2.user_info where special_authorities like '%*ALLOBJ%')))
  on authorization_name = a.user_name);
stop;

--
-- Package the DML into an SQL procedure for ease of use, maintenance, and more
--
create or replace procedure coolstuff.maintain_allobj_users ()
    set option usrprf = *user
begin
  merge into coolstuff.allobj_users au using (
      select authorization_name, user_creator, status, no_password_indicator, previous_signon, user_default_password,
             text_description
        from qsys2.user_info
        where special_authorities like '%*ALLOBJ%' or authorization_name in (select user_profile_name
                  from qsys2.group_profile_entries
                  where group_profile_name in (select authorization_name
                        from qsys2.user_info
                        where special_authorities like '%*ALLOBJ%'))
        order by authorization_name
    ) live
    on au.user_name = live.authorization_name
    when not matched then insert values (live.authorization_name, live.user_creator, live.status,
        live.no_password_indicator, live.previous_signon, live.user_default_password, live.text_description);
  delete from coolstuff.allobj_users where user_name in (select a.user_name
      from coolstuff.allobj_users a
           left exception join (
               select authorization_name
                 from qsys2.user_info
                 where special_authorities like '%*ALLOBJ%' or authorization_name in (select user_profile_name
                           from qsys2.group_profile_entries
                           where group_profile_name in (select authorization_name
                                 from qsys2.user_info
                                 where special_authorities like '%*ALLOBJ%'))
             )
             on authorization_name = a.user_name);
end;

stop;
-- The work process can be called via:
-- 1) Directly using the SQL CALL statement
-- 2) Directly using the RUNSQL CL command
-- 3) Scheduled, using a scheduled job and RUNSQL
call coolstuff.maintain_allobj_users();
-- Or
cl: RUNSQL SQL('call coolstuff.maintain_allobj_users()') COMMIT(*NONE) NAMING(*SQL); 
-- Or
cl: ADDJOBSCDE JOB(COOLALLOBJ) CMD(RUNSQL SQL('call coolstuff.maintain_allobj_users()') COMMIT(*NONE) NAMING(*SQL)) FRQ(*WEEKLY) SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME(235500)   ;
stop;

-- ======================================================================================================
--
-- End of 1 time setup
--
-- ======================================================================================================


--
-- review the scheduled job detail
--
select command_string, last_successful_submission_timestamp, next_submission_date, sj.*
  from qsys2.scheduled_job_info sj
  where scheduled_job_name = 'COOLALLOBJ';
stop;

--
-- Compare the current number of users with *ALLOBJ with the number from a day ago
--
with today (allobj_count) as (
       select count(*)
         from coolstuff.allobj_users
     ),
     yesterday (allobj_count) as (
       select count(*) as current_allobj_yesterday
         from coolstuff.allobj_users for system_time as of current timestamp - 1 day
     )
  select t.allobj_count as today_allobj_count, 
         y.allobj_count as yesterday_allobj_count
    from today t, yesterday y;
stop;

--
-- Compare the current number of users with *ALLOBJ with the number from a week ago
--
with today (allobj_count) as (
       select count(*)
         from coolstuff.allobj_users
     ),
     before (allobj_count) as (
       select count(*)  
         from coolstuff.allobj_users for system_time as of current timestamp - 7 days
     )
  select t.allobj_count as today_allobj_count, 
         y.allobj_count as yesterday_allobj_count
    from today t, before y;
stop;


--
-- which users had *ALLOBJ a week ago, but do not have it today?
--
with today (un) as (
       select user_name
         from coolstuff.allobj_users
     ),
     before (un) as (
       select user_name  
         from coolstuff.allobj_users for system_time as of current timestamp - 7 days
     )
  select y.un
    from before y left exception join today t
     on y.un = t.un;
stop;
     
--
-- which users did not have *ALLOBJ a week ago, but have it today?
--
with today (un, creator, default_pw) as (
       select user_name, user_creator, user_default_password
         from coolstuff.allobj_users
     ),
     before (un) as (
       select user_name  
         from coolstuff.allobj_users for system_time as of current timestamp - 7 days
     )
  select t.un
    from today t left exception join before y
     on y.un = t.un;
     
stop;

--
-- which users did not have *ALLOBJ a week ago, but have it today? (expanded report)
--
with today (un, creator, default_pw) as (
       select user_name, user_creator, user_default_password
         from coolstuff.allobj_users
     ),
     before (un) as (
       select user_name  
         from coolstuff.allobj_users for system_time as of current timestamp - 7 days
     )
  select t.un, creator, default_pw
    from today t left exception join before y
     on y.un = t.un;
     
stop;