Tuesday, December 29, 2020

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



 grant SELECT access to v$ objects (exp - V$session) to other users?

we can’t grant direct access V$session because v$session is a synonym.

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

— V$SESSION is a public synonym
SQL> SELECT owner, object_type FROM dba_objects WHERE object_name = ‘V$SESSION’;

OWNER OBJECT_TYPE
———————— ——————-
PUBLIC SYNONYM

— the object (table/view) the synonym points to
SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’;

TABLE_OWNER TABLE_NAME
————– ——————————
SYS V_$SESSION

you cannot grant any privilege to v$ objects  or any other fixed views.  
however,you can grant directly option the base table:

SQL> GRANT SELECT ON V_$SESSION TO scott;

Grant succeeded.






No comments:

Post a Comment