Privileges acquired through role

The following example demonstrated that the privileges acquired through role are not honored in PL/SQL procedures (also functions, triggers)


'dennis' has DBA role, however, it has problem to create a procedure, which uses dbms_lock package


dennis@TESTDB11> create or replace procedure t1_load(i_tag varchar2) as
2 m_date date;
3 begin
4 dbms_lock.sleep(0.01);
5 end;
6 /

Warning: Procedure created with compilation errors.

dennis@TESTDB11> show error;
Errors for PROCEDURE T1_LOAD:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/8 PL/SQL: Statement ignored
4/8 PLS-00201: identifier 'DBMS_LOCK' must be declared


'dennis' auctually does not have any problems to use dbms_lock package, if it is not used in the procedure:

dennis@TESTDB11> begin
2 dbms_lock.sleep(0.01);
3 end;
4 /

PL/SQL procedure successfully completed.


After explicitly grant the execute on dbms_lock to dennis, problem solved:

sys@TESTDB11> grant execute on dbms_lock to dennis;

Grant succeeded.


dennis@TESTDB11> create or replace procedure t1_load(i_tag varchar2) as
2 m_date date;
3 begin
4 dbms_lock.sleep(0.01);
5 end;
6 /

Procedure created.