ORACLE在存储过程中调用dbms_包的权限问题
ORACLE在存储过程中调用dbms_包的权限问题
遇到一个问题，如下：
ETL@RACTEST> begin
2 DBMS_LOCK.sleep(1);
3 end;
4 /
PL/SQL procedure successfully completed.
www.zzzyk.com
但是
ETL@RACTEST> CREATE OR REPLACE PROCEDURE etl.pliutest
2 authid current_user
3 AS
4 BEGIN
5 DBMS_LOCK.sleep(1);
6 END;
7 /
www.zzzyk.com
Warning: Procedure created with compilation errors.
Elapsed: 00:00:00.07
ETL@RACTEST> ETL@RACTEST> show errors
Errors for PROCEDURE ETL.PLIUTEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/2 PL/SQL: Statement ignored
5/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 EXECUTE
DMSYS SYS DBMS_LOCK SYS EXECUTE
CTXSYS SYS DBMS_LOCK SYS EXECUTE
EXFSYS SYS DBMS_LOCK SYS EXECUTE
MDSYS SYS DBMS_LOCK SYS EXECUTE
OLAPSYS SYS DBMS_LOCK SYS EXECUTE
SYSMAN SYS DBMS_LOCK SYS EXECUTE
ETL SYS DBMS_LOCK SYS EXECUTE
8 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了！
