Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

"ALTER SESSION SET CURRENT_SCHEMA" not working after "ALTER SESSION SET CONTAINER"

York35Jul 19 2021 — edited Jul 19 2021

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
This post has been answered by JohnWatson2 on Jul 19 2021
Jump to Answer
Comments
Post Details
Added on Jul 19 2021
2 comments
2,246 views