日期:2014-05-16  浏览次数:20405 次

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>   

?