Skip to Main Content
  • Questions
  • v$ table access from stored procedures

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, karl.

Asked: August 21, 2004 - 3:26 pm UTC

Last updated: September 17, 2016 - 3:05 pm UTC

Version: 9.2.0

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I want to select from v$ tables inside stored procedures. Before Oracle9 if I had "select any table" privileges there was no problem doing so.

With Oracle9, "select any table" privileges no longer allow direct access to the v$ tables. Even dba privileges, which allow direct access to the v$ tables, do not allow access by stored procedures. This can be seen in the following example.

SQL> conn / as sysdba
Connected.
SQL> grant dba to bill identified by bill;

Grant succeeded.

SQL> connect bill/bill
Connected.
SQL> select machine from v$session where sid=1;

MACHINE
----------------------------------------------------------------
MOZART

1 row selected.
SQL>
SQL> create or replace procedure get_machine (machine OUT varchar2 )
2 authid current_user
3 is
4 begin
5 select machine
6 into machine
7 from v$session
8 where sid = 1;
9 end;
10 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE GET_MACHINE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
7/6 PL/SQL: ORA-00942: table or view does not exist
SQL>
SQL>
SQL>
SQL> drop procedure get_machine
2 /

Procedure dropped.
SQL>
SQL>
SQL>



It looks like Bill doesn't have access to v$session inside the stored procedure.

Some experimentation led me to the following solution:
- let sys be the owner of the stored procedure
- create a public synonym for the procedure
- grant execute privileges to bill.

The SQL*PLUS example below provides all the details.

My questions are:

1. why did Oracle9 make me go through these contortions to do
something rather basic?
2. can you suggest a better way that (a) does not involve making sys
the owner of the stored proc and (b) does not require bill to have
dba privileges.

Thanks,

Karl


SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL> create or replace procedure get_machine (machine OUT varchar2 )
2 authid current_user
3 is
4 begin
5 select machine
6 into machine
7 from v$session
8 where sid = 1;
9 end;
10 /
SQL>
SQL>

Procedure created.

SQL>
SQL> create public synonym get_machine for get_machine;

Synonym created.

Elapsed: 00:00:00.00
SQL>
SQL> grant execute on get_machine to bill;

Grant succeeded.

SQL>
SQL>
SQL> conn bill/bill
Connected.
SQL>
SQL>
SQL>
SQL> var m varchar2(50)
SQL>
SQL> exec get_machine(:m)

PL/SQL procedure successfully completed.

SQL> print m

M
------------------------------------------------------------
MOZART

SQL>




and Tom said...

1) security, to lock down the data dictionary. you can "disable it" (07 dictionary accessibility is the parameter), but you should not.

all you need to do is "grant select on v_$session to whomever" and it'll work.

2) see #1.

Here is a script for example that I use to "recreateme" -- recreate my account, for testing purposes. It gives me access to all of the v_$* views:

@connect "/ as sysdba"
set echo on
drop user ops$tkyte cascade;
create user ops$tkyte identified externally;
grant connect, dba to ops$tkyte;
alter user ops$tkyte default tablespace users;

begin
for x in ( select object_name from user_objects where object_type = 'VIEW' and
object_name like 'V\_$%' escape '\' )
loop
execute immediate 'grant select on ' || x.object_name || ' to ops$tkyte';
end loop;
end;
/


@connect /



Rating

  (16 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I wish I had known this 2 years ago

Karl Veselic, August 21, 2004 - 6:11 pm UTC

I spent hours coming up with my workaround, when
all I needed to know was to
grant select on v_$session to whomever

But it's still not clear to me why

grant select any table to whomever

isn't more powerful than your grant.

Thanks!
Karl

By the way, your script is wonderful; I shall
make much use of it.


Tom Kyte
August 21, 2004 - 8:15 pm UTC

because SYS owned objects are protected "especially well" by default in 9i for security.

grant select any dictionary to my_user ;

pasko, August 23, 2004 - 3:50 am UTC

Hi Tom,

could we also use this to grant select on V$_* Views ?


"grant select any dictionary to .....; "


Tom Kyte
August 23, 2004 - 7:57 am UTC

a@ORA9IR2> @connect "/ as sysdba"
a@ORA9IR2> set termout off
sys@ORA9IR2> set termout on
sys@ORA9IR2>
sys@ORA9IR2> drop user a cascade;

User dropped.

sys@ORA9IR2>
sys@ORA9IR2> create user a identified by a;

User created.

sys@ORA9IR2>
sys@ORA9IR2> grant create session to a;

Grant succeeded.

sys@ORA9IR2> grant select any dictionary to a;

Grant succeeded.

sys@ORA9IR2> grant create procedure to a;

Grant succeeded.

sys@ORA9IR2>
sys@ORA9IR2> @connect a/a
sys@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2>
a@ORA9IR2> create or replace procedure p
2 as
3 begin
4 for x in ( select * from v$session ) loop null; end loop;
5 end;
6 /

Procedure created.

a@ORA9IR2>



good idea, shorter than my approach :)


select_catalog_role

Arun Gupta, August 23, 2004 - 8:53 am UTC

Tom,
What is the difference between select_catalog_role and select any dictionary system privilege? I read the Metalink note but could not figure out the difference.
Thanks.


Tom Kyte
August 23, 2004 - 9:00 am UTC

select catalog role is a role, roles are not enabled during the compilation of plsql/views and are not ever enabled during the execution of definers rights procedures

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

so, select catalog role would let you write queries in sqlplus for example, but not create a procedure (as select any dictionary, the system privilege, does)

A reader, September 15, 2004 - 10:45 am UTC


Prasad, November 29, 2004 - 1:03 am UTC


A reader, January 17, 2005 - 4:02 pm UTC


v$session - any security risk?

Tim, March 17, 2005 - 1:35 pm UTC

Our DBA is refusing to grant our application's Oracle username access to v$session. He is citing security as a reason. (Reason for request is to see what the program/module initiating the database session is. We know this is easy to spoof - but it helps a little bit until we can go to something secure such as proxy authentication.)

I have suggested creating a view in a schema which does have a direct grant to v$session and only granting our application user a grant to this new view.

However, this was also called a security risk. Do you have thoughts on what security risks this would cause (if any) and if so, can this information be obtained in another manner which would not be a security risk?

This is on an Oracle 9iR2 database.

The view which I suggested is as follows:

CREATE OR REPLACE VIEW v_session_info (
audsid,
process,
program,
module,
logon_time)
AS
select
audsid,
process,
program,
module,
logon_time
from v$session
where sys_context('userenv','sessionid') = audsid;
/

Thanks.


Tom Kyte
March 17, 2005 - 2:04 pm UTC

i would use a where clause of


where sid = (select sid from v$mystat where rownum=1)

myself, but tell the dba unless you get this view, you HAVE a security risk and if he cannot specify what the security risk involved in letting you see your sessions record is, you'll just have to tell you management chain that "no, we cannot do what you asked for the dba won't help us accomplish the goal you gave us"

select any dictionary...

Kashif, June 17, 2005 - 5:09 pm UTC

Hey Tom -

Isn't this system privilege a tad too powerful? I mean it essentially circumvents the security provided by O7_DICTIONARY_ACCESSIBILITY, plus if you just needed access to a finite number of V$ views (like the original poster did) then wouldn't it be better to simply grant privileges on those views individually (as you had originally suggested)? I'm trying to provide some developers with similar privileges for some views they need to create on top of some V$ views, and was wondering what the best approach was. Thanks for any feedback.

Kashif

Tom Kyte
June 17, 2005 - 5:11 pm UTC

not all of the security provided. (no access to sys.link$ for example)


It is safe for development environment where the developers should be able to see all of the DBA views, the V$ views.

Hmm.

Kashif, June 17, 2005 - 5:31 pm UTC

Thanks Tom. I don't think O7_DICTIONARY_ACCESSIBILITY provides access to sys.link$ either, though I'm itching to get out of here and won't be able to test it till later, it's way too nice to be indoors... ;)

Anyway, I think I still prefer the grant-as-you-need method of granting access to the data dictionary, select any dictionary seems more than what is required, at least in this scenario.

Have a good one.

Kashif

Tom Kyte
June 17, 2005 - 6:53 pm UTC

it doesn't - my point was select any dictionary won't either.


select on few data dictionary views ora-01031

pjp, May 18, 2006 - 2:44 am UTC

Hi Tom,

When I am trying to give grant "select" on few data dictionary views I am getting following error

SQL> grant select on v$session to parag;
grant select on v$session to parag;
                *
ERROR at line 1:
ORA-01031: insufficient privileges

How can I do this ? I do not want to give "select any dictionary" priv.

This question is for my learning only.

thanks & regards
pjp 

Tom Kyte
May 19, 2006 - 9:03 am UTC

you don't have the privilege to grant select on that object is what that means.  

you do not say who you are logged in as, nor the version, nor much of any sort of detail.


ops$tkyte@ORA10GR2> grant select on v$session to scott;
grant select on v$session to scott
                *
ERROR at line 1:
ORA-01031: insufficient privileges


ops$tkyte@ORA10GR2> connect / as sysdba;
Connected.

sys@ORA10GR2> grant select on v_$session to ops$tkyte with grant option;
Grant succeeded.

sys@ORA10GR2> connect /
Connected.

ops$tkyte@ORA10GR2> grant select on v$session to scott;
Grant succeeded.
 

