How to grant SELECT access to v$session to other users?

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.

7 thoughts on “How to grant SELECT access to v$session to other users?

    • 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

  1. 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

  2. CREATE TABLESPACE TBS_TEST DATAFILE ‘C:\oraclexe\app\oracle\oradata\XE\DTF_TEST.DBF’ SIZE 100M AUTOEXTEND ON;
    CREATE USER USR_TEST IDENTIFIED BY MEDELLIN2013 DEFAULT TABLESPACE TBS_TEST;
    GRANT CONNECT, RESOURCE TO USR_TEST;

    SQL> CONNECT / AS SYSDBA;
    Connected.
    SQL> GRANT SELECT ON V_$SESSION TO USR_TEST;
    Grant succeeded.

    CREATE TABLE TEST(
    ID NUMBER PRIMARY KEY,
    NOM VARCHAR(100) NOT NULL);

    CREATE TABLE AUDITORIA(
    ID NUMBER PRIMARY KEY,
    FECHA DATE,
    CAMANDO VARCHAR(100),
    IDTEST NUMBER,
    NOMTEST VARCHAR(100),
    USUARIO VARCHAR(100)
    );

    CREATE SEQUENCE SEQ_IDAUDITORIA;

    INSERT INTO TEST VALUES(1,’111′);
    INSERT INTO TEST VALUES(2,’222′);
    INSERT INTO TEST VALUES(3,’333′);
    INSERT INTO TEST VALUES(4,’444′);
    INSERT INTO TEST VALUES(5,’555′);

    create or replace
    trigger TRG_AUDITORIA
    BEFORE DELETE ON TEST
    FOR EACH ROW
    DECLARE
    NOMUSUARIO VARCHAR(100);
    NOMEQUIPO VARCHAR(100);
    BEGIN
    SELECT (SELECT distinct SYS.V_$SESSION.MACHINE FROM SYS.V_$SESSION WHERE ROWNUM=1) INTO NOMEQUIPO FROM DUAL;
    SELECT USER INTO NOMUSUARIO FROM DUAL;
    INSERT INTO AUDITORIA VALUES(SEQ_IDAUDITORIA.NEXTVAL,SYSDATE,’DELETE’,:OLD.ID,:OLD.NOM,NOMUSUARIO,NOMEQUIPO);
    END;

  3. Hi
    my user having dba privilege
    but when i give grants on v$session to another user it is giving error as follows
    insuffiecient privileges

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