LOG_MODE
------------
ARCHIVELOG

SQL> select name,value from v$parameter
2 where name in('log_archive_start','log_archive_dest');

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
log_archive_start
TRUE

log_archive_dest
/export/home/u01/app/oracle/admin/emil/archive

To detect which user executed a command:

SQL> select name,value from v$parameter
2 where name = 'transaction_auditing';

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
transaction_auditing
TRUE

Now execute the SQL injection attempt and then use Log Miner to see what is recorded. To make the analysis easier for this example, the archive log is saved before and after to ensure only this command is in the log:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> alter system archive log current;

System altered.

SQL>
SQL> connect dbsnmp/dbsnmp@emil
Connected.
SQL> set serveroutput on size 100000
SQL> exec get_cust('x'' union select username from all_users where ''x''=''x');
debug:select customer_phone from customers where customer_surname='x' union
select username from all_users where 'x'='x'
::AURORA$JIS$UTILITY$
::AURORA$ORB$UNAUTHENTICATED
::CTXSYS
::DBSNMP
::EMIL
<records snipped>
::SYS
::SYSTEM
::WKSYS
::ZULIA

PL/SQL procedure successfully completed.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> alter system archive log current;

System altered.

SQL>

First create the Log Miner dictionary:

SQL> set serveroutput on size 1000000
SQL> exec dbms_logmnr_d.build('logmnr.dat','/tmp');
LogMnr Dictionary Procedure started
LogMnr Dictionary File Opened
TABLE: OBJ$ recorded in LogMnr Dictionary File
TABLE: TAB$ recorded in LogMnr Dictionary File
TABLE: COL$ recorded in LogMnr Dictionary File
TABLE: TS$ recorded in LogMnr Dictionary File
<output snipped>
Procedure executed successfully - LogMnr Dictionary Created

PL/SQL procedure successfully completed.

SQL>

Find the correct archive log file:

SQL> select name
2 from v$archived_log
3 where completion_time=(select max(completion_time) from v$archived_log);

NAME
--------------------------------------------------------------------------------
/export/home/u01/app/oracle/admin/emil/archive/1_7.dbf

SQL>

Now load the archive log file into Log Miner:

SQL> exec dbms_logmnr.add_logfile('/export/home/u01/app/oracle/admin/emil/archive/1_7.dbf',sys.dbms_logmnr.NEW);

PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(dictFileName => '/tmp/logmnr.dat');

PL/SQL procedure successfully completed.

SQL>

Finally, search the results:

SQL> select scn,username,timestamp,sql_redo
2 from v$logmnr_contents
SQL>
<snipped>
SCN USERNAME TIMESTAMP SQL_REDO
---------- --------------- --------- ------------------------------
253533 DBSNMP 16-JUN-03 set transaction read write;
253533 DBSNMP 16-JUN-03 update "SYS"."AUD$" set
"ACTION#" = '101',
"RETURNCODE" = '0',
"LOGOFF$LREAD" = '228',
"LOGOFF$PREAD" = '0',
"LOGOFF$LWRITE" = '10',
"LOGOFF$DEAD" = '0',
"LOGOFF$TIME" =
TO_DATE('16-JUN-2003
12:16:12', 'DD-MON-YYYY

SCN USERNAME TIMESTAMP SQL_REDO
---------- --------------- --------- ------------------------------
HH24:MI:SS'), "SESSIONCPU" =
'5' where "ACTION#" = '100'
and "RETURNCODE" = '0' and
"LOGOFF$LREAD" IS NULL and
"LOGOFF$PREAD" IS NULL and
"LOGOFF$LWRITE" IS NULL and
"LOGOFF$DEAD" IS NULL and
"LOGOFF$TIME" IS NULL and
"SESSIONCPU" IS NULL and ROWID
= 'AAAABiAABAAAAEWAAX';

SCN USERNAME TIMESTAMP SQL_REDO
---------- --------------- --------- ------------------------------
253534 DBSNMP 16-JUN-03 commit;
<snipped output>