Granting permissions on v$ views

Sometimes we may get a requirement in which we need to provide access on dynamic performance views like v$session, v$process etc. I had seen this when application team want to capture session information from their webpages.

In such situations, application team will ask to grant select on those views. when you try the same as just like normal grant statement, you will get following error

SQL> grant select on v$session to PAST1;
grant select on v$session to PAST1
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

The reason for this is v$session is synonym to the view v_$session. so you need to grant select on that main view instead of synonym

SQL> grant select on v_$session to PAST1;

Grant succeeded.

Same you need to follow for all other v$ views. But for data dictionary views, you can directly grant permissions

SQL> grant select on dba_users to PAST1;

Grant succeeded.

Note : Due to security reasons, never we should encourage granting permissions on data dictionary of Oracle until very much required