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

最近想写个存储过程，定时调度，kill active session。脚本写的没问题，可以执行。用到了V$Process，放存储过程里面一直报表或视图不存在的错误，怎么也编译不通过。在网上搜了半天，终于搜到了下面这篇文章，把这个核心问题解决了。虽然存储过程还没完成，但是也差不多了。

收藏一下这篇文章。

 

 

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

SQL> 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特征让本存储过程可以使用当前用户的角色权限。