Oracle Spin – Flimatech Blog

Sharing Our Database Experience

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';

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

– shows 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

But one can grant access the underlying table/view.

SQL> GRANT SELECT ON V_$SESSION TO scott;

Grant succeeded.

Note: The same steps can be used to find other V$ views and access can be granted depending on internal representation.

About these ads

5 Responses to “How to grant SELECT access to v$session to other users?”

  1. Raghu said

    Yes its working fine Thanks…….!!!!!!

  2. VR said

    But it is possible to grant select on V_$SESSION to role and than grant this role to user without dba role ?

    • Amin Jaffer said

      Yes, it should work, i have tried it below.

      SQL> create role session_role;

      Role created.

      SQL> grant select on v_$session to session_role;

      Grant succeeded.

      SQL> grant session_role to scott;

      Grant succeeded.

      SQL> connect scott/tiger;
      Connected.
      SQL> select count(1) from V$session;

      COUNT(1)
      ———-
      17

  3. Samira said

    hi,
    but i give this error!

    SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION';

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

    SQL> GRANT SELECT ON V_$SESSION TO CONVERSION_USER;
    GRANT SELECT ON V_$SESSION TO CONVERSION_USER
    *
    ERROR at line 1:
    ORA-00980: synonym translation is no longer valid

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 40 other followers

%d bloggers like this: