Monday, September 24, 2012

Granting Access to V$SESSION



I have the Oracle 9 version of this book and a colleague recently bought the Oracle 11.2 version. I found the idea for this post on page 554 of the newer version advertised above. It was tested on Oracle 11.2.

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>

No comments: