V$Views and Permissions. Interesting Issue.

I was working on creating a package to capture and store runtime execution plans for historical review and troubleshooting purposes. More on that later. While I was working on the package, I came across this interesting issue. I thought I might share this. In the snippet below, I have reduced the code to illustrate the issue at hand.

I named the schema "SQLCAPTURE". So going ahead with the example:

By logging in as SYS I create the SQLCAPTURE database user and assign privileges.

SQL> create user sqlcapture identified by sqlcapture;

User created.

SQL> grant dba,scheduler_admin to sqlcapture;

Grant succeeded.

Now I log in as SQLCAPTURE and Compile the Package.


THE COMPLETE CODE IN SQL_CAPTURE1 Script

CREATE OR REPLACE PACKAGE xx_plan_capture1 IS
PROCEDURE sqlcache_capture;
END;
/
CREATE OR REPLACE PACKAGE BODY xx_plan_capture1 IS
PROCEDURE sqlcache_capture IS
v_iteration NUMBER;
begin
SELECT count( a.sql_id ) into v_iteration
FROM v$sql_plan A where rownum <2;
dbms_output.put_line(v_iteration);
END; --sqlcache_capture

END; --package body
/

SQL> conn sqlcapture/sqlcapture
Connected.
SQL> @E:\sql_capture1
Package created.


Warning: Package Body created with compilation errors.

It returns an error. Upon finding out the offending statement:

SQL> sho err
Errors for PACKAGE BODY XX_PLAN_CAPTURE1:

LINE/COL ERROR
-------- --------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
6/6 PL/SQL: ORA-00942: table or view does not exist
SQL> l 6
6* FROM v$sql_plan A where rownum <2; href="https://metalink.oracle.com/metalink/plsql/f?p=130:14:5850005794970861733::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,1062335.6,1,1,1,helvetica">Metalink Note), I do that.

SQL> sho user
USER is "SYS"
SQL> grant select on v_$sql_plan to sqlcapture;

SQL> sho user
USER is "SQLCAPTURE"
SQL> @E:\sql_capture1

Package created.


Package body created.

All is well, the Package and Package Body is created successfully.

Now comes the interesting part.

By logging in as SYS, I revoke the grant on V_$SQL_PLAN
SQL> revoke select on v_$sql_plan from sqlcapture;

Then as SQLCAPTURE, I recompile the script.

SQL> sho user
USER is "SQLCAPTURE"
SQL> @E:\sql_capture1

Package created.


Package body created.
SQL> col object_name for a19
SQL> select object_name,object_type,status from user_objects where object_type like 'PACKAGE%';

OBJECT_NAME OBJECT_TYPE STATUS
------------------- ------------------- -------
XX_PLAN_CAPTURE1 PACKAGE VALID
XX_PLAN_CAPTURE1 PACKAGE BODY VALID

It Compiles!!!!!

Though at runtime it fails with 942, Interesting to know it compiled.

I guess the takeaway is while coding your own scripts using V$views, it is safer to use the underlying (SYS) tables. Otherwise, at runtime you might
end up with ugly errors and unsuccessful execution of your procedures.

I could not reproduce this in 11g as the package compiles in 11g without even having to give the GRANT on the base table.