March 7, 2007

How to grant on v$ views

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

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)

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.