I login as a user which has SYS privilege and ran below sql to check some of session variables so far everything looks good and expected.
select substr(user,1,5) as "user", substr(sys_context('USERENV','SESSION_USER'),1,5) as SESSION_USER, substr(sys_context('USERENV','CURRENT_SCHEMA'),1,5) as CURRENT_SCHEMA
, substr(sys_context('USERENV','CURRENT_USER'),1,5) as CURRENT_USER, sys_context('USERENV','CURRENT_SCHEMAID') as CURRENT_SCHEMAID
from dual;
user SESSI CURRE CURRE CURRENT_SCHEMAID
----- ----- ----- ----- -----------------
SYS SYS SYS SYS 0
Now connect as different user and run same SQL it still show SYS I expect to see tst is this expected?
conn tst/tst
select substr(user,1,5) as "user", substr(sys_context('USERENV','SESSION_USER'),1,5) as SESSION_USER, substr(sys_context('USERENV','CURRENT_SCHEMA'),1,5) as CURRENT_SCHEMA
, substr(sys_context('USERENV','CURRENT_USER'),1,5) as CURRENT_USER, sys_context('USERENV','CURRENT_SCHEMAID') as CURRENT_SCHEMAID
from dual;
user SESSI CURRE CURRE CURRENT_SCHEMAID
----- ----- ----- ----- -----------------
SYS SYS SYS SYS 0
thanks in advance for your time.
Sorry I can't reproduce
SQL> select substr(user,1,5) as "user",
2 substr(sys_context('USERENV','SESSION_USER'),1,5) as SESSION_USER,
3 substr(sys_context('USERENV','CURRENT_SCHEMA'),1,5) as CURRENT_SCHEMA ,
4 substr(sys_context('USERENV','CURRENT_USER'),1,5) as CURRENT_USER,
5 sys_context('USERENV','CURRENT_SCHEMAID') as CURRENT_SCHEMAID
6 from dual;
user SESSION_USER CURRENT_SCHEMA CURRENT_USER CURRENT_SCHEMAID
-------------------- -------------------- -------------------- -------------------- --------------------
SYS SYS SYS SYS 0
SQL> conn scott/tiger
Connected.
SQL> select substr(user,1,5) as "user",
2 substr(sys_context('USERENV','SESSION_USER'),1,5) as SESSION_USER,
3 substr(sys_context('USERENV','CURRENT_SCHEMA'),1,5) as CURRENT_SCHEMA ,
4 substr(sys_context('USERENV','CURRENT_USER'),1,5) as CURRENT_USER,
5 sys_context('USERENV','CURRENT_SCHEMAID') as CURRENT_SCHEMAID
6 from dual;
user SESSION_USER CURRENT_SCHEMA CURRENT_USER CURRENT_SCHEMAID
-------------------- -------------------- -------------------- -------------------- --------------------
SCOTT SCOTT SCOTT SCOTT 127
Send us your output from SQL Plus exactly like we have please.
Is "TST" is granted SYSDBA you would see this, but otherwise not.