-- Thanks for the question regarding "Own a Job", version 11.1.0.7
Submitted on 29-Oct-2009 10:33 Central time zone
Last updated 15-Nov-2009 12:12
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 we 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://download.oracle.com/docs/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!)
Heres the code
October 29, 2009 - 10pm Central time zone
Reviewer: A reader
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
Followup October 30, 2009 - 10am Central time zone:
ok, from the documentation I tried to point you to:
http://download.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.
ops$tkyte%ORA11GR1> grant create job to abc;
Grant succeeded.
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
October 30, 2009 - 11am Central time zone
Reviewer: A reader
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) .
Followup November 9, 2009 - 11am Central time zone:
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
October 30, 2009 - 11am Central time zone
Reviewer: A reader
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 .
Followup November 9, 2009 - 11am Central time zone:
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
November 11, 2009 - 5am Central time zone
Reviewer: dharamPal
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.
Followup November 15, 2009 - 12pm Central time zone:
I don't know what you mean.
job and scheduler
November 11, 2009 - 6am Central time zone
Reviewer: dharamPal
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.
|