存储过程中创建表,视图、同义词、序列时经常会遇到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