在存储过程中调用dbms_包的权限问题
遇到一个问题，如下：ETL@RACTEST> begin 2 DBMS_LOCK.sleep(1); 3 end; 4 /PL/SQL procedure successfully completed.但是ETL@RACTEST> CREATE OR REPLACE PROCEDURE etl.pliutest 2 authid current_user 3 AS 4 BEGIN 5 DBMS_LOCK.sleep(1); 6 END; 7 /Warning: Procedure created with compilation errors.Elapsed: 00:00:00.07ETL@RACTEST> ETL@RACTEST> show errorsErrors for PROCEDURE ETL.PLIUTEST:LINE/COL ERROR-------- -----------------------------5/2 PL/SQL: Statement ignored5/2 PLS-00201: identifier 'DBMS_LOCK' must be declared同一个包，在相同的用户下的匿名程序块中可以执行，但是在存储过程中却没有权限执行，查了下资料，一些DBMS_开头的pkg在pl/sql中用到的时候，需要显示的执行grant execute on dbms_xxxx to xxx 来赋下权限才可以。此外这个授权system用户是没有权限的，要sys 或者plsql / as dba登录才有权限授权。授权以后可以看到：SYSTEM@RACTEST> select grantee,owner,table_name,grantor,privilege from dba_tab_privs where table_name like 'DBMS_LOCK';GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE-------------------- ---------- ---------- ---------- ----------EXECUTE_CATALOG_ROLE SYS DBMS_LOCK SYS EXECUTEDMSYS SYS DBMS_LOCK SYS EXECUTECTXSYS SYS DBMS_LOCK SYS EXECUTEEXFSYS SYS DBMS_LOCK SYS EXECUTEMDSYS SYS DBMS_LOCK SYS EXECUTEOLAPSYS SYS DBMS_LOCK SYS EXECUTESYSMAN SYS DBMS_LOCK SYS EXECUTEETL SYS DBMS_LOCK SYS EXECUTE8 rows selected.然后再次执行：SYSTEM@RACTEST> CREATE OR REPLACE PROCEDURE etl.pliutest 2 authid current_user 3 AS 4 BEGIN 5 DBMS_LOCK.sleep(1); 6 END; 7 /Procedure created.OK了！
?转自：http://blog.sina.com.cn/s/blog_6ff05a2c0100mico.html