Search notes:

Effects of ALTER SESSION SET CURRENT_SCHEMA in AUTHID DEFINER and AUTHID CURRENT_USER packages

If execute immediate is used to execute a alter session set current_schema statement in stored procedure with authid definer, it does not affect the environment in which the execute immediate statement was executed.
However, in an authid current_user procedure (or package), it is.
This is demonstrated in the following research.

Creating two users/schemas

We need two schemas, one for the packages and one that executes the stored procuders. 
create user pkg_owner identified by pw default tablespace data quota unlimited on data;
create user pkg_user  identified by pw default tablespace data quota unlimited on data;

grant create session, create procedure to pkg_owner;
grant create session                   to pkg_user;
Github repository Oracle-patterns, path: /SQL/statement/nouns/session/alter/set/current_schema/execute-immediate/create-users.sql

Packages

The two packages. One is defined with authid definer the other with authid current_user. Both have exactly one procedure: do_something.
In both cases, do_something uses execute immediate to
After executing execute immediate, the procedure prints the «actual» current schema yet again. 
create package PKG_DEFINER AUTHID DEFINER
as
   procedure do_something;
end;
/

create package PKG_CURRENT_USER AUTHID CURRENT_USER
as
   procedure do_something;
end;
/

create package body PKG_DEFINER
as
   procedure do_something as
   begin

      dbms_output.put_line('pkg_definer');

      execute immediate q'{
          begin
             dbms_output.put_line('  exec immediate: current schema before setting current_schema: ' || sys_context('userenv', 'current_schema'));
             execute immediate 'alter session set current_schema = pkg_user';
             dbms_output.put_line('  exec immediate: current schema after  setting current_schema: ' || sys_context('userenv', 'current_schema'));
          end;
      }';

   --
   -- Turns out, the alter session set current_schema in the
   -- previous execute immediate affects only the statement being
   -- executed in the execute statement. It does not change
   -- the enclosing scope.
   --
      dbms_output.put_line('  current schema after execute immediate:                       ' || sys_context('userenv', 'current_schema'));

   end do_something;
end pkg_definer;
/

create package body PKG_CURRENT_USER
as
   procedure do_something as
   begin

      
      dbms_output.put_line('pkg_current_user');

      execute immediate q'{
          begin
             dbms_output.put_line('  exec immediate: current schema before setting current_schema: ' || sys_context('userenv', 'current_schema'));
             execute immediate 'alter session set current_schema = pkg_owner';
             dbms_output.put_line('  exec immediate: current schema after  setting current_schema: ' || sys_context('userenv', 'current_schema'));
          end;
      }';

   --
   -- Turns out, the alter session set current_schema in the
   -- previous execute immediate affects affects also the scope
   -- of the enclosing  scope
   --
      dbms_output.put_line('  current schema after execute immediate:                       ' || sys_context('userenv', 'current_schema'));

   end do_something;
end pkg_current_user;
/



grant execute on pkg_definer      to pkg_user;
grant execute on pkg_current_user to pkg_user;
Github repository Oracle-patterns, path: /SQL/statement/nouns/session/alter/set/current_schema/execute-immediate/pkg-owner.sql

Executing the stored procedures

Execute do_something in both packages: 
begin
   pkg_owner.pkg_definer     .do_something;
   pkg_owner.pkg_current_user.do_something;
end;
/
Github repository Oracle-patterns, path: /SQL/statement/nouns/session/alter/set/current_schema/execute-immediate/pkg-user.sql
It prints 
kg_definer
  exec immediate: current schema before setting current_schema: PKG_OWNER
  exec immediate: current schema after  setting current_schema: PKG_USER
  current schema after execute immediate:                       PKG_OWNER
pkg_current_user
  exec immediate: current schema before setting current_schema: PKG_USER
  exec immediate: current schema after  setting current_schema: PKG_OWNER
  current schema after execute immediate:                       PKG_OWNER

Index