You can never change who you are, or can you?

In PL/SQL statements are parsed as the current connected schema. One can find out what user is parsing a statement by typing (for instance)

 select user from dual;

This is usefull in all kinds of debugging, auditing etc. statements. You cannot really change your identity, for security reasons ofcourse. However one can parse a statement using rights and privileges of another user. This is nothing new, Oracle APEX does it all the time, as descibed here. Basically one uses

 sys.dbms_sys_sql

package to parse the statement using the rights of another user. However, one never becomes that user. This is obvious in APEX. For instance when a trigger writes the USER to an audit column (depending on the Listerner configuration) on sees APEX_PUBLIC_USER. Maybe the trigger is adapted to APEX and uses v('APP_USER'), but that is not what I want to demonstrate. Inspired by this pdf, I wrote some code that allows one to be like Mystique, a chameleon:

create procedure run_statement_as_user(p_statement in clob,
                                       p_user      in dba_users.username%type) 
authid definer is
 l_ret_val      number;
 l_current_user dba_users.username%type := user;
 l_ddl          varchar2(128);
--
 e_not_allowed  exception;
begin
   -- here a list of users one can 'change into'
   -- be carefull, not to list any users that have DBA  like
   -- rights or privileges. 
   --
    if p_user not in ('<MYUSER>') then raise e_not_allowed; end if;
   -- 
   -- switch user to desired p_user
   --
    l_ret_val := sys.kupp$proc.disable_multiprocess;
    sys.kupp$proc.change_user(upper(p_user));
   --   
   -- switch to the user schema
   --
    l_ddl := 'alter session set current_schema ='||upper(p_user);
    execute immediate l_ddl;
   --
   -- Next parse the statement using rights of the p_user
   --
     declare
      l_cursor    number;
      l_ret_val_c number;
      uid         dba_users.user_id%type;
     begin
       l_ret_val_c := sys.dbms_sql.open_cursor;
      --
       select user_id
       into   uid
       from   dba_users
       where  username = user;
      --
       sys.dbms_sys_sql.parse_as_user(  l_ret_val_c
                                      , p_statement
                                      , dbms_sql.native
                                      , uid);
      --
       l_cursor := sys.dbms_sql.execute(l_ret_val_c);
      --
       sys.dbms_sql.close_cursor(l_ret_val_c);
      end;
   --
   -- Done, all remains is switching back to the original user.
   --
   -- switch back to original user
    sys.kupp$proc.change_user(l_current_user);
    l_ret_val :=  sys.kupp$proc.enable_multiprocess;
   -- switch back to schema
    l_ddl := 'alter session set current_schema = '||l_current_user;
    execute immediate l_ddl;
   --
   exception 
    when e_not_allowed then raise;
    when others then 
  sys.kupp$proc.change_user(l_current_user);
         l_ret_val :=  sys.kupp$proc.enable_multiprocess;
  l_ddl := 'alter session set current_schema = '||l_current_user;
         execute immediate l_ddl;
  raise;
end run_statement_as_user;

When the above procedure is compiled in SYS schema any other user that is given executable grant can execute statemens AS IF p_user . So for instance, when connected to the database as user JOHN (who is given executable rights ofcourse):

 
declare
 l_statement varchar2(128):= ' begin 
                                insert into 
                                mytable ( dbuser )
                                values  ( user ); 
                                commit;
                               end;';
 l_user      varchar2(64) := 'RONALD'; 
begin
  sys.run_statement_as_user(l_statement, l_user);
end;

results in the insertion of one record in the RONALD.MYTABLE table, with dbuser having the value RONALD instead of JOHN, the latter is not even required to have any grants on RONALD.MYTABLE.