Home » SQL & PL/SQL » SQL & PL/SQL » v$access query
v$access query [message #195771] Mon, 02 October 2006 05:38 Go to next message
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???
Re: v$access query [message #195772 is a reply to message #195771] Mon, 02 October 2006 05:39 Go to previous messageGo to next message
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.
Re: v$access query [message #195774 is a reply to message #195772] Mon, 02 October 2006 05:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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>;
Re: v$access query [message #195852 is a reply to message #195774] Mon, 02 October 2006 16:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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.
Re: v$access query [message #195896 is a reply to message #195852] Tue, 03 October 2006 01:28 Go to previous messageGo to next message
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....
Re: v$access query [message #195912 is a reply to message #195896] Tue, 03 October 2006 02:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Read Barbara's reply
Re: v$access query [message #196050 is a reply to message #195896] Tue, 03 October 2006 17:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
grant select on v_$access to marian;
Re: v$access query [message #196083 is a reply to message #195771] Wed, 04 October 2006 01:36 Go to previous messageGo to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
It works Cool ..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..
Re: v$access query [message #196281 is a reply to message #196083] Wed, 04 October 2006 14:00 Go to previous message
Barbara Boehmer
Messages: 8625
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
Previous Topic: From my own session how to find outthe last sql that I issued? (merged 2 threads)
Next Topic: creating workflow
Goto Forum:
  


Current Time: Sat Dec 03 18:34:57 CST 2016

Total time taken to generate the page: 0.19456 seconds