ORA-01031与存储过程和JOB

存储过程中创建表，视图、同义词、序列时经常会遇到ORA-01031: 权限不足的错误。原因是因为从角色中获取的权限在存储过程中是不可用的。

有两种方法可以解决：一、创建存储过程时在存储过程名后增加  Authid Current_User 如create or replace procedure tt_senquence  Authid Current_User is ...

 

二、显示授予该用户相关系统权限 如：grant create table to &user_name;grant create any synonym to &user_name;grant create any view to  &user_name;

 

两种方法各有利弊；

第一种方法不用显示授于各种系统权限，比较方便。但如果在job中调用该存储过程则还会报ORA-01031: 权限不足的错误。

第二种方法：虽然麻烦，但可以解决job中调用存储过程的问题。

SQL> show user;User is "report"

--report用户拥有DBA角色

SQL> select grantee, granted_role  from dba_role_privs  2   where granted_role = 'DBA' AND GRANTEE='REPORT'  3  ;

GRANTEE                        GRANTED_ROLE------------------------------ ------------------------------REPORT                         DBA

 

--手工创建一个序列，证明该用户有权限创建序列SQL> create sequence pro_sequence minvalue 1 maxvalue 2000 start with 1 increment by 1 cache 100;

Sequence created

--删除序列SQL> drop sequence pro_sequence;

Sequence dropped

 

--用存储过程创建该序列SQL> create or replace procedure tt_senquence is  2  begin  3    execute immediate('create sequence pro_sequence minvalue 1 maxvalue 2000 start with 1 increment by 1 cache 100 ');  4  end ;  5  /Procedure created

 

--执行存储过程时报ORA-01031: 权限不足SQL> exec tt_senquence;

begin tt_senquence; end;

ORA-01031: 权限不足ORA-06512: 在 "REPORT.TT_SENQUENCE", line 3ORA-06512: 在 line 1

 

 

-- 存储过程中加入 Authid Current_User后，可以成功创建序列。-- 但不能在job中调用该存储过程。SQL> create or replace procedure tt_senquence  Authid Current_User is  2  begin  3    execute immediate('create sequence pro_sequence minvalue 1 maxvalue 2000 start with 1 increment by 1 cache 100 ');  4  end ;  5  /

Procedure created

 

SQL> exec tt_senquence;PL/SQL procedure successfully completed

SQL> select pro_sequence.nextval from dual;

   NEXTVAL----------         1

--新建job，调用该过程

SQL> variable jobno number;SQL> begin  2  sys.dbms_job.submit(job => :jobno,  3                      what => 'tt_senquence;',  4                      next_date => to_date('09-01-2010', 'dd-mm-yyyy'),  5                      interval => 'TRUNC(sysdate)+0.2/24');  6    commit;  7  end;  8  /

PL/SQL procedure successfully completedjobno---------28

 

PL/SQL procedure successfully completed

我们在alert_sid.log中可以看到下述信息：ORA-12012: 自动执行作业 28 出错ORA-01031: 权限不足ORA-06512: 在 "REPORT.TT_SENQUENCE", line 3ORA-06512: 在 line 1

 

 

如果显示授予report用户创建序列权限 SQL> grant create any sequence to REPORT; Grant succeeded --去掉 Authid Current_User后创建存储过程，则可以成功调用。 --并且可以用job调用该存储过程 SQL> create or replace procedure tt_senquence is 2 begin 3 execute immediate('create sequence pro_sequence minvalue 1 maxvalue 2000 start with 1 increment by 1 cache 100 '); 4 end ; 5 / Procedure created SQL> exec tt_senquence; begin tt_senquence; end; ORA-00955: 名称已由现有对象使用 ORA-06512: 在 "REPORT.TT_SENQUENCE", line 3 ORA-06512: 在 line 1 SQL> drop sequence pro_sequence; Sequence dropped SQL> exec tt_senquence; PL/SQL procedure successfully completed