RE: Log Miner Question

Since you said "I requested a log of user logins" I'm going to assume the users log directly into the server to access sqlplus...

Some ways to approach this from the O.S. side:

Does your SA have accounting enabled? The lastcomm command will tell which users where using SQLPLUS.
This info can also be derived from looking at the shell history file of users logged into the server during the time period in question.

There maybe other various auditing tools specific to AIX...

Thanks!=20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sam Bootsma Sent: Friday, May 20, 2005 6:37 AM
To: oracle-l_at_freelists.org
Subject: Log Miner Question

Hello,
=20

Last week we had a problem in our production environment due to a dropped Oracle package that invalidated a trigger. Our Development team was able to research the problem, then recreate the dropped package and compile the trigger. Nobody admitted to dropping the Oracle package so I used Log Miner to confirm that the package had been dropped, what day and time it was dropped, the Oracle user logged in, and the OS user logged in. The details immediately below come from v$logmnr_contents (select username, session#, serial#, to_char(timestamp,'yy-mon-dd hh:mi:ss') "time", session_info, sql_redo .....)

=20

SYSTEM                                222       2036 05-may-16 03:04:12

login_username=3DSYSTEM client_info=3D OS_username=3Dbanner Machine_name=3DTCC6C402 OS_t

erminal=3D OS_process_id=3D82044 OS_program name=3Dsqlplus_at_TCC6C402 (TNS V1-V3) =20

drop package BANINST1.WSKSTST; =20

=20

Note that the OS process id is included in session_info column of this virtual table. The problem is, I am not able to trace the OS process_id back to a user workstation. I requested a log of user logins from our AIX Administrator and he returned with a report that shows the Unix user id, terminal (eg. pts/2), the name or IP address connecting to the Unix box, and the time period of the connection. However, the log did not include Unix Process ID so I am not able to link back to the information provided by Log Miner. The System Administrator is currently following up with the vendor on how Unix Process ID can be obtained. Can anybody tell me if there is another way to trace the drop command back to a workstation? For example, does Log Miner store the terminal type in any of its tables?

=20

We are running Oracle 9.2.0.4 on AIX Unix 5. =20

=20

Our other Oracle DBA (who was dropping packages an hour earlier as part of cleanup tasks) is telling everybody this is a security breach. But I think it is much more likely to be human error.

=20

Thanks!

=20

Sam Bootsma

George Brown College

sbootsma_at_gbrownc.on.ca <mailto:sbootsma_at_gbrownc.on.ca>=20

416-415-5000 x4933

=20

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l