This example shows the power of the REGEXP_REPLACE built-in function, when combined with IBM i Services for security and dynamic SQL.

Remove *IOSYSCFG from users and groups

-- Author: Scott Forstie
-- Email : forstie@us.ibm.com
-- Date  : July 28, 2019
--
-- Subtract '*IOSYSCFG from all users by producing the CHGUSRPRF command
-- necessary to get the job done
--
with iosyscfg_users (user_name) as (
       select authorization_name
         from qsys2.user_info
         where special_authorities like '%*IOSYSCFG%'
               and user_creator <> '*IBM'
     ),
     iosyscfg_groups (group_name) as (
       select distinct (group_profile_name)
         from qsys2.group_profile_entries
         where group_profile_name in (select authorization_name
               from qsys2.user_info
               where special_authorities like '%*IOSYSCFG%'
                     and user_creator <> '*IBM')
     )
  select user_name
    from iosyscfg_users
  union all
    select group_name
      from iosyscfg_groups;                      
                              
stop;

--
-- description: Retain list (those that get to retain *IOSYSCFG)
--
declare global temporary table retain_list (un) as
      (values ('GIORDANO'), ('SCOTTF'))
      with data
  with replace;
stop;

--
-- description: Generate CL Commands
--
with iosyscfg_users (user_name) as (
       select authorization_name
         from qsys2.user_info
         where special_authorities like '%*IOSYSCFG%'
               and user_creator <> '*IBM'
               and authorization_name not in (select un from session.retain_list)
     ),
     iosyscfg_groups (group_name) as (
       select distinct (group_profile_name)
         from qsys2.group_profile_entries
         where group_profile_name in (select authorization_name
               from qsys2.user_info
               where special_authorities like '%*IOSYSCFG%'
                     and user_creator <> '*IBM')
     )
select 'QSYS/CHGUSRPRF USRPRF(' concat authorization_name concat ') SPCAUT('
    concat regexp_replace(special_authorities, '\*IOSYSCFG', '', 1, 1, '')
    concat ')'
    from qsys2.user_info where authorization_name in (select user_name from iosyscfg_users)
    union all
select 'QSYS/CHGUSRPRF USRPRF(' concat authorization_name concat ') SPCAUT('
    concat regexp_replace(special_authorities, '\*IOSYSCFG', '', 1, 1, '')
    concat ')'
    from qsys2.user_info where authorization_name in (select group_name from iosyscfg_groups);
                        
--
-- description: Proceduralize it
--   
   
cl: crtlib coolstuff;

create or replace procedure coolstuff.subtract_iosyscfg ()
begin
  declare not_found condition for '02000';
  declare error_count integer default 0;
  declare at_end int default 0;
  declare v_cmd_text varchar(1000);
  declare iosyscfg_users_cursor cursor for 
with iosyscfg_users (user_name) as (
       select authorization_name
         from qsys2.user_info
         where special_authorities like '%*IOSYSCFG%'
               and user_creator <> '*IBM'
               and authorization_name not in (select un from session.retain_list)
     ),
     iosyscfg_groups (group_name) as (
       select distinct (group_profile_name)
         from qsys2.group_profile_entries
         where group_profile_name in (select authorization_name
               from qsys2.user_info
               where special_authorities like '%*IOSYSCFG%'
                     and user_creator <> '*IBM')
     )
select 'QSYS/CHGUSRPRF USRPRF(' concat authorization_name concat ') SPCAUT('
    concat regexp_replace(special_authorities, '\*IOSYSCFG', '', 1, 1, '')
    concat ')'
    from qsys2.user_info where authorization_name in (select user_name from iosyscfg_users)
    union all
select 'QSYS/CHGUSRPRF USRPRF(' concat authorization_name concat ') SPCAUT('
    concat regexp_replace(special_authorities, '\*IOSYSCFG', '', 1, 1, '')
    concat ')'
    from qsys2.user_info where authorization_name in (select group_name from iosyscfg_groups);

  declare continue handler for sqlexception set error_count = error_count + 1;
  begin
    declare continue handler for sqlexception set at_end = 1;
    declare continue handler for not_found set at_end = 1;
    open iosyscfg_users_cursor;
    fetch from iosyscfg_users_cursor into v_cmd_text;
    while (at_end = 0) do
      call qsys2.qcmdexc(v_cmd_text);
      fetch from iosyscfg_users_cursor into v_cmd_text;
    end while;
    close iosyscfg_users_cursor;
  end;
end;

stop;

call coolstuff.subtract_iosyscfg ();