Wednesday 17 July 2013

How to grant on v$ views

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.

No comments:

Post a Comment