Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Henish.

Asked: July 21, 2020 - 7:52 pm UTC

Last updated: July 23, 2020 - 3:58 am UTC

Version: 12.2.0.1.0

Viewed 1000+ times

You Asked

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.

and Connor said...

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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 22, 2020 - 8:53 pm UTC

Thanks for the response.

I am running in SQL-developer 18.4.0.376
Connor McDonald
July 23, 2020 - 3:58 am UTC

Sorry - I can't reproduce in SQL Dev either

I assume you're running this as a *script* in SQL Dev (so that the connect command works)

A reader, July 22, 2020 - 9:09 pm UTC

sorry it is working as expected in SQLplus but not in SQL-developer 18.4.0.376

SQL> ed
Wrote file afiedt.buf

  1  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
SQL> /

user  SESSI CURRE CURRE
----- ----- ----- -----
CURRENT_SCHEMAID
--------------------------------------------------------------------------------
SYS   SYS   SYS   SYS
0


SQL> conn tst@dev08
Enter password:
Connected.
SQL> /

user  SESSI CURRE CURRE
----- ----- ----- -----
CURRENT_SCHEMAID
--------------------------------------------------------------------------------
TST   TST   TST   TST
6018

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library