An Oracle database user cannot create a DB job even with relevant privileges

Question

I have an Oracle database user named "ADMUSER" with following privileges, which are the required privileges for creating a database job according to my understanding.[Oracle database version is Oracle 10g 10.2.0.4]

CREATE JOB
CREATE ANY JOB
CREATE EXTERNAL JOB
MANAGE SCHEDULER

But when I try to call a stored procedure which in turn call DBMS_SCHEDULER.create_job method, I am getting an insufficient privileges error.

Stored Procedure:

   PROCEDURE prod_dispatch_main_job (l_max_job    IN NUMBER,
                                     l_interval   IN NUMBER,
                                     l_freq       IN VARCHAR2)
   BEGIN
         DBMS_SCHEDULER.create_job (
            job_name          => 'PROD_DISPATCH_JOB',
            job_type          => 'PLSQL_BLOCK',
            job_action        =>    'BEGIN PROD_Procedures.prod_run_user_job('
                                 || l_max_job
                                 || '); END;',
            start_date        => SYSTIMESTAMP,
            repeat_interval   =>    'freq='
                                 || l_freq
                                 || ';interval='
                                 || l_interval,
            job_class         => 'CONSOLIDATE_CLASS',
            enabled           => TRUE,
            auto_drop         => FALSE);
   END;

I executed this SP as mentioned in below using the SQL developer (I tried to run like this because it didn't work when I called like "{call PROD_Procedures.prod_dispatch_main_job(?,?,?)}" using java jdbc).

begin
prod_procedures.prod_dispatch_main_job(1,10,'minutely');
end;

Then I got the following error.

Error starting at line 7 in command:
begin
prod_procedures.prod_dispatch_main_job(1,10,'minutely');
end;
Error report:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
ORA-06512: at "ADMUSER.PROD_PROCEDURES", line 422
ORA-06512: at line 2
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).

Line 422 contains "DBMS_SCHEDULER.create_job"... string. But as I mentioned earlier, when I checked "session_privs" table, I can see this user has above mentioned privileges. Could anyone please help me to fix this issue?

---

UPDATE: I replaced the value of job_class in the create_job function with 'DEFAULT_JOB_CLASS'. Then it works without any problem. Therefore I think this user needs some privileges to access the 'CONSOLIDATE_CLASS' job class. Could any one let me know how to check the database for the privileges granted on this job class for this user? What table I should refer to check the privileges grant on objects?

I need to verify this user doesn't have execute privilege on 'CONSOLIDATE_CLASS' before granting the privileges.

Solution 1

When calling DBMS_SCHEDULER.create_job(...) you specify the value for job_class parameter.

User must have EXECUTE privileges on CONSOLIDATE_CLASS job class to create jobs belonging to this class (documentation link):

grant execute on user_who_owns_class.CONSOLIDATE_CLASS to user_who_creates_job

UPDATE

To check if user allowed to create a job with this class you can look at all_tab_privs view:

select count(1) 
from all_tab_privs 
where 
  table_name = 'PROD_DISPATCH_JOB' 
  and 
  table_schema = upper('<job class owner name here>')
  and
  privilege = 'EXECUTE'
  and 
  grantee in (
    select 'PUBLIC' from dual  -- granted to public
    union 
    select upper('<current_user_name_here>') from dual -- direct grant
    union
    select role from session_roles  -- roles enabled for current session
  )

Solution 2

1st login from the DBA should be to give this grant by querying as below:

BEGIN
  grant create any job to **ADMUSER**;
  grant execute on DBMS_SCHEDULER to **ADMUSER**;
  grant manage scheduler to **ADMUSER**;
END;