Home » RDBMS Server » Security » DBMS_SCHEDULER
DBMS_SCHEDULER [message #558932] Wed, 27 June 2012 10:04 Go to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi ,
I connected to Scott user & I am trying to create a job using DBMS_SCHEDULER as mentioned below.

begin
dbms_scheduler.create_job(
job_name => 'job1',
job_type => 'PLSQL_BLOCK',
job_action => 'Insert into tab2 values(70,''TR'');',
start_date => sysdate,
repeat_interval => 'FREQ = DAILY; INTERVAL = 1');
end;

When i execute this code i'm getting an Error Message as
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
ORA-06512: at line 2


I have given
grant manage scheduler to scott;
Manage Scheduler privilege from SYS User.

Could anyone please provide me the solution.
Thanks in Advance.
Re: DBMS_SCHEDULER [message #558933 is a reply to message #558932] Wed, 27 June 2012 10:18 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
According to documentation CREATE_JOB procedure requires "CREATE JOB" privilege (not "MANAGE SCHEDULER" one).
Re: DBMS_SCHEDULER [message #558935 is a reply to message #558933] Wed, 27 June 2012 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... In addition, EACH AND EVERY package comes with a "security model" section in the manual.
ALWAYS read it before using it (and posting question).

Regards
Michel
Re: DBMS_SCHEDULER [message #559538 is a reply to message #558935] Tue, 03 July 2012 20:41 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I connect to "sys as sysdba" when I schedule the job so the job will run as sys and will have the correct path/privileges to run. Here is an insert that I got to work nightly.
-- You must commit when you are finished to add the line to sys.job$/dba_jobs
-- You must run this as sys to get the jobs to run as sys to get the correct path to run the job

variable jobno number;
variable instno number;
begin
  select max(job)+1 into :jobno from dba_jobs;
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno,
'
begin
insert into polls.votes_audit 
select trunc(sysdate-1) create_date,OPTION_ID,HIT_COUNT 
from POLLS.VOTES 
where option_id like ''622592%'';
end;
',
trunc(sysdate)+1+1/24/60, 'trunc(sysdate)+1+1/24/60',
 TRUE, :instno);
end;
/
commit;

[Updated on: Tue, 03 July 2012 22:59] by Moderator

Report message to a moderator

Re: DBMS_SCHEDULER [message #559543 is a reply to message #559538] Tue, 03 July 2012 22:18 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If it does what you meant it to do - fine.

However, why did you use SYS? Generally speaking, you should pretend that SYS doesn't even exist, that you don't know its password, and avoid its use. You might screw your database up (sooner or later) if you continue using it for wrong purposes (and yes, I believe that scheduling a job that inserts data into "polls" user's table IS wrong).
Re: DBMS_SCHEDULER [message #559545 is a reply to message #559538] Tue, 03 July 2012 23:03 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
select max(job)+1 into :jobno from dba_jobs;


Is useless, jobno is an OUT parameter of dbms_job.submit not an IN (or IN/OUT) one, there is no need to get the max jobno. In addition, dbms_job use its own sequence to get the jobno and the next one may not be max(jobno)+1.

About SYS (to emphasize what Littlefoot said):

- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.


Regards
Michel

[Updated on: Tue, 03 July 2012 23:04]

Report message to a moderator

Previous Topic: Truncate aud$ table
Next Topic: Schema Creation
Goto Forum:
  


Current Time: Thu Mar 28 09:18:19 CDT 2024