存储过程与权限

在SQL中可以正常执行的语句，为什么在存储过程中却无法执行？

1.定义者权限存储过程role无效，必须要有显示的授权  AUTHID DEFINER

点击(此处)折叠或打开

conn / AS SYSDBA;
CREATE USER usera IDENTIFIED BY usera;
CREATE USER luise IDENTIFIED BY luise;
GRANT CONNECT, RESOURCE TO usera;
GRANT CONNECT, RESOURCE, DBA TO luise;

conn usera/usera;
CREATE TABLE usera_tab AS SELECT * FROM all_objects;

--luise用户因为具有DBA角色,可以执行usera方案下的表的删除操作
conn luise/luise;
DELETE FROM usera.usera_tab;

--但在luise用户下创建存储过程,执行同样的删除操作,却不可以
CREATE OR REPLACE PROCEDURE lusie_proc
AS
BEGIN
  DELETE FROM usera.usera_tab;
  COMMIT;
END;
/

Warning: Procedure created with compilation errors.

--查看错误
luise@TESTDB11>show errors
Errors for PROCEDURE LUSIE_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: SQL Statement ignored
4/21 PL/SQL: ORA-00942: table or view does not exist

--usera登录给luise直接授权
conn usera/usera;
GRANT ALL ON usera_tab TO luise;

--再次登录,编译成功
conn luise/luise
CREATE OR REPLACE PROCEDURE lusie_proc
AS
BEGIN
  DELETE FROM usera.usera_tab;
  COMMIT;
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1070896/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1070896/