8 responses to “How to grant SELECT access to v$session to other users?”

1. 

   Raghu

   June 29, 2009

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

   LikeLike

   Reply
2. 

   VR

   September 1, 2009

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

   LikeLike

   Reply
   1. 

      Amin Jaffer

      April 2, 2010

      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

      LikeLike

      Reply
3. 

   Samira

   February 27, 2010

   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

   LikeLike

   Reply
   1. 

      Amin Jaffer

      June 23, 2010

      http://www.dba-oracle.com/sf_ora_00980_synonym_translation_is_no_longer_valid.htm

      Check when you execute desc sys.v_$session or select * from sys.v_$session does that work?

      The underlying object is a view, it could be something doesn’t exists or could be invalid.

      SQL> select object_type, status from dba_objects where object_name = ‘V_$SESSION’;

      OBJECT_TYPE STATUS
      ——————- ——-
      VIEW VALID

      LikeLike

      Reply
4. 

   Carlos Arturo Castro Castro

   April 23, 2013

   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;

   LikeLike

   Reply
5. 

   anji

   June 13, 2013

   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

   LikeLike

   Reply
6. 

   amietshiravadekar

   April 27, 2021

   This article is really most lovely article and helpful article to give the session acess. These kind of questions are most commonly asked questions to dba. With this article you can also find out some most important complex sql interview questions https://www.complexsql.com/complex-sql-queries-examples-with-answers/ . I hope you like it.

   LikeLike

   Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.