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 3
ORA-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 completed
jobno
---------
28

 

PL/SQL procedure successfully completed

我们在alert_sid.log中可以看到下述信息：
ORA-12012: 自动执行作业 28 出错
ORA-01031: 权限不足
ORA-06512: 在 "REPORT.TT_SENQUENCE", line 3
ORA-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 createdSQL> exec tt_senquence;begin tt_senquence; end;ORA-00955: 名称已由现有对象使用ORA-06512: 在 "REPORT.TT_SENQUENCE", line 3ORA-06512: 在 line 1SQL> drop sequence pro_sequence;Sequence droppedSQL> exec tt_senquence;PL/SQL procedure successfully completed