Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: October 29, 2009 - 10:33 am UTC

Last updated: November 15, 2009 - 12:12 pm UTC

Version: 11.1.0.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I have a user say ABC with resource role and create session priv in 11g.

So it has following from resource role create cluster,indextype,operator,procedure,sequence,table,trigger,type.

I try to create a Job USING dbms_scheduler.

It says insufficient privileges.
ORA-27486: insufficient privileges

ORA-06512: at "SYS.DBMS_ISCHED", line 99

ORA-06512: at "SYS.DBMS_SCHEDULER", line 268

ORA-06512: at line 2


Why do I get this error ? Is it because I have no execute priv on dbms_scheduler ? May be not ... Please continue reading.

Next I go to OEM and schedule this Job , in OEM I use system account to create the Job.

I query dba_scheduler The OWNER is ABC and job creator is SYSTEM.

So user system created this Job as ABC.JOB .

Now I can also execute the job when logged in to ABC. using dbms_scheduler.run_job.

Questions :-- It seems When I use system account to create a job in user ABC , A USER which does not have priv to create Job , then still I can run it.
I did not give explicit priv to dbms_scheduler to ABC , How it got that ?


Another Question :What priv would I need to do all job actions from user ABC for its own job ?

and Tom said...

ops$tkyte%ORA10GR2> !oerr ora 27486
27486, 00000, "insufficient privileges"
// *Cause: An attempt was made to perform a scheduler operation without the
//         required privileges.
// *Action: Ask a sufficiently privileged user to perform the requested
//          operation, or grant the required privileges to the proper user(s).


one would need an example to work with to answer what privilege you needed, however


http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/d_sched.htm#insertedID0

the docs do have the various privileges required to do the various things.

without knowing WHAT you were trying to do, we cannot say much else. Examples are always nice


(but no, it has nothing to do with " Is it because I have no execute priv on dbms_scheduler ?" - you are getting an error RUNNING dbms_scheduler, if you did not have privileges to run it - you could not get an error from it!)

Rating

  (5 ratings)

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

Comments

Heres the code

A reader, October 29, 2009 - 10:42 pm UTC

Heres the code I am trying to run from user ABC.

This runs fine from OEM logged on as system user, owner of job shows as ABC . I can then run the job by logging in a user ABC.
I do not have the create Job privilege is it because of that.May if you somehow own a Job you can only run it even if you dont have privilege to create a Job. This sounds strange , is it True though ?

I even tried commenting the set attributes raise events ...still same error.

How did I get execute on dbms_scheduler since I can use dbms_scheduler.run_job.

Thanx

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"ABC"."TJOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
NULL;
end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0',
start_date => systimestamp at time zone 'America/New_York',
job_class => '"DEFAULT_JOB_CLASS"',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"ABC"."TJOB"', attribute => 'raise_events', value => dbms_scheduler.job_failed + dbms_scheduler.job_broken);
sys.dbms_scheduler.enable( '"ABC"."TJOB"' );
END;
Error at line 1
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 268
ORA-06512: at line 2



Tom Kyte
October 30, 2009 - 10:20 am UTC

ok, from the documentation I tried to point you to:

http://docs.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sched.htm#sthref6742


... To create a job in your own schema, you need to have the CREATE JOB privilege. ....


ops$tkyte%ORA11GR1> drop user abc cascade;

User dropped.

ops$tkyte%ORA11GR1> grant create session, resource to abc identified by abc;

Grant succeeded.

<b>
ops$tkyte%ORA11GR1> grant create job to abc;

Grant succeeded.
</b>

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> connect abc/abc
Connected.
abc%ORA11GR1>
abc%ORA11GR1> set echo on
abc%ORA11GR1> begin
  2  sys.dbms_scheduler.create_job(
  3  job_name => '"ABC"."TJOB"',
  4  job_type => 'PLSQL_BLOCK',
  5  job_action => 'begin
  6     NULL;
  7  end;',
  8  repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0',
  9  start_date => systimestamp at time zone 'America/New_York',
 10  job_class => '"DEFAULT_JOB_CLASS"',
 11  auto_drop => FALSE,
 12  enabled => FALSE);
 13  end;
 14  /

PL/SQL procedure successfully completed.

abc%ORA11GR1>
abc%ORA11GR1> begin
  2  sys.dbms_scheduler.set_attribute( name => '"ABC"."TJOB"', attribute => 'raise_events', value =>
  3  dbms_scheduler.job_failed + dbms_scheduler.job_broken);
  4  end;
  5  /

PL/SQL procedure successfully completed.

abc%ORA11GR1> begin
  2  sys.dbms_scheduler.enable( '"ABC"."TJOB"' );
  3  END;
  4
  5  /

PL/SQL procedure successfully completed.

Follow Up

A reader, October 30, 2009 - 11:02 am UTC

Thanx Tom. Kindly correct me

If you do not have create job privilege ( say user ABC ) you cannot create a Job But you can still run a Job which is created by another user but as owned by the same user ( ABC) .
Tom Kyte
November 09, 2009 - 11:17 am UTC

if you do not have create job, you cannot create a job.

to see the other bit, why wouldn't you just try it out?


ops$tkyte%ORA10GR2> drop user abc cascade;

User dropped.

ops$tkyte%ORA10GR2> grant create session, resource to abc identified by abc;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2  sys.dbms_scheduler.create_job(
  3  job_name => '"ABC"."TJOB"',
  4  job_type => 'PLSQL_BLOCK',
  5  job_action => 'begin
  6     NULL;
  7  end;',
  8  repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0',
  9  start_date => systimestamp at time zone 'America/New_York',
 10  job_class => '"DEFAULT_JOB_CLASS"',
 11  auto_drop => FALSE,
 12  enabled => FALSE);
 13  end;
 14  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2  sys.dbms_scheduler.set_attribute( name => '"ABC"."TJOB"', attribute => 'raise_events', value =>
  3  dbms_scheduler.job_failed + dbms_scheduler.job_broken);
  4  end;
  5  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect abc/abc
Connected.
abc%ORA10GR2> begin
  2  sys.dbms_scheduler.enable( '"ABC"."TJOB"' );
  3  sys.dbms_scheduler.run_job( '"ABC"."TJOB"' );
  4  END;
  5  /

PL/SQL procedure successfully completed.



It is like a table - you don't need CREATE TABLE to own a table, but once you owned the table - it is there and you can insert into it.

Follow Up

A reader, October 30, 2009 - 11:04 am UTC

Even though ABC user had no explicit priviLege given on DBMS_SCHEDULER

Why could user ABC still run a Job ( owned by itself )if it cant create a JOB .
Tom Kyte
November 09, 2009 - 11:19 am UTC

because someone else can create it, just like a table can be created in another schema.

or, it might have had (past tense) create job, but does not anymore. Just like revokeing create table would not drop tables, revokeing create job will not drop jobs.

job and scheduler

dharamPal, November 11, 2009 - 5:58 am UTC

Dear Tom Sir, this is the greate represention of an object/owner relationship. i would like to draw your attention on the logic of priority settings of code execution like pl/sql , shell scripts. Job shedular is going to replace crontab.

Tom Kyte
November 15, 2009 - 12:12 pm UTC

I don't know what you mean.

job and scheduler

dharamPal, November 11, 2009 - 6:01 am UTC

Dear Tom Sir, this is the greate represention of an object/owner relationship. i would like to draw your attention on the logic of priority settings of code execution like pl/sql , shell scripts in scheduler. Job schedular may be going to replace crontab.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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