[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.
--对于这些非常不熟悉,做一个记录.