something different?

Mark Wooldridge, July 31, 2006 - 6:08 pm UTC

  1* grant select on v$session to hdfs
SQL> /
grant select on v$session to hdfs
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL> select *
  2  from v$instance
  3  /

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 hdfn
gitrdone
10.2.0.2.0        27-JUL-06 OPEN         NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO 

Tom Kyte
July 31, 2006 - 9:04 pm UTC

v_$session can be used in that case

Privilege for USER_ dictionary views

Asim, July 12, 2007 - 10:47 am UTC

Hi Tom,

We have a stored procedure which does query to dba_tables, dba_sub_partitions, dba_indexes, dba_ind_columns etc.

Now we want to avoid the grants needed for production deployment. So I have tried using user_tables, user_sub_partitions, user_indexes etc inside the stored procedure and it works because I am looking for only information from those views which I own.

So just wanted to confirm with you that is there any specific grant needed to access "user" views from the owner schema inside the stored procedure? The stored procedure will remain in owner schema and it will be executed from an user schema?

Does authid matters for this case?

Tom Kyte
July 12, 2007 - 12:12 pm UTC

you only need to remember that the user views will return the current schema's information - so no matter what user runs that procedure, they will see the OWNER OF THAT PROCEDURE's data only.

USER dictionary views access

Asim, July 12, 2007 - 12:23 pm UTC

Hi Tom,
Thank you for confirming that.
These are the views which stored procedure is referring right now and we want to replace them with "user" views.
DBA_TAB_SUBPARTITIONS
DBA_TABLES
DBA_IND_COLUMNS
DBA_INDEXES
DBA_CONSTRAINTS
DBA_CONS_COLUMNS

Yes we do need only the information for the schema in which the stored procedure will be present. So I believe we should be ok.


Can you tell me what about DBMS_STATS.GATHER_TABLE_STATS?
I think we only need the privilege to run this from the owner schema for the owner table.

Thanks,
Asim

Tom Kyte
July 12, 2007 - 1:07 pm UTC

you can analyze your own tables using dbms_stats, yes.

grant select on SYS views to developers

Atta, September 11, 2013 - 12:37 pm UTC

Hello Tom,
I've seen above your quote 'It is safe for development environment where the developers should be able to see all of the DBA views, the V$ views.

I wonder why my DBA sees 'availability, correctness and consistency' of development database compromised and asks to me which specific views I need to see. Of course to do properly my job, I can't know exactly in advance which views from sys I will need.

Do you see any real reasons for not being allowed to see any v$ view ?

My guess is that he just doesn't know which are the views to stay hidden ( are they ? ), but at the end we speak about Development.

I personally assume also he's never been a good developer to understand that necessity some developers have for not saying a good DBA.
Tom Kyte
September 23, 2013 - 5:47 pm UTC

I do don't see why developers should not have access to the v$ information in test/dev - including full access to ASH and AWR.

v$ view access over dblink from package

Ian, December 05, 2014 - 2:48 pm UTC

Hi Tom

Welcome back!

I have a similar issue. I am trying to access gv$instance over a dblink from within a package.

I have granted select to gv_$instance to the dblink end user.

I can select in SQL*Plus from gv$instance@my_dblink with no problem.

Initially I could also do the same from an anonymous PL/SQL block and even a package.

I then added gv$active_session_history (same grant to gv_$active_session_history, same ability to select from SQL*Plus) to the anonymous PL/SQL block but got the following errors:

ORA-04052: error occurred when looking up remote object SYS.GV_$ACTIVE_SESSION_HISTORY@XXPX_TEMP_P1
ORA-02030: can only select from fixed tables/views

Now this is where it gets odd. I went back to the original anonymous block (just gv$instance) and got the same errors.

I then double checked I could use SQL*Plus to select from the gv$ view - no problem.

So it used to work in PL/SQL with gv$instance - but now it doesn't.

NB - The dblink is a loopback to the same database.

Any ideas? I found one hit on this site from someone else getting intermittent problems with gv$ views over dblinks - but there was no follow up. Nothing relevant on Metalink.

Regards

Ian
Tom Kyte
December 07, 2014 - 12:22 pm UTC

I'm sorry, I cannot reproduce, I tried...

I'll have to refer you to support (an SR). They can help you enable some tracing that might be of assistance.

One workaround to try would be to create a view of the remote object and select from the view - can you try that?

A reader, September 17, 2016 - 11:29 am UTC

Suppose I have a three procedure like proc_1, proc_2 ans proc3.
but they also have a different code but following query is a common of them.
Query :- " select * from employee e where e.emp_id=:proc_argument "

so when procedure calls.
sql statement appears in a v$sql and v$session.
but I want ti identify which procedure is call like proc_1 or proc_2 or proc_3?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library