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.
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