Find out who is connecting 2

[cleanair4me]

I tried using v$session to find out who is using a JDBC Client to connect to my schema (by way of a web application). The v$session gives me my program and schema info but was wondering if I can find out the exact workstation that is connecting to the database and using my schema?

 

[SantaMufasa]

Clean,

Here is my "logins.sql" script that you can run from your SQL*Plus prompt. It shows, for each session connected to the instance (including Oracle background processes):[ul][li]Oracle Username[/li][li]Operating-System Username[/li][li]Computer/Terminal from which the user connected[/li][li]Session IDentifier and Session Serial # (in single quotes for easy copy-and-paste in the event you want to issue an "ALTER SYSTEM KILL SESSION '<SID,Serial#>';" command).[/li][li]Session Status[/li][li]Operating System Process ID[/li][li]Session Logon Date/Time[/li][li]Program which the session is executing[/li][/ul]Here is the code:

set linesize 200
set echo off
set feedback on
col oruser format a20 heading "Oracle Username"
col osuser format a15 heading "O/S Username"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a20
col computer heading "Computer/Terminal" format a21
col program format a50 heading "Program"
col process format 999999 heading "OS|Process|ID"
select     username oruser
   ,osuser osuser
   ,decode(substr(machine,1,7),'TENFOLD',substr(machine,9),machine) computer
   ,''''||s.sid||','||s.serial#||'''' ss
   ,status
   ,process
   ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
   ,program
from       gv$session s
order by time
/
ttitle off

Let us know if this gives you what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[DrD123]

Hi cleanair4me,

Mufasa has the right answer from the Oracle perspective.

Another place to look since it is a web app connecting would be the webserver logs. Which also include any failed attempts, which good to know from a security stand point.

Not sure of the webserver in use, so I can't point out the location at the moment. But, in the logs you'll get the time of connection, IP address of connecting workstation, what pages they were accessing, the user login info, etc...



Good Luck
DrD

 

[cleanair4me]

Thanks!