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  |
 |
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 #587908 is a reply to message #587906] |
Wed, 19 June 2013 05:34  |
|
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
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 01 21:37:18 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
