Today i was faced with an error when I try to give permission on a v$view
SQL> grant select on v$session to hr;
grant select on v$session to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
I wonder why i couldn’t give select privilage to a v$ view. I tried to give to permission to another v$ views but the error was same. Action for this error on error codes manual was meaningless to me (Action: You may only select rows from fixed tables/views.)
From a little googling effort i saw that the problem is caused because of trying to give select privilage on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym.
If you want to give permission to a V$ view you must give it like below
SQL> grant select on v_$session to hr;
Grant succeeded.
Coskan's Approach to Oracle 
oh.. i find it. thanks a lot, u really helped me
Comment by pRozaC — February 17, 2008 @ 1:47 am
yup. thanks for good post
Comment by /zyloprim/ — February 26, 2008 @ 12:49 am
This really helped me. Thanks a lot
Comment by naresh — March 17, 2008 @ 3:05 pm
Thank you for this post, it helped me too, as I revoked “select any table” grant.
Comment by Calin — March 18, 2008 @ 2:34 pm
Thx! You helped me a lot again! Cheers!!!
Comment by pinlaser — March 28, 2008 @ 6:22 am
Thanks for the tip!!!!
Comment by Cesar — April 1, 2008 @ 5:59 pm
Thanks for the tip I don’t always have time to RTFM. Like many other people I am not a dba and don’t have infinate time to research something I might use once a year. you saved my day!
Comment by Pete B — May 20, 2008 @ 10:31 am
Very usefull tip.
Comment by Murugesh — August 7, 2008 @ 1:02 pm
This helped me a lot. Great tip!!! Thx
Comment by Gurmel — September 25, 2008 @ 10:54 pm
Hey real great tip :)!
Comment by jov — October 26, 2008 @ 4:03 pm
Greatest help!
Comment by Vishal — February 10, 2009 @ 6:46 am
how to check this given privilege.
Comment by feroz — September 2, 2009 @ 11:13 am
SQL> select * from dba_tab_privs where grantee=’HR’;
GRANTEE OWNE TABLE_NAME GRANTOR PRIVILEGE GRA HIE
——- —- ———- ——- ——— — —
HR SYS DBMS_STATS SYS EXECUTE NO NO
SQL> grant select on v_$session to hr;
Grant succeeded.
SQL> select * from dba_tab_privs where grantee=’HR’;
GRANTEE OWNE TABLE_NAME GRANTOR PRIVILEGE GRA HIE
——- —- ———- ——- ——— — —
HR SYS V_$SESSION SYS SELECT NO NO
HR SYS DBMS_STATS SYS EXECUTE NO NO
Comment by coskan — September 7, 2009 @ 12:16 pm
Gr8 Help..thanks
Comment by Dharan — September 22, 2009 @ 10:25 am
Thanks a lot.. it helped me
Comment by Arun — March 3, 2010 @ 9:25 pm
The problem is not the synonym: the synonym transmits the GRANT command (or whatever) if it can. When you (SYS) execute “grant select on v$ session” (v$session is owned by PUBLIC), it is as if PUBLIC was trying to do (I don’t know if my English is correct)
Consider the following:
SQL> show user
USER is “SYS”
SQL> select grantee, owner, table_name, grantor, privilege
2 from dba_tab_privs
3 where table_name = ‘V_$SESSION’ and grantee = ‘HR’;
no rows selected
SQL> create synonym sys_v$session for v_$session;
Synonym created.
SQL> grant select on sys_v$session to hr;
Grant succeeded.
SQL> select grantee, owner, table_name, grantor, privilege
2 from dba_tab_privs
3 where table_name = ‘V_$SESSION’ and grantee = ‘HR’;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
———- ———- ———- ———- ———-
HR SYS V_$SESSION SYS SELECT
Comment by Roberto — April 28, 2010 @ 12:13 pm
Hello Thank you very much this really hellped me thanks
Comment by samir — July 11, 2010 @ 2:23 pm
i get error for the above sql..
Error at line 1:
ora-00942 : table or view does not exist
Comment by Mukesh — September 23, 2010 @ 1:32 pm
Thanks a lot . It worked for me by granting privilege to v_$database
Comment by Vijay — May 11, 2011 @ 1:11 pm
how to find whether where clause is present in a query or not?
Comment by sivakumar — June 9, 2011 @ 2:53 am
Thank you
Comment by hi — February 18, 2012 @ 4:45 am
Hi Coskan, first I appreciate your information. But you said synonym cannot be granted to a user. But I experimented it.
I first created a view like “v_$gogo” and created a synonym for it as “v$gogo” as Sys user and granted all privilege on v$gogo synonym to a user. It succeeds in this case. Then how come it could’t be done with “real” v$views? Something is there. Kindly, share it if you are already aware of it.
Comment by Vimal Rathinasamy (@valiantvimal) — December 1, 2012 @ 5:30 am
Good One.. Really helped me !!
Comment by Sudheer — December 5, 2012 @ 7:11 am
I was suggested this web site by my cousin. I am not sure whether this post is
written by him as no oone else know such detailed about my difficulty.
You’re amazing! Thanks!
Comment by http://digsitevalue.org/s/bling.ie — September 8, 2013 @ 6:21 am
how to give grant on run time views
Comment by mark — August 14, 2014 @ 5:08 pm
perceive that “bad things” keep happening to us, it is important to shift those negative thoughts to
Oakley Flak Jacket Sunglasses http://www.ebuyaccessories.com
Comment by Oakley Flak Jacket Sunglasses — September 18, 2014 @ 1:10 am
Hi there everyone, it’s my first pay a quick visit at this web site, and
piece of writing is in fact fruitful in favor of me, keep up posting these types
of articles.
Comment by brands — September 23, 2014 @ 4:06 am
“….and you can’t give privilage on a synonym.” – This is wrong – it’s general possible to give a privilege to a SYNONYM!
The reason for this behavior must be different…but I also don’t know.
Comment by st_guitar — January 6, 2015 @ 8:43 am
Thanks you!!!
Comment by wahid khan — May 31, 2015 @ 8:59 am
Often we need to wait for things to unfold gloriously and to take time to listen to the angels. Good things come to those who wait to accept them with a grateful heart.see more
Comment by Tom — June 27, 2015 @ 6:35 am
Thank you sir. This was super helpful.
Comment by phil — July 11, 2015 @ 9:54 pm