How to move and query audit log data? sqlplus / as sysdba create user audit_owner ... grant create session, create view, select_catalog_role to audit_owner; grant select on aud$ to audit_owner; create table audit_owner.audit_data as select * from sys.aud$ where 1=2; --The view below is modifed from dba_audit_trail. Note the EXTENDED_TIMESTAMP part. create view audit_owner.audit_trail as select spare1 OS_USERNAME, userid USERNAME, userhost, terminal, cast ( (from_tz(ntimestamp#,'00:00') at local) as date) TIMESTAMP, obj$creator OWNER, obj$name OBJECT_NAME, aud.action# ACTION, act.name ACTION_NAME, new$owner NEW_OWNER, new$name NEW_NAME, decode(aud.action#, 108 /* grant sys_priv */, null, 109 /* revoke sys_priv */, null, 114 /* grant role */, null, 115 /* revoke role */, null, auth$privileges) OBJ_PRIVILEGE, decode(aud.action#, 108 /* grant sys_priv */, spm.name, 109 /* revoke sys_priv */, spm.name, null) SYS_PRIVILEGE, decode(aud.action#, 108 /* grant sys_priv */, substr(auth$privileges,1,1), 109 /* revoke sys_priv */, substr(auth$privileges,1,1), 114 /* grant role */, substr(auth$privileges,1,1), 115 /* revoke role */, substr(auth$privileges,1,1), null) ADMIN_OPTION, auth$grantee GRANTEE, decode(aud.action#, 104 /* audit */, aom.name, 105 /* noaudit */, aom.name, null) AUDIT_OPTION, ses$actions SES_ACTIONS, logoff$time LOGOFF_TIME, logoff$lread LOGOFF_LREAD, logoff$pread LOGOFF_PREAD, logoff$lwrite LOGOFF_LWRITE, decode(aud.action#, 104 /* audit */, null, 105 /* noaudit */, null, 108 /* grant sys_priv */, null, 109 /* revoke sys_priv */, null, 114 /* grant role */, null, 115 /* revoke role */, null, aud.logoff$dead) LOGOFF_DLOCK, comment$text COMMENT_TEXT, sessionid, entryid, statement STATEMENTID, returncode, spx.name PRIVILEGE, clientid CLIENT_ID, auditid ECONTEXT_ID, sessioncpu SESSION_CPU, cast ((from_tz(ntimestamp#,'00:00') at local) as date) /* syntax error in orig dba_audit_trail */ EXTENDED_TIMESTAMP, proxy$sid PROXY_SESSIONID, user$guid GLOBAL_UID, instance# INSTANCE_NUMBER, process# OS_PROCESS, xid TRANSACTIONID, scn, to_nchar(substr(sqlbind,1,2000)) SQL_BIND, to_nchar(substr(sqltext,1,2000)) SQL_TEXT from audit_owner.audit_data aud, /* the only real change from dba_audit_trail; changed from sys.aud$ */ system_privilege_map spm, system_privilege_map spx, STMT_AUDIT_OPTION_MAP aom, audit_actions act where aud.action# = act.action (+) and - aud.logoff$dead = spm.privilege (+) and aud.logoff$dead = aom.option# (+) and - aud.priv$used = spx.privilege (+); Now you can query dba_audit_trail and audit_owner.audit_trail using the same select list: col os_username for a15 col userhost for a30 col terminal for a30 --Can add comment_text if client port is needed to find the match in listener log select os_username, userhost, terminal, timestamp, action_name, returncode from dba_audit_trail where username = 'APPUSER' and os_username != 'oracle' and rownum < 101 order by timestamp; select os_username, userhost, terminal, timestamp, returncode, action_name, comment_text from audit_owner.audit_trail where username = 'APPUSER' and os_username != 'oracle' and rownum < 101 order by timestamp; Example: Find who attempted 5 times to login to APPUSER and caused the account to be locked that has a profile limiting failed_login_attempts to 5: SQL> select os_username, userhost, terminal, timestamp, returncode from audit_owner.audit_trail 2 where username = 'APPUSER' and os_username = 'oracle' and action_name = 'LOGON' order by timestamp; OS_USERNAME USERHOST TERMINAL TIMESTAMP RETURNCODE --------------- ------------------------------ ------------------------------ ----------------- ---------- john.d clientmachine.example.com 20080330 10:46:53 1017 <-- "invalid username/password; logon denied" john.d clientmachine.example.com 20080330 10:46:53 1017 john.d clientmachine.example.com 20080330 10:46:54 1017 john.d clientmachine.example.com 20080330 10:46:54 1017 john.d clientmachine.example.com 20080330 13:25:09 1017 john.d clientmachine.example.com 20080330 13:25:09 28000 <-- "the account is locked" john.d clientmachine.example.com 20080330 13:25:10 28000 john.d clientmachine.example.com 20080330 13:25:10 28000 SQL> select comment_text from audit_owner.audit_trail 2 where username = 'APPUSER' and os_username = 'oracle' and timestamp = '20080530 13:25:09' and action_name = 'LOGON'; COMMENT_TEXT ---------------------------------------------------------------------------------------------------- Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.111.111.32)(PORT=18981)) Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.111.111.32)(PORT=18985)) Then go to the database listener log for more info about the clients using ports 18981 and 18985 at or slightly before that moment. Example: Sometimes comment_text is empty (e.g. connection was made by OS user oracle on the server). You can get the terminal name and look it up in `last` if UNIX/Linux: SQL> select os_username, username, userhost, terminal, timestamp from dba_audit_trail where action_name in ('CREATE TABLE', 'DROP TABLE') and obj_name = 'USERS'; OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP --------------- ------------------------------ ----------------------------- ---------- ----------------- oracle LARRYELLISON clientmachine.example.com pts/2 20080722 10:58:09 oracle LARRYELLISON clientmachine.example.com pts/2 20080722 10:58:09 $ last | grep pts/2 | more ... oracle pts/2 clientmachine.ex Tue Jul 22 10:12 - 14:24 (04:12) On the OS, you focus on the connection to the second terminal and use command such as `last' to look for the terminal connection time range, which should encompass the timestamp in dba_audit_trail. Then find the user on clientmachine.example.com (at that time). The last step may need some clever thinking: * nbtstat -A [the IP] #Check its NetBIOS info, very useful if messenger service is left running * ask team members one by one (normally only DBAs have oracle access to the DB server) * search on company Intranet for the workstation #Some companies' Intranet may even have account passwords! * net view \\clientmachine.example.com #See if he has some network shares; IP address works too * psloggedon \\clientmachine.example.com #Download psloggedon from Sysinternals or Microsoft.com Appendix: Script to transfer audit records #!/bin/bash #transfer_audit_record.sh: Move data from sys.aud$ to audit_owner.audit_data and empty sys.aud$ export ORACLE_SID=ORCL export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db export PATH=$ORACLE_HOME/bin:/usr/bin:/bin #To-do: rewrite to use bulk collect sqlplus -L / as sysdba <