OraFAQ Forum: SQL & PL/SQL » v$access query

Home » SQL & PL/SQL » SQL & PL/SQL » v$access query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

v$access query [message #195771]

Mon, 02 October 2006 05:38

user82
Messages: 49
Registered: June 2006
Location: Romania

Member

Hy...i need to get some informations from v$access view...i need to query this view from a function but i can't, so i tried to make my own view but i get the following error:

SQL> create view v_access as select * from v$access;
ORA-01031: insufficient privileges

The users has DBA role and i gave it privileges to create any view....

Can anyone help me handle this problem???

Report message to a moderator

Re: v$access query [message #195772 is a reply to message #195771]

Mon, 02 October 2006 05:39

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Grant select on v$access to the user directly, rather than via a role.

Report message to a moderator

Re: v$access query [message #195774 is a reply to message #195772]

Mon, 02 October 2006 05:45

user82
Messages: 49
Registered: June 2006
Location: Romania

Member

i did...but still doesn't work..... Mad

i get the following error:

SQL> grant select on v$access to marian;
ORA-01031: insufficient privileges

[Updated on: Mon, 02 October 2006 05:48]

Report message to a moderator

Re: v$access query [message #195783 is a reply to message #195774]

Mon, 02 October 2006 06:49

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

That's because you have to issue the GRANT statement as a user who has the privs to grant those rights.
In the case of V$ views, that's SYS.

If you're going to need to grant this priv several times, your best bet is:

Connect as SYS

GRANT SELECT ON v$access TO <Dba account> WITH GRANT OPTION;

Connect as <dba account>

GRANT SELECT ON V$access TO <user>;

Report message to a moderator

Re: v$access query [message #195852 is a reply to message #195774]

Mon, 02 October 2006 16:01

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

It is also insufficient to just grant select on the v$access view. You need to grant select on v_$access, the underlying base table of the view.

Report message to a moderator

Re: v$access query [message #195896 is a reply to message #195852]

Tue, 03 October 2006 01:28

user82
Messages: 49
Registered: June 2006
Location: Romania

Member

thanks for advicing me but doesn't work...look what happened:

SQL>connect sys/<pass> as sysdba

SQL> grant select on v$access to marian with grant option;

ERROR at line 1:
ORA-02030: can only select from fixed tables/views

If there is another ideea please let me know...anyway i think i must change the logic of the application....

Report message to a moderator

Re: v$access query [message #195912 is a reply to message #195896]

Tue, 03 October 2006 02:26

Frank
Messages: 7901
Registered: March 2000

Senior Member

Read Barbara's reply

Report message to a moderator

Re: v$access query [message #196050 is a reply to message #195896]

Tue, 03 October 2006 17:04

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

grant select on v_$access to marian;

Report message to a moderator

Re: v$access query [message #196083 is a reply to message #195771]

Wed, 04 October 2006 01:36

user82
Messages: 49
Registered: June 2006
Location: Romania

Member

It works

..thanks for your pertinent advice but i have a little question...can you tell me or give a link to a documentation about the difference between v$ views an v_$ views (for example v$access an v_$access)...thnaks..

Report message to a moderator

Re: v$access query [message #196281 is a reply to message #196083]

Wed, 04 October 2006 14:00

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

The v$... views are public synonyms for the v_$... views and tables. Ordinarily, you just select from them using the v$... public synonyms that everybody has access to. However, when you are trying to use such a view within a procedure or another view, where role privileges are not applied, then you must grant select on the underlying base v_$... tables or views directly, not through a role.

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1001.htm

Report message to a moderator

Previous Topic:	From my own session how to find outthe last sql that I issued? (merged 2 threads)
Next Topic:	creating workflow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 08:20:14 CST 2024