Oracle privilege missing for DBMS_SCHEDULER, ORA-27486 after GRANT CREATE JOB, CREATE EXTERNAL JOB
61,051
Solution 1
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.
Solution 2
According to TFM, PURGE_LOG requires the MANAGE SCHEDULER privilege:
GRANT MANAGE SCHEDULER TO xxx;
Comments
-
dacracot over 3 years
What additional privilege am I missing?
Connected to: Oracle Database 10g Enterprise Edition Release 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>