If you use adopted authority, how do you avoid allowing code that you call from taking a free ride on your elevated authority? One answer lies within this gist...

controlling adopted authority.sql

-- =========================================================================================
-- 
-- Adopted authority... if you use it, how do you control it?
--
-- =========================================================================================
-- Q: How can you avoid propagating authority to code you need to call?
--
-- A: MODIFY INVOCATION AUTHORITY ATTRIBUTES
--    https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzatk/MODINVAU.htm
-- =========================================================================================
--
-- setup
call qsys.create_sql_sample('TOYSTORE');

-- =================================================================
-- no adopted authority
-- ================================================================
create or replace procedure coolstuff.change_sales (
        in p_sales_date date,
        in p_sales_person varchar(20), 
        in p_sales_region varchar(20),
        in p_additional_sales integer)
    program name change1
    set option usrprf = *user, dynusrprf = *user
begin
  declare continue handler for sqlexception
  begin
    declare local_sqlcode integer;
    declare local_sqlstate char(5);
    declare v_message_text varchar(3000) ccsid 37;
    get diagnostics condition 1
        local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate,
        v_message_text = message_text;
    set v_message_text = 'coolstuff.change_sales() - failed with SQLCODE=' concat
          local_sqlcode concat ' SQLSTATE=' concat local_sqlstate concat ' MESSAGE= '
          concat v_message_text;
    call systools.lprintf(v_message_text);
    signal sqlstate 'QZW51'
      set message_text = v_message_text;
  end;
  call systools.lprintf('coolstuff.change_sales() - USER:' concat user concat 
                                          ' CURRENT_USER:' concat current_user);
  -- static SQL, run with authorities of the caller
  update toystore.sales
    set sales = sales + p_additional_sales
    where sales_date   = p_sales_date and
          sales_person = p_sales_person and
          region       = p_sales_region;
end;
 
grant execute on procedure coolstuff.change_sales  to public;

cl:addlible qsysinc;
cl:CRTSRCPF FILE(QTEMP/QCSRC);
cl:addpfm file(qtemp/qcsrc) mbr(MODINVAU);
insert into qtemp.qcsrc values
 (1,010101,'{'), 
 (2,010101,'#include "modinvau.h"                '),  
 (3,010101,'MODINVAU_T modifyme;                 '),  
 (4,010101,'modifyme.option = MODINVAU_SUPPRESS; '),      
 (5,010101,'_MODINVAU(&modifyme);                '),  
 (6,010101,'}');

create or replace procedure coolstuff.do_not_propagate_authority()
  set option usrprf = *owner, dynusrprf = *owner
begin
  call systools.lprintf('coolstuff.do_not_propagate_authority() - USER:' concat user concat 
                                          ' CURRENT_USER:' concat current_user);

  -- This call succeeds because the called procedure (*PGM) adopts the authority of the owner of this procedure (*PGM)
  call coolstuff.change_sales(
     p_sales_date   => date('03/30/1996'), 
     p_sales_person => 'LEE',
     p_sales_region => 'Ontario-North', 
     p_additional_sales => 2);
     
  -- From this point forward, suppress the ability of those programs I call from adopting authority
  include qtemp/qcsrc(modinvau);

  -- This call fails
  call coolstuff.change_sales(
     p_sales_date   => date('03/30/1996'), 
     p_sales_person => 'LEE',
     p_sales_region => 'Ontario-North', 
     p_additional_sales => 222);
 end;
 
grant execute on procedure coolstuff.do_not_propagate_authority to public;

stop;
          
-- What user profile do I use to test security?
cl:CRTUSRPRF USRPRF(JOEUSER) PASSWORD(db2fori); 
set session authorization joeuser;
call coolstuff.do_not_propagate_authority();

stop;
-- Reconnect to get back to your user profile
-- note that the first update succeeded, and the second update failed
-- because of the call to MODINVAU
select *
  from toystore.sales
  where sales_date = '03/30/1996' and
        sales_person = 'LEE' and
        region = 'Ontario-North';