GRANT CREATE JOB、CREATE EXTERNAL JOB 后 DBMS_SCHEDULER 缺少 Oracle 特权，ORA-27486
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5253087/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle privilege missing for DBMS_SCHEDULER, ORA-27486 after GRANT CREATE JOB, CREATE EXTERNAL JOB
提问by dacracot
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>
采纳答案by dacracot
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.
哇，我发现了问题......“myJob”是数据库中现有的包对象。我猜我的“权限不足”是用作业对象替换包对象。
回答by DCookie
回答by ik_zelf
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.
如果您获得了管理调度程序权限，那么接下来会失败的是 shell 脚本上不存在的执行位。如果执行位就位，它将失败，因为它缺少运行 SQL*Plus 所需的环境设置，如 PATH 和 ORACLE_HOME。
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.
除此之外，为什么坚持10g？Oracle 11g 有更好的选项来运行外部作业，安全性由凭据实现，而不是 $ORACLE_HOME 中定义用户运行作业的某些文件。
There is some very nice reading available on this subject, see my profile.
关于这个主题有一些非常好的读物，请参阅我的个人资料。
I hope this helps, Ronald.
我希望这会有所帮助，罗纳德。
