执行存储过程没有权限？？？

最新推荐文章于 2023-09-27 08:30:42 发布

原创最新推荐文章于 2023-09-27 08:30:42 发布 · 3.2k 阅读

1 ·

CC 4.0 BY-SA版权

故障处理专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一个关于Oracle数据库中存储过程执行时遇到的权限不足问题。通过创建存储过程并尝试执行，作者遇到了ORA-01031权限不足错误。尽管用户拥有DBA权限，但仍然无法直接解决问题。最终发现需要显式授予执行存储过程中所涉及操作的具体权限。

SQL> create or replace procedure insert_table_by_condition( table_name varchar2,log_name varchar2 ,HIS_name varchar2,mid_name varchar2 ,v_mid number ,v_start number,v_end number) is
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 totoalcount number;
4 m number;
5 i number;
6 v_count number;
7 begin
8 m :=v_mid;
9 i := v_start;
10 totoalcount:=v_end;
11 select sum(1) into v_count from boswll.TABLE_LOG where name=log_name;
12 if v_count=0 then
13 insert into boswll.TABLE_LOG values(log_name,0);
14 commit;
15 end if;
16 if totoalcount > 0 then
17 while i <= totoalcount+m loop
18 execute immediate 'insert into bosdata.' || HIS_name||' select * From bosdata.' || table_name||' where rowid in (select w_rowid from BOSWLL.'||mid_name ||' s where s.nmm >=' ||i|| ' and s.nmm
19 <'||i||' + '||m||') ';
20 update boswll.TABLE_LOG set zs=i where name=log_name;
21 commit;
22 i := i + m;
23 end loop;
24 end if;
25 END;
26 /

过程已创建。

SQL> exec insert_table_by_condition ('PD_WO','PD_WO','PD_WO_HIS','PD_WO_T',100,1,400);
BEGIN insert_table_by_condition ('PD_WO','PD_WO','PD_WO_HIS','PD_WO_T',100,1,400); END;

*
第 1 行出现错误:
ORA-01031: 权限不足
ORA-06512: 在 "BOSWLL.INSERT_TABLE_BY_CONDITION", line 18
ORA-06512: 在 line 1

SQL> select * From dba_role_privs where grantee='BOSWLL';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
BOSWLL RESOURCE NO YES
BOSWLL CONNECT NO YES
BOSWLL DBA NO YES

SQL> select * From dba_role_privs where grantee='BOSDATA';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
BOSDATA DBA NO YES
BOSDATA RESOURCE NO YES
BOSDATA CONNECT NO YES

为什么有了dba权限，在执行存储过程的时候还是报没有权限，并且单独拿出insert 执行也是没有问题的，最后发现，执行存储过程中的权限必须是显示的给权限

SQL> grant insert any table to boswll;
Grant succeeded.

SQL> grant insert any table to bosdata;
Grant succeeded.

SQL> exec insert_table_by_condition ('PD_WO','PD_WO','PD_WO_HIS','PD_WO_T',100,1,400);
PL/SQL 过程已成功完成。