Oracle 数据库用户即使具有相关权限也无法创建数据库作业
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17396747/
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
An Oracle database user cannot create a DB job even with relevant privileges
提问by Manjula
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]
我有一个名为“ADMUSER”的Oracle数据库用户，具有以下权限，根据我的理解，这是创建数据库作业所需的权限。 [Oracle数据库版本为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.
但是，当我尝试调用一个存储过程，而该过程又调用 DBMS_SCHEDULER.create_job 方法时，我收到了权限不足的错误。
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).
我使用 SQL 开发人员执行了下面提到的这个 SP（我尝试像这样运行，因为当我使用 java jdbc 调用“{call PROD_Procedures.prod_dispatch_main_job(?,?,?)}”时它不起作用）。
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?
第 422 行包含“DBMS_SCHEDULER.create_job”...字符串。但正如我之前提到的，当我检查“session_privs”表时，我可以看到该用户具有上述权限。有人可以帮我解决这个问题吗？
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?
更新：我用'DEFAULT_JOB_CLASS'替换了create_job函数中job_class的值。然后它可以正常工作。因此，我认为该用户需要一些权限才能访问“CONSOLIDATE_CLASS”作业类。任何人都可以让我知道如何检查数据库中为该用户授予的此作业类的权限吗？我应该参考什么表来检查对象的权限授予？
I need to verify this user doesn't have execute privilege on 'CONSOLIDATE_CLASS' before granting the privileges.
在授予权限之前，我需要验证此用户对“CONSOLIDATE_CLASS”没有执行权限。
回答by Ankur Bhutani
1st login from the DBA should be to give this grant by querying as below:
来自 DBA 的第一次登录应该是通过如下查询来授予此授权：
BEGIN
grant create any job to **ADMUSER**;
grant execute on DBMS_SCHEDULER to **ADMUSER**;
grant manage scheduler to **ADMUSER**;
END;
??
??
回答by ThinkJet
When calling DBMS_SCHEDULER.create_job(...)you specify the value for job_classparameter.
调用DBMS_SCHEDULER.create_job(...)时指定job_class参数值。
User must have EXECUTEprivileges on CONSOLIDATE_CLASSjob class to create jobs belonging to this class (documentation link):
用户必须具有作业类的EXECUTE权限CONSOLIDATE_CLASS才能创建属于此类的作业（文档链接）：
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_privsview:
要检查用户是否允许使用此类创建作业，您可以查看all_tab_privs视图：
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
)
