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