How to grant SELECT access to v$session to other users?
Posted by Amin Jaffer on December 18, 2008
One can’t grant direct access V$session as v$session is a synonym.
SQL> GRANT SELECT ON v$session TO scott;
grant select on v$session to test
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
— shows the V$SESSION is a public synonym
SQL> SELECT owner, object_type FROM dba_objects WHERE object_name = ‘V$SESSION';
— shows the object (table/view) the synonym points to
SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION';
But one can grant access the underlying table/view.
SQL> GRANT SELECT ON V_$SESSION TO scott;
Note: The same steps can be used to find other V$ views and access can be granted depending on internal representation.