0

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?

2
  • Well, you know scheduler jobs are executed as job creator (not the job owner). This is different to what you expected but that's how Oracle implemented it and now you are aware of it. Maybe simple query select * from DBA_SCHEDULER_RUNNING_JOBS helps in future analysis. Dec 16, 2015 at 6:44
  • @WernfriedDomscheit Your comment appears to contradict your answer... job creator would be the invoker, but your answer says they run as Definer's (owner's) rights... (I suppose they could technically both be true, while job's run as the job creator, the jobs are actually using Definer's rights)... But that's neither here not there... I guess there is no other work around than to to login to the job owner as proxy in order to perform job creation or to create a SCHEDULER_JOB user from which to perform job creation... In any case, thank you for your responses. Dec 16, 2015 at 13:56

1 Answer 1

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.
1
  • Yes, I already knew that the scheduler jobs are running as definer's rights (the job's owner), which isn't the issue here. The issue is that the top activity graph in OEM reports that the JOB_CREATOR is who is running the job instead of the owner... which is what we are trying to avoid since it is misleading to anyone who views top activity. I've edited the subject to make this a little clearer. Dec 15, 2015 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.