非sys用户下存储过程对v$视图的使用(转) (

要在非sys用户下存储过程中使用v$xxx视图，还是尽量使用动态sql，并设置AUTHID CURRENT_USER特征让本存储过程可以使用当前用户的角色权限。[@more@] QL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

写一存储过程从v$datafile中获取记录数：

SQL> conn wacos/oss
Connected.
SQL> CREATE OR REPLACE PROCEDURE sp_test
2 IS
3 v_count PLS_INTEGER;
4 BEGIN
5 SELECT COUNT(*)
6 INTO v_count
7 FROM v$datafile;
8
9 DBMS_OUTPUT.put_line('v_count=' || v_count);
10 END;
11 /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE SP_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5 PL/SQL: SQL Statement ignored
7/12 PL/SQL: ORA-00942: table or view does not exist

上面提示不存在该视图，现加上该视图的select权限：

SQL> conn /as sysdba
Connected.
SQL> grant select on v$datafile to wacos;
grant select on v$datafile to wacos
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

v$xxx只是对应v_$xxx的同义词，应当给对应v_$xxx加select权限：

SQL> grant select on v_$datafile to wacos;

Grant succeeded.

重建该过程，并修改为从v_$datafile获取数据：

SQL> conn wacos/oss
Connected.
SQL> CREATE OR REPLACE PROCEDURE sp_test
2 IS
3 v_count PLS_INTEGER;
4 BEGIN
5 SELECT COUNT(*)
6 INTO v_count
7 FROM v_$datafile;
8
9 DBMS_OUTPUT.put_line('v_count=' || v_count);
10 END;
11 /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE SP_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5 PL/SQL: SQL Statement ignored
7/12 PL/SQL: ORA-00942: table or view does not exist

从上面看，还是提示该视图不存在，看看当前用户的表权限信息：

SQL> set lines 256
SQL> select * from user_tab_privs where table_name = 'V_$DATAFILE';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
WACOS SYS V_$DATAFILE SYS SELECT NO NO

再试试通过AUTHID CURRENT_USER使用户的角色可以用于存储过程中：

SQL> CREATE OR REPLACE PROCEDURE sp_test
2 AUTHID CURRENT_USER
3 IS
4 v_count PLS_INTEGER;
5 BEGIN
6 SELECT COUNT(*)
7 INTO v_count
8 FROM v_$datafile;
9
10 DBMS_OUTPUT.put_line('v_count=' || v_count);
11 END;
12 /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE SP_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5 PL/SQL: SQL Statement ignored
8/12 PL/SQL: ORA-00942: table or view does not exist
SQL>

还是提示表不存在，说明即使赋予了v_$xxx视图select权限给用户，还是无法在plsql中直接调用v$视图。

现在看看能否用动态sql来获取：

SQL> CREATE OR REPLACE PROCEDURE sp_test
2 IS
3 v_count PLS_INTEGER;
4 BEGIN
5 EXECUTE IMMEDIATE ' SELECT count(*) FROM v$datafile '
6 INTO v_count;
7
8 DBMS_OUTPUT.put_line('v_count=' || v_count);
9 END;
10 /

Procedure created.

SQL> set serverout on
SQL> exec sp_test;
v_count=23

PL/SQL procedure successfully completed.

说明在v_$xxx被赋予了select权限后，可以使用动态sql来调用对应v$视图。

把v_$xxx视图的select权限回收：

SQL> conn /as sysdba
Connected.
SQL> revoke select on v_$datafile from wacos;

Revoke succeeded.

SQL> conn wacos/oss
Connected.
SQL> select * from user_tab_privs where table_name = 'V_$DATAFILE';

no rows selected

再试试通过AUTHID CURRENT_USER使用户的角色可以用于存储过程中：

SQL> CREATE OR REPLACE PROCEDURE sp_test
2 AUTHID CURRENT_USER
3 IS
4 v_count PLS_INTEGER;
5 BEGIN
6 EXECUTE IMMEDIATE ' SELECT count(*) FROM v$datafile '
7 INTO v_count;
8
9 DBMS_OUTPUT.put_line('v_count=' || v_count);
10 END;
11 /

Procedure created.

SQL> set serverout on
SQL> exec sp_test;
v_count=23

PL/SQL procedure successfully completed.

通过以上测试表明：要在非sys用户下使用v$xxx视图，还是尽量使用动态sql，并设置AUTHID CURRENT_USER特征让本存储过程可以使用当前用户的角色权限。

--End--

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21704144/viewspace-1056919/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21704144/viewspace-1056919/