Top Activity in OEM reports that dbms_scheduler jobs run as the job_creator user?

Question

The top activity graph in Oracle Enterprise Manager was reporting that certain activity was being performed by certain users at odd hours of the night. Upon researching, this activity was because the user in question was the creator of a scheduler job (even though the scheduler job was in a different schema). This has led to some amount of confusion, so we are attempting to have the Top Activity graph in OEM report that the activity is from the owner of the scheduler job instead of the job_creator.

Test Case

sqlplus kjohnston

begin
    dbms_scheduler.create_job(job_name => 'UTILITIES.SCHEDULE_TEST', job_type => 'PLSQL_BLOCK', 
    job_action => 'begin for v_id in (select * from dba_tables) loop null; end loop; end;', 
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=1', auto_drop => FALSE, enabled => TRUE);
end;

When UTILITIES.SCHEDULE_TEST runs via the repeat_interval, the job is reported as running as the job_creator user.

SELECT sample_time, username, sql.sql_text 
  FROM v$active_session_history sess, v$sql sql, dba_users users
 WHERE sess.user_id = users.user_id
   AND sess.sql_id = sql.sql_id
   AND upper(sql.sql_text) = 'SELECT * FROM DBA_TABLES'
  ORDER BY sample_time desc;

SAMPLE_TIME         USERNAME 
14-DEC-15 14.47.52  KJOHNSTON
14-DEC-15 14.46.52  KJOHNSTON

However, when the job is run on-demand, the job is reported as having been run as the job owner.

exec dbms_scheduler.run_job('UTILITIES.SCHEDULE_TEST');
/
SELECT sample_time, username, sql.sql_text 
  FROM v$active_session_history sess, v$sql sql, dba_users users
 WHERE sess.user_id = users.user_id
   AND sess.sql_id = sql.sql_id
   AND upper(sql.sql_text) = 'SELECT * FROM DBA_TABLES'
  ORDER BY sample_time desc;

SAMPLE_TIME         USERNAME
14-DEC-15 14.59.19  UTILITIES

My first attempt at a solution was to wrap the create_scheduler_job in a procedure owned by the owner of the scheduler job, then run the procedure to create the scheduler job... however, the job_creator is still KJOHNSTON (which would then still look like KJOHNSTON is running the job instead of the job owner: UTILITIES):

create or replace procedure utilities.create_scheduler_job as
begin
    dbms_scheduler.create_job(job_name => 'UTILITIES.SCHEDULE_TEST', job_type => 'PLSQL_BLOCK', 
    job_action => 'begin for v_id in (select * from dba_tables) loop null; end loop; end;', 
    auto_drop => FALSE, enabled => TRUE);
end;
/
exec utilities.create_scheduler_job;
/
SELECT job_creator FROM dba_scheduler_jobs WHERE owner = 'UTILITIES' and job_name = 'SCHEDULE_TEST';

JOB_CREATOR
KJOHNSTON

I've considered 2 other potential solutions:

1) Proxy in as the job owner to create the scheduler job.

2) Create a generic SCHEDULER_JOB user that we login as in order to create the scheduler job.

Neither solution is particularly appealing to me as either solution requires more work per job than I would care to put into scheduler job creation.

Just to be clear, the jobs run without issue whether they are reported to have been run from the job_creator or the owner of the job, but this would help with the future analysis of top activity on our databases.

Has anyone else come across this issue and come up with a better/easier/more elegant solution?

Answer 1

You have to distinct between the user of the current session you used for logon and the name of the database user whose privileges are currently active (see also About Definer's Rights and Invoker's Rights). The Scheduler jobs are running under Definer's Rights.

You can get the name of the database user whose privileges are currently active by SYS_CONTEXT('USERENV', 'CURRENT_USER'), see SYS_CONTEXT

In order to understand, try this test-case:

sqlplus kjohnston

CREATE TABLE UTILITIES.A_SCHEDULER_TEST 
   (username VARCHAR2(30), CURRENT_USER VARCHAR2(30), ts TIMESTAMP);

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'UTILITIES.SCHEDULE_TEST', 
        job_type => 'PLSQL_BLOCK', 
      job_action => 'begin insert into UTILITIES.A_scheduler_test values (USER, SYS_CONTEXT(''USERENV'', ''CURRENT_USER''), LOCALTIMESTAMP ); commit; end;', 
      repeat_interval => 'FREQ=MINUTELY;INTERVAL=1;BYSECOND=00', 
        auto_drop => FALSE, 
        enabled => TRUE);

    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'kjohnston.SCHEDULE_TEST', 
        job_type => 'PLSQL_BLOCK', 
      job_action => 'begin insert into UTILITIES.A_scheduler_test values (USER, SYS_CONTEXT(''USERENV'', ''CURRENT_USER''), LOCALTIMESTAMP ); commit; end;', 
      repeat_interval => 'FREQ=MINUTELY;INTERVAL=1;BYSECOND=10', 
        auto_drop => FALSE, 
        enabled => TRUE);
END;

The content of A_scheduler_test will be like this

USERNAME      CURRENT_USER      TS       
------------  ---------------   --------------------
KJOHNSTON     UTILITIES         2015-12-15 08:50:00
KJOHNSTON     KJOHNSTON         2015-12-15 08:50:10

JOB_CREATOR is relevant only for result of USER, resp SYS_CONTEXT('USERENV', 'SESSION_USER'), but the PL/SQL block runs in context of Job owner.

If you run the scheduler job manually you can define how it is executed: RUN_JOB Procedure

use_current_session: This specifies whether or not the job run should occur in the same session that the procedure was invoked from.

• use_current_session => TRUE (default): The job runs as the user who called RUN_JOB.

• use_current_session => FALSE: The job runs as the user who is the job owner.

However, when I test this in my database (11.1.0.7.0), this seems to be a (documentation) bug in Oracle!

• For use_current_session => FALSE, function USER always returns the job creator.
• For use_current_session => TRUE, function USER always returns the job owner.