Oracle PL/SQL之不能在function里面调用DBMS_LOCK（Grant to role OR Grant to user）

已知：测试用户tuser1，测试角色trole1，trole1已经授权给了tuser1。

在测试一段程序时需要用到延时，于是就把dbms_lock授权给了trole1，放在匿名块里测试没有问题：

view plain
SQL> set serveroutput on;  
SQL>   
SQL> BEGIN  
  2    dbms_output.put_line(systimestamp);  
  3    --  dbms_backup_restore.sleep(3);  
  4    dbms_lock.sleep(3);  
  5    dbms_output.put_line(systimestamp);  
  6  END;  
  7  /  
   
24-JUN-11 04.30.57.722000000 PM +08:00  
24-JUN-11 04.31.00.723000000 PM +08:00  
   
PL/SQL procedure successfully completed  
   
SQL>   

 

但是把它移到function里面却报错了：

view plain
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0   
Connected as tuser1  
   
SQL>   
SQL> CREATE OR REPLACE FUNCTION sum2(p_deptno IN NUMBER) RETURN NUMBER IS  
  2    l_ret NUMBER;  
  3  BEGIN  
  4    dbms_lock.sleep(5);  
  5    SELECT SUM(sal)  
  6    INTO   l_ret  
  7    FROM   emp  
  8    WHERE  deptno = p_deptno;  
  9    RETURN l_ret;  
 10  END sum2;  
 11  /  
   
Warning: Function created with compilation errors  
   
SQL> show err;  
Errors for FUNCTION TUSER1.SUM2:  
   
LINE/COL ERROR  
-------- --------------------------------------------------  
4/3      PLS-00201: identifier 'DBMS_LOCK' must be declared  
4/3      PL/SQL: Statement ignored  

 

很奇怪，难道不能在function里面用dbms_lock，google一下，找到答案：

必须把dbms_lock的execute权限直接grant给用户才行，只grant给角色就会报错。

grant execute sys.dbms_lock to uums; 

现在function可以正常创建了，不过为什么呢？怎么会存在grant to user和grant to role在某些object上有区别？

view plain
SQL>   
SQL> CREATE OR REPLACE FUNCTION sum2(p_deptno IN NUMBER)  
  2    RETURN NUMBER IS PRAGMA AUTONOMOUS_TRANSACTION;  
  3  l_ret NUMBER;  
  4  BEGIN  
  5    dbms_lock.sleep(5);  
  6    --DBMS_BACKUP_RESTORE.SLEEP(5);  
  7    dbms_output.put_line(systimestamp);  
  8    SELECT SUM(sal)  
  9    INTO   l_ret  
 10    FROM   emp  
 11    WHERE  deptno = p_deptno;  
 12    RETURN l_ret;  
 13  END sum2;  
 14  /  
   
Function created  
   
SQL>   

?