ORA-02030
Tried to grant SELECT object privilege of V$SESSION to an user, but it failed with ORA-02030.
SQL> grant select on v$session to erpapp;
grant select on v$session to erpapp
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
ORA-02030 means that the type of object you want to grant SELECT privilege to other users cannot not be done, you should find the right object to be granted.
SYNONYM
Let's see what object type of it.
SQL> select * from all_objects where object_type in ('TABLE', 'VIEW') and object_name = 'V$SESSION';
no rows selected
OK, then what is the object type of V$SESSION.
SQL> select object_type from all_objects where object_name = 'V$SESSION';
OBJECT_TYPE
-----------------------
SYNONYM
Oh, it's a synonym. Type SYNONYM is just an alias of another object, it cannot be granted with SELECT privilege, only TABLE and VIEW can.
Now, the question is, what is the base object of this synonym? Let's continue.
TABLE or VIEW
Let's check the underlying object by querying ALL_SYNONYMS.
SQL> select table_name from all_synonyms where synonym_name = 'V$SESSION';
TABLE_NAME
-----------------------
V_$SESSION
SQL> select object_type from all_objects where object_name = 'V_$SESSION';
OBJECT_TYPE
-----------------------
VIEW
OK, the base object should be V_$SESSION and it's a view, now we can grant the correct object to the user.
SQL> grant select on v_$session to erpapp;
Grant succeeded.
We made it.
Not only V$SESSION, but also V$SQL and V$DATABASE can not be granted with SELECT privilege directly, we should find the right object type to do it.