Home » RDBMS Server » Security » How to find the user and ipaddress from oracle database (Oracle 10g,10.2.0.3,RHEL Itanium)
How to find the user and ipaddress from oracle database [message #587881] Wed, 19 June 2013 03:13 Go to next message
rjocmc
Messages: 14
Registered: December 2011
Junior Member
Dear All

Last week we have realized that a user who connects through SQL Developer(as nothing wrong found in application server logs) has made a serious change in the database which created a real mess. The user has done some mischief in some calculations by making some inserts and updates in some important tables in our production database.

How can I find which user or from which IP the change is made.

Kindly help me..


Thanks & Regards

Roy
Re: How to find the user and ipaddress from oracle database [message #587882 is a reply to message #587881] Wed, 19 June 2013 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have this information in listener.log file.

Regards
Michel
Re: How to find the user and ipaddress from oracle database [message #587889 is a reply to message #587882] Wed, 19 June 2013 04:00 Go to previous messageGo to next message
rjocmc
Messages: 14
Registered: December 2011
Junior Member
Thanks a lot Michel..
Re: How to find the user and ipaddress from oracle database [message #587904 is a reply to message #587889] Wed, 19 June 2013 05:19 Go to previous messageGo to next message
rjocmc
Messages: 14
Registered: December 2011
Junior Member
Dear Michel

That really helped.

But the problem started on 7th June for which the listener.log backup is not able to found. It is not there in the listener log directory or not even in the backups.

Can I regenerate the listener log of that particular date one more time.


Thanks & Regards

Roy
Re: How to find the user and ipaddress from oracle database [message #587906 is a reply to message #587904] Wed, 19 June 2013 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm afraid not.
If you have bought the option (I think it is "Performances and tuning" or similar), you can query DBA_HIST_ACTIVE_SESS_HISTORY, MODULE will give you SQL Developer sessions and MACHINE the client machine name.

Regards
Michel
Re: How to find the user and ipaddress from oracle database [message #587908 is a reply to message #587906] Wed, 19 June 2013 05:34 Go to previous messageGo to next message
rjocmc
Messages: 14
Registered: December 2011
Junior Member
Michel Please tell me whether this will help me

SELECT I.INSTANCE_NAME AS INSTANCE
,S.USERNAME AS "ORACLE USER"
,S.OSUSER AS "CLIENT MACHINE LOGINID"
,S.MACHINE AS "LOGGED IN FROM"
,S.STATUS
,DECODE(TO_CHAR(S.COMMAND),'1', 'Create Table', '2', 'Insert statement', '3', 'Select statement', '6', 'Update statement',
'7', 'Delete statement', '9', 'Create Index', '10', 'Drop Index', '11', 'Alter Index', '12', 'Drop Table',
'13', 'Create Sequence', '14', 'Alter Sequence', '15', 'Alter Table', '16', 'Drop Sequenec', '17', 'Grant statement',
'18', 'Revoke statement', '21', 'Create View', '22', 'Drop View', '24', 'Create Procedure', '25', 'Alter Procedure',
'32', 'Create DBLink', '33', 'Drop DBLink', '35', 'Alter Database', '39', 'Create Tablespace', '40',
'Alter Tablespace', '41', 'Drop Tablespace', '43', 'Alter User', '47', 'PL/SQL Execute', '49', 'Alter System',
'51', 'Create User', '53', 'Drop User', '59', 'Create Trigger', '60', 'Alter Trigger', '61', 'Drop Trigger',
'68', 'Drop Procedure', '85', 'Truncate Table', '91', 'Create Function', '92', 'Alter Function', '93', 'Drop Function',
'94', 'Create Package', '95', 'Alter Package', '96', 'Drop Package', '97', 'Create Pkg. Body', '98', 'Alter Pkg. Body',
'99', 'Drop Pkg. Body', TO_CHAR(S.COMMAND)
) AS "COMMAND EXECUTED"
,TO_CHAR(S.LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS "LOGIN TIME"
-- ,DECODE(TO_CHAR(S.COMMAND), '101', TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'), '102', TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'), ' ') AS "LOGOUT TIME"
,NVL(S.PROGRAM, 'Not Available') AS "PROGRAM EXECUTED"
FROM GV$SESSION S,
GV$INSTANCE I
WHERE I.INST_ID = S.INST_ID
AND S.LOGON_TIME >= SYSDATE - INTERVAL '9' MINUTE
AND ((S.MACHINE NOT LIKE '%portal%') AND (S.MACHINE NOT LIKE '%web%') AND (S.MACHINE NOT LIKE '%wl1%'))
ORDER BY 7 ASC, 1,2

Can you please tell me how to change the time :S.LOGON_TIME >= SYSDATE - INTERVAL '9' MINUTE in such a way that I can retrieve info for 13Jun2013

Thanks & Regards

Roy
Re: How to find the user and ipaddress from oracle database [message #587911 is a reply to message #587908] Wed, 19 June 2013 05:38 Go to previous message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will not help you unless the offending session is still active.

Regards
Michel
Previous Topic: ora-28374 typed master key not found in wallet
Next Topic: auditing DB access through ColdFusion
Goto Forum:
  


Current Time: Tue Sep 16 13:23:22 CDT 2014

Total time taken to generate the page: 0.13826 seconds