Is SESSION_USER really constant during the session?

I always thought that SESSION_USER parameter of the USERENV context is constant during the life of the session. Actually not only I, because I so many times when this context parameter was used in triggers or packages for logging or access restriction purposes. For example to restrict access to some procedures of the package, condition like IF sys_context(‘USERENV’, ‘SESSION_USER’) != ‘SYS’ then reject execution.

But during the work on some security project I found the gap, actually it is possible that SESSION_USER changes! With this article I just want to warn you to not trust to SESSION_USER parameter 100% and make double checks when possible.

The example below will reproduce strange behavior.

Actually what happens, we are logged in as some non-privileged user SCOTT. SCOTT will execute some action and as the result SESSION_USER will change to SYS!

Preconditions to reproduce this behavior are:

WHEN  there is evet-based job in schema SCOTT
AND   there is event queue in schema SYS
AND   user SCOTT disables then enables it own Job
THEN  SESSION_USER of the SCOTTs session will change from SCOTT to SYS

Here is the code:

create or replace type sys.queue_payload as object (message varchar2(30));
/

begin
  dbms_aqadm.create_queue_table (queue_table        => 'sys.queue_table',
                                 queue_payload_type => 'sys.queue_payload', 
                                 multiple_consumers => true);

  dbms_aqadm.create_queue (queue_name  => 'sys.testqueue',
                           queue_table => 'sys.queue_table');

  dbms_aqadm.start_queue (queue_name => 'sys.testqueue');
end;
/

begin
  dbms_scheduler.create_job (job_name => 'scott.event_job',
                             job_type => 'PLSQL_BLOCK', 
                             job_action => 'BEGIN INSERT INTO dummy VALUES (dummy); COMMIT; END;',
                             start_date => SYSTIMESTAMP, 
                             event_condition => 'tab.user_data.message is not null',
                             queue_spec => 'sys.testqueue');
end;
/


grant execute on dbms_scheduler to scott;

I used some dummy code in job_action, because to simulate this behavior we do not need to let the job successfully run, we just need to disable and then enable it.

Now connect to SCOTT and execute:

connect scott/scott
col current_user format a20
col session_user format a20
col user format a20
select user, sys_context('USERENV','CURRENT_USER') current_user, sys_context('USERENV','SESSION_USER') session_user from dual;
USER                 CURRENT_USER         SESSION_USER
-------------------- -------------------- --------------------
SCOTT                SCOTT                SCOTT

exec dbms_scheduler.disable('SCOTT.EVENT_JOB');  
exec dbms_scheduler.enable('SCOTT.EVENT_JOB');

-- In 11g during enable job ORA-27373 possible, just ignore it.

col current_user format a20
col session_user format a20
col user format a20
select user, sys_context('USERENV','CURRENT_USER') current_user, sys_context('USERENV','SESSION_USER') session_user from dual;

USER		     CURRENT_USER	  SESSION_USER
-------------------- -------------------- --------------------
SYS		     SCOTT                SYS

show user
USER is "SCOTT"

Incredible, but it is true. Actually now SCOTT has the SESSION_USER SYS with all outcomes from it.

Just be aware, that it is possible. This behavior can be reproduced in 12c as well as in 11g.

 

---

Aychin Gasimov, 07/07/2018, Munich, Germany

Advertisement