[20160412]访问all_directorys视图问题.txt

--调试PL SQL脚本,遇到一个问题:

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING         VERSION    BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立测试问题:

--以scott用户登录:
SCOTT@book> SELECT count(*) FROM ALL_DIRECTORIES WHERE directory_name = UPPER ('DATA_PUMP_DIR');
COUNT(*)
--------
1

set serverout on
DECLARE
   v_count   NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO v_count
     FROM ALL_DIRECTORIES
    WHERE directory_name = UPPER ('DATA_PUMP_DIR');

   DBMS_OUTPUT.put_line ('count: ' || v_count);
END;
/
count: 1

CREATE OR REPLACE PROCEDURE test_a1 (p_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR')
AS
   v_count   NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO v_count
     FROM ALL_DIRECTORIES
    WHERE directory_name = UPPER (p_dir);

   DBMS_OUTPUT.put_line ('count: ' || v_count);
END;
/

SCOTT@book> exec test_a1('DATA_PUMP_DIR')
count: 0
PL/SQL procedure successfully completed.

--可以发现在存储过程中相似的sql语句返回不同的结果.

3.实际上这个问题角色授权问题:

我们知道，用户拥有的role权限在存储过程是不可用的。遇到这种情况，我们一般需要显式授权，如grant create table to usera;但这
种方法太麻烦，有时候可能需要进行非常多的授权才能执行存储过程，实际上，oracle给我们提供了在存储过程中使用role权限的方法：
修改存储过程，加入Authid Current_User时存储过程可以使用role权限。

CREATE OR REPLACE PROCEDURE test_a2  (p_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR') AUTHID CURRENT_USER
AS
   v_count   NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO v_count
     FROM ALL_DIRECTORIES
    WHERE directory_name = UPPER (p_dir);

   DBMS_OUTPUT.put_line ('count: ' || v_count);
END;
/

SCOTT@book> exec test_a2('DATA_PUMP_DIR')
count: 1
PL/SQL procedure successfully completed.

CREATE OR REPLACE PROCEDURE test_a3  (p_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR') AUTHID DEFINER
AS
   v_count   NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO v_count
     FROM ALL_DIRECTORIES
    WHERE directory_name = UPPER (p_dir);

   DBMS_OUTPUT.put_line ('count: ' || v_count);
END;
/

--这个结果与原来一样.

--ZALBB 提出 如下授权也可以.

grant all on directory DATA_PUMP_DIR to public;

SCOTT@book> grant all on directory DATA_PUMP_DIR to public;
Grant succeeded.

SCOTT@book> exec test_a1('DATA_PUMP_DIR')
count: 1
PL/SQL procedure successfully completed.

SCOTT@book> revoke all on directory DATA_PUMP_DIR from public;
Revoke succeeded.

SCOTT@book> exec test_a1('DATA_PUMP_DIR')
count: 0
PL/SQL procedure successfully completed.

--对于这些非常不熟悉,做一个记录.