Coskan’s Approach to Oracle

March 7, 2007

How to grant on v$ views

Filed under: How To — coskan @ 3:07 pm

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.

About these ads

25 Comments »

  1. oh.. i find it. thanks a lot, u really helped me

    Comment by pRozaC — February 17, 2008 @ 1:47 am

  2. yup. thanks for good post

    Comment by /zyloprim/ — February 26, 2008 @ 12:49 am

  3. This really helped me. Thanks a lot

    Comment by naresh — March 17, 2008 @ 3:05 pm

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

  5. Thx! You helped me a lot again! Cheers!!!

    Comment by pinlaser — March 28, 2008 @ 6:22 am

  6. Thanks for the tip!!!!

    Comment by Cesar — April 1, 2008 @ 5:59 pm

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

  8. Very usefull tip.

    Comment by Murugesh — August 7, 2008 @ 1:02 pm

  9. This helped me a lot. Great tip!!! Thx

    Comment by Gurmel — September 25, 2008 @ 10:54 pm

  10. Hey real great tip :)!

    Comment by jov — October 26, 2008 @ 4:03 pm

  11. Greatest help!

    Comment by Vishal — February 10, 2009 @ 6:46 am

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

  13. Gr8 Help..thanks

    Comment by Dharan — September 22, 2009 @ 10:25 am

  14. Thanks a lot.. it helped me

    Comment by Arun — March 3, 2010 @ 9:25 pm

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

  16. Hello Thank you very much this really hellped me thanks

    Comment by samir — July 11, 2010 @ 2:23 pm

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

  18. Thanks a lot . It worked for me by granting privilege to v_$database

    Comment by Vijay — May 11, 2011 @ 1:11 pm

  19. how to find whether where clause is present in a query or not?

    Comment by sivakumar — June 9, 2011 @ 2:53 am

  20. Thank you

    Comment by hi — February 18, 2012 @ 4:45 am

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

  22. Good One.. Really helped me !!

    Comment by Sudheer — December 5, 2012 @ 7:11 am

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

  24. how to give grant on run time views

    Comment by mark — August 14, 2014 @ 5:08 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: