问题
在PL/SQL中新建存储过程Procedure,然后在右键,点击测试的时候,调试到建表的那一行就报错:ORA-01031: insufficient privileges!
存储过程:
create or replace procedure get_clob
is
v_table_name varchar2(32);
v_sql varchar2(2000);
v_cnt number;
begin
v_table_name := 'T_CLOB';
v_sql := 'truncate table ' || v_table_name;
execute immediate v_sql;
select count(1) into v_cnt from user_tables t
where t.TABLE_NAME=upper('table1');
if v_cnt >0 then
execute immediate 'drop table table1';
end if;
v_sql := 'create global temporary table table1 on commit delete rows as select * from '|| v_table_name ||' where 1=2';
execute immediate v_sql;
execute immediate ('insert into table1 select * from '||v_table_name||'@etl59');
v_sql := 'insert into ' || v_table_name || ' select * from table1';
execute immediate v_sql;
commit;
exception
when others then
dbms_output.put_line(v_table_name || ':' || sqlcode || ':' || sqlerrm);
end get_clob;
解决方案
给该用户orcl添加权限:
grant create any table to orcl;
这样再调试,则没有再报错!
分析
用户具有dba权限,但在执行一个存储过程时提示“ORA-01031: 权限不足”,该过程中存在动态SQL“execute immediate。。。”正是这里报的错误。
1、ORACLE默认为定义者权限,定义者权限在存储过程中ROLE无效,需要显示授权。
2、如果使用AUTHID CURRENT_USER关键字使用调用者权限,则编译时ROLE无效,运行时有效。
解决办法:
方法1:就这个存储过程来说,CREATE TABLE想使用CREATE ANY TABLE权限,而CREATE ANY TABLE权限来自DBA角色,默认情况下,虽然在会话环境中可见,但在存储过程中不可见(无效)。
所以根据上面的第一条规则,可以显示地将CREATE ANY TABLE权限授予orcl就可以了,即
GRANT CREATE ANY TABLE TO orcl
方法2:采用调用者权限,由于过程中使用动态SQL,所以可以避开编译时的检查,但在运行时DBA角色生效,即
create or replace procedure get_clob
AUTHID CURRENT_USER
is