ORA-27486: insufficient privileges.

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

A short account of a couple of occurrences of ORA-27486.

exec  dbms_scheduler.create_job_class('TEST_CLASS');

Error starting at line 1 in command:
exec  dbms_scheduler.create_job_class('TEST_CLASS')
Error report:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 706
ORA-06512: at line 1
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).

The user performing this operation needs the MANAGE SCHEDULER privilege.
Please note that having the CREATE JOB privilege is not enough.

That was easy.

connect system 

exec  dbms_scheduler.create_job_class('TEST_CLASS');

connect test

begin
  dbms_scheduler.create_job(
   job_name          =>  'TEST_JOB',
   job_type          =>  'PLSQL_BLOCK',
   job_class         =>  'TEST_CLASS',
   job_action        =>  'begin null; end;',
   start_date        =>  sysdate);
end;
/

begin
  dbms_scheduler.enable('TEST_JOB');
end;
/

Error starting at line 11 in command:
begin
  dbms_scheduler.enable('TEST_JOB');
end;
Error report:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 2751
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1794
ORA-06512: at line 2
27486. 00000 -  "insufficient privileges"
 

This was less trivial to find out, especially because I thought user TEST had created the job class, but it didn't.
Intestingly the first block is executed without problems, it's the enable procedure that is failing.

Then I carefully read the Oracle documentation for the DBMS_SCHEDULER.ENABLE procedure.
"For users to create jobs that belong to a job class, the job owner must have EXECUTE privileges on the job class. Therefore, after the job class has been created, EXECUTE privileges must be granted on the job class so that users create jobs belonging to that class."
 
So, if the user willing to schedule a job didn't create the job class, the user who created the job class must grant the EXECUTE privilege on the job class to the other user.

GRANT EXECUTE ON TEST_CLASS TO TEST; -- AS SYS or SYSTEM

 
The problem here was that I originally created the job classes with the TEST user, but then I exported and imported the schema as SYS, and I imagine something went wrong with the privileges (just my speculation thinking about the steps I made).

It resulted in a somewhat confusing situation where I couldn't immediately understand why I couldn't run a job even if I created it.

See message translations for ORA-27486 and search additional resources.