Granting access on dynamic views (ORA-02030: can only select from fixed tables/views)

There were situations when the application team asks for access to dynamics views such as V$SESSION or similar dynamic views. Access on V$SESSION is required in order to monitor sessions from the application end, so this is a common request

Here, we will discuss, granting access to dynamic views, if you try to grant access to these dynamic views, it will throw ORA error ORA-02030 as snipped below

SQL> grant select on v$session to tech_owner;

grant select on v$session to tech_owner

*

ERROR at line 1:

ORA-02030: can only select from fixed tables/views 

SQL> 

The meaning of this error is, we can assign select access to tables and views only. We will see the object type of this V$SESSION dynamic view 

[Also Read - Oracle SCN]

SQL> select owner, object_name, object_type from dba_objects where object_Name='V$SESSION';

OWNER        OBJECT_NAME          OBJECT_TYPE

------------ -------------------- -------------------

PUBLIC       V$SESSION            SYNONYM

We can see, V$SESSION is neither view nor table, now we will see base object of this synonym

SQL> select owner, synonym_name, table_owner,

  2  table_name from dba_synonyms where synonym_name='V$SESSION';

OWNER        SYNONYM_NAME      TABLE_OWNER        TABLE_NAME

---------- ---------------- ------------------- ---------------------

PUBLIC       V$SESSION         SYS                 V_$SESSION 

here we can see the base object for synonym V$SESSION is V_$SESSION, now we will see the type of this object V_$SESSION 

SQL> select owner, object_name, object_type from dba_objects where object_Name='V_$SESSION';

OWNER        OBJECT_NAME          OBJECT_TYPE

------------ -------------------- -------------------

SYS          V_$SESSION           VIEW 

 We can see V_$SESSION is a view so we can grant access on V_$SESSION to get access on V$SESSION dynamic view  

SQL> grant select on v_$session to tech_owner;

 

Grant succeeded.