Oracle privilege missing for DBMS_SCHEDULER, ORA-27486 after GRANT CREATE JOB, CREATE EXTERNAL JOB

What additional privilege am I missing?

Connected to:
Oracle Database 10g Enterprise Edition R开发者_开发知识库elease 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL> create user myUser identified by password default tablespace theData temporary tablespace temp;

User created.

SQL> grant connect, resource to myUser;

Grant succeeded.

SQL> GRANT READ,WRITE ON DIRECTORY tmp TO myUser;

Grant succeeded.

SQL> GRANT CREATE JOB TO myUser;

Grant succeeded.

SQL> GRANT CREATE EXTERNAL JOB TO myUser;

Grant succeeded.

SQL> connect myUser/password
Connected.
SQL>
SQL>
1 CREATE PROCEDURE shellScript
2 AS
3 /*-----------------------*/
4 v_sql UTL_FILE.FILE_TYPE;
5 v_shell UTL_FILE.FILE_TYPE;
6 /*=======================*/
7 BEGIN
8 /*=======================*/
9 -- write the sql script to /tmp/myUser-tmp-script.sql
10 v_sql:= UTL_FILE.FOPEN('TMP','myUser-tmp-script.sql','w');
11 UTL_FILE.PUT_LINE(v_sql,'select to_char(sysdate,''YYYYMMDDHR24MISS'') from dual'||';', FALSE);
12 UTL_FILE.FFLUSH(v_sql);
13 UTL_FILE.FCLOSE(v_sql);
14 -- write the shell script to /tmp/myUser-tmp-script.sh
15 v_shell:= UTL_FILE.FOPEN('TMP','myUser-tmp-script.sh','w');
16 UTL_FILE.PUT_LINE(v_shell,'#!/bin/bash', FALSE);
17 UTL_FILE.PUT_LINE(v_shell,'sqlplus myUser/password@sbox @/tmp/myUser-tmp-script.sql > /tmp/myUser-tmp-script.err', FALSE);
18 UTL_FILE.FFLUSH(v_shell);
19 UTL_FILE.FCLOSE(v_shell);
20 -- execute the shell script which executes the sql script
21 DBMS_SCHEDULER.PURGE_LOG(JOB_NAME=>'myJob');
22 DBMS_SCHEDULER.CREATE_JOB(JOB_NAME=>'myJob', JOB_TYPE=>'EXECUTABLE', JOB_ACTION=>'/bin/bash', NUMBER_OF_ARGUMENTS=>1, START_DATE=>SYSTIMESTAMP, ENABLED=>FALSE);
23 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('myJob', 1, '/tmp/myUser-tmp-script.sh');
24 DBMS_SCHEDULER.ENABLE('myJob');
25 USER_LOCK.SLEEP(500); -- give it 5 seconds to complete
26 -- clean up
27 UTL_FILE.FREMOVE('TMP', 'myUser-tmp-script.sh');
28 UTL_FILE.FREMOVE('TMP', 'myUser-tmp-script.sql');
29 /*=======================*/
30 END shellScript;
/

Procedure created.

SQL> SHOW ERRORS PROCEDURE shellScript
No errors.
SQL>
SQL>
SQL> execute shellScript;
BEGIN shellScript; END;

*
ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 411
ORA-06512: at "SYS.DBMS_ISCHED", line 452
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1082
ORA-06512: at "MYUSER.SHELLSCRIPT", line 21
ORA-06512: at line 1

SQL>

According to TFM, PURGE_LOG requires the MANAGE SCHEDULER privilege:

GRANT MANAGE SCHEDULER TO xxx;

Wow, I found the problem... "myJob" was an existing package object in the database. I'm guessing my "insufficient privileges" were to replace the package object with a job object.

if you get the manage scheduler privilege, the next thing where this will fail is the none existing execute bits on the shell script. If the execute bits are in place, it will fail because it lacks the environment settings like PATH and ORACLE_HOME, needed to run SQL*Plus.

Besides that, why stick to 10g? Oracle 11g has much better options to run external jobs, security implemented by credentials instead of some file in $ORACLE_HOME that defines the user to run the job.

There is some very nice reading available on this subject, see my profile.

I hope this helps, Ronald.