存储过程如下:
CREATE OR REPLACE PROCEDURE P_INSERT_DEPT
ASBEGIN
INSERT INTO dept
VALUES(70,'研发部','深圳');
DBMS_OUTPUT.PUT_LINE('插入成功!');
execute immediate('create table dept1 as select * from dept');
END;
能在块中执行,在存储过程中却不能执行.
已经授予SCOTT用户sysdba角色权限了,还是报权限不足,到底是缺少什么权限呢?
网上搜索“存储过程建表”,果然有人跟我遇到了相同的问题,确实是缺少权限,同时给出了解决方案:
grant create any table to scott;
授权后,不报错了。
很纳闷的是dba角色怎么会没有create any table权限呢?
主要是role的角色没有起使用,还有以下的解决办法:
可以看到,即使拥有DBA role,也不能创建表。role在存储过程中不可用。
--实际上,oracle给我们提供了在存储过程中使用role权限的方法:
--修改存储过程,加入Authid Current_User时存储过程可以使用role权限。
另外查得存储过程中只能执行insert、delete、update、select操作,如果要实现其他操作,只能用动态sql去实现。
参考文档:
http://www.cnblogs.com/yuechaotian/archive/2013/02/20/2918036.html
oracle-存储过程提示ORA-01031: 权限不足
用户具有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权限授予cog就可以了,即
GRANT CREATE ANY TABLE TO COG;
方法2:采用调用者权限,由于过程中使用动态SQL,所以可以避开编译时的检查,但在运行时DBA角色生效,即
CREATE OR REPLACE PROCEDURE INSERT_DATA --插入user_客户ID,套餐表,客户宽表
(RTN_ID OUT NUMBER, --返回错误ID
RTN_STR OUT VARCHAR --返回错误叙述
) AUTHID CURRENT_USER IS
...................