Granting Access to Your Details in V$SESSION

You can allow a user to see details of his own session in V$SESSION like this. First do the following as SYS:

SQL> conn / as sysdba

Connected.

SQL> create view session_log as

  2  select * from v$session

  3  where username = user;

View created.

SQL> create public synonym session_log

  2  for session_log;

Synonym created.

SQL> grant select on session_log to public;

Grant succeeded.

SQL>

Now, still as SYS, create a user to test this out: 

SQL> grant create session

  2  to andrew identified by reid;

 

Grant succeeded.

SQL>

Connect as the user, show that you can only see 1 row in session_log and note its SID and SERIAL#:

 

SQL> conn andrew/reid

Connected.

SQL> select sid, serial# from session_log;

 

       SID    SERIAL#

---------- ----------

       589        141

 

SQL>

Leave that session logged in to SQL*Plus and reconnect as SYS. Confirm that the SID and SERIAL# for Andrew's session match those shown above:

SQL> conn / as sysdba

Connected.

SQL> select sid, serial# from v$session

  2  where username = 'ANDREW';

       SID    SERIAL#

---------- ----------

       589        141

SQL>