Trace users connecting to DB not via standard methods 1

[JtheRipper]

Hi there,

I would like to know how I can trace all users connecting to the database using software like Excel, SQL Gold and so on.

The reason why I want to do this is because (and I am sure I am not the only person with this problem) we have users connecting to the database by not using only the applications given to them to do their work. They use Excelm SQL Gold an a variety of other tools to connect to the database. The problem with this is that the code in the applications is optimized for best performance, and these ad-hoc queries that the users submit are written so badly that is causes many performance issues. I know that if a user has a valid logon to the DB that he should be able connect from anywhere, but there must be a way to stop/monitor this kind of behaviour.

I know if I enable logging in the listener that it picks up some users connecting from i.e. Excel, but it does not pick up everything. I also thought of using v$session / v$process to maybe determine if the user connecting has a process on the unix server (our apps and DB are on the same server).

Any other ideas/comments very welcome.

Thanks,
J.

 

[Nuggie99]

The program field on v$session should work. It's buggy in that it won't work with if your DB is 8.1.7 and you client isn't, or if the DB is something else and the client is 8.1.7 However, if they are both the same (either both 8.1.7 or both not 8.1.7) that should give the program they are using to conenct.

 

[carp]

Here's a view I wrote for our DBA - she seems to get a lot of mileage out of it. Note that this is written for a database on which we're running Oracle Apps 11i; if you don't have Oracle Apps installed, then you will need to modify this to exclude the fnd_concurrent_requests table.

CREATE OR REPLACE VIEW XXDBA_DB_TO_UNIX_V ( REQUEST_ID, 
UNIX_PROCESS, SID, SERIAL#, CONCURRENT_PROGRAM_NAME, 
REQUESTOR ) AS SELECT r.request_id, 
       p.spid unix_process, 
       s.sid, 
       s.serial#, 
       t.user_concurrent_program_name concurrent_program_name, 
       u.user_name requestor 
FROM fnd_concurrent_requests r, 
     v$session s, 
     v$process p, 
     fnd_user u, 
     fnd_concurrent_programs_tl t 
WHERE r.requested_by = u.user_id 
  AND r.concurrent_program_id = t.concurrent_program_id 
  AND r.program_application_id = t.application_id 
  AND r.os_process_id = s.process 
  AND s.paddr = p.addr 
  AND r.phase_code='R' 
UNION ALL
select -1, 
       p.spid, 
       s.sid, 
       s.serial#, 
       s.program, 
       s.osuser 
FROM v$session s, 
     v$process p 
  WHERE s.paddr = p.addr
UNION ALL
SELECT r.request_id,
       '-1',
       -1,
       -1,
       t.user_concurrent_program_name concurrent_program_name,
       u.user_name requestor
FROM fnd_concurrent_requests r,
     fnd_user u,
     fnd_concurrent_programs_tl t
WHERE r.requested_by = u.user_id
  AND r.concurrent_program_id = t.concurrent_program_id
  AND r.program_application_id = t.application_id
  AND r.phase_code='R';

 

[JtheRipper]

Thanks for the input!

We are running 8.1.7.4.

Thanks for the code Carp, I will certainly have a look at it and update the post with any relevant feedback.

J.