DB version: 19c
OS : Oracle Linux 7.9
The "ALTER SESSION SET CURRENT_SCHEMA" statement does not seem to take effect after you run "ALTER SESSION SET CONTAINER=".
To demonstrate this, I created 2 users JOHN and DUFFY within a PDB as shown below.
John has a table emp_test which DUFFY does not have SELECT privilege.
But, as shown below, when you execute "ALTER SESSION SET CURRENT_SCHEMA = DUFFY; " and run a SELECT on john.emp_test it seems to work. But, DUFFY does not have access to John.emp_test. The SELECT statement works because, the 'context' is still SYS user
Any idea why the "ALTER SESSION SET CURRENT_SCHEMA" statement shown below is not taking effect ?
Step2 to reproduce the issue
Connect to root container as SYS
SQL> alter session set container=PDB3;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB3
create user john identified by Tiger#35 default tablespace users;
alter user john quota unlimited on users;
grant create session, create table to john;
--Exit from SQL*Plus
-- Connecting to john user directly from SQL*Plus using a user-defined service, pdb3.corpdomain.net which
--- is created from within PDB3 using dbms_service.create_service stored proc
sqlplus john/Tiger#35@10.82.16.217:1521/pdb3.corpdomain.net
SQL> create table emp_test (empid number, empName varchar2(35));
Table created.
SQL> insert into emp_test values (3, 'Keith');
1 row created.
SQL> commit;
Commit complete.
-- Exit from SQL*Plus and connect to PDB3 pluggable DB
Connect to root container as SYS
SQL> alter session set container = PDB3;
Session altered.
Create user duffy identified by wEbman#35 ;
grant create session to duffy;
SQL> alter session set current_schema = duffy;
Session altered.
SQL> show user
USER is "SYS" ------------> This is expected, I think
SQL>
SQL> select * from john.emp_test;
EMPID EMPNAME
---------- -----------------------------------
3 Keith
But, DUFFY did not have SELECT access on JOHN.EMP_TEST table
And I noticed the following. Despite setting current_schema = duffy , the following query still returns SYS
col "Current User" format a25
select sys_context('userenv', 'CURRENT_USER') "Current User" FROM DUAL;
Current User
-------------------------
SYS