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.
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;