| e organisation and analysed offline. There is a GUI tool available via the Oracle Enterprise Manager (OEM) Also, to make the use of this tool realistic the database has to be in ARCHIVELOGMODE and transaction_auditing needs to be true in the initialisation file for user information to be included. This is a very effective tool for after the fact analysis and forensics to find out exactly when some event occurred within the database and who did it. It can be used successfully to help recover, for instance, a table deleted by accident. Redo logs can also be analysed by hand the hard way. A good paper demonstrating this can be found here (PDF). Now we can run through the example and explore the contents of the archive logs. First check if the database is in ARCHIVELOGMODE, determine where the archive logs are written to, and finally that username auditing is on. SQL> select log_mode from v$database;
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>
The first thing that can be noticed is that Log Miner does not process select statements and display the output in 9i. The Log Miner package doesn''''t support selects as they are not stored in the redo logs. It is possible to use Log Miner to read on-line redo logs but I will leave that to the reader to experiment with. Even though SQL injection can be detected in insert, delete and update statements, Log Miner is not suitable for detecting SQL injection. This is due to its lack of being able to detect select statements as well as some of the other issues mentioned above. Packet sniffing The main issue with packet sniffing for connections to the Oracle database is that the Oracle network protocol is proprietary and not published. Does that matter for trying to ascertain if SQL injection attempts have taken place? Probably yes, as access to the protocol would allow a better design and efficient tool for this task. Without access to the protocol and no wish to reverse engineer it, the task is limited to grabbing ASCII text strings from the wire. There are both advantages and disadvantages with this method: Advantages: - A system could be implemented on a separate server allowing real time analysis without impacting the source database.
Disadvantages: - This method can be resource intensive.
- The packets need to be sniffed close to the source and on the same subnet to ensure all packets pass in front of the sniffer.
- If the Oracle advanced security options for encrypting of network packets or any other third party solution is used to encrypt, sniffing packets will not work.
- If, as in our example, the SQL injection attempt is passed as a call to a package procedure then the true internal dynamic SQL will not be visible. Instead you will simply see the typed in command.
- Packet sniffing every packet will generate a huge amount of data. Piping the packets through a filter program is a solution to mitigate this issue.
To demonstrate, we will use snoop on Solaris to see what is visible within network packets. Start up snoop and fire the SQL from a SQL*Plus session: root:jupiter> snoop -t a -x 0 jupiter and port 1521 | strings
Using device /dev/hme (promiscuous mode)
15:06:34.31348 172.16.240.3 -> jupiter TCP D=1521 S=1404 Ack=299902194 Seq=26460609 Len=174 Win=8413
0: 0800 2092 9d88 00a0 ccd3 a550 0800 4500 .. ........P..E.
16: 00d6 6884 4000 8006 596d ac10 f003 ac10 ..h.@...Ym......
32: f00b 057c 05f1 0193 c1c1 11e0 24f2 5018 ...|........$.P.
48: 20dd 0f36 0000 00ae 0000 0600 0000 0000 ..6............
64: 1169 36a4 61de 0001 0101 0303 5e37 0304 .i6.a.......^7..
80: 0021 0078 71de 0001 52bc 39de 0001 0a00 .!.xq...R.9.....
96: 0000 00e0 39de 0000 0101 0000 0000 0000 ....9...........
112: 0000 0000 0000 0000 0000 0000 0000 0000 ................
128: e239 de00 4245 4749 4e20 6765 745f 6375 .9..BEGIN get_cu
144: 7374 2827 7827 2720 756e 696f 6e20 7365 st(''''x'''''''' union se
160: 6c65 6374 2075 7365 726e 616d 6520 6672 lect username fr
176: 6f6d 2061 6c6c 5f75 7365 7273 2077 6865 om all_users whe
192: 7265 2027 2778 2727 3d27 2778 2729 3b20 re ''''''''x''''''''=''''''''x'''');
208: 454e 443b 0a00 0101 0101 0000 0000 0001 END;............
224: 0800 0105 ....
15:06:34.33281 jupiter -上一页 [1] [2] [3] [4] [5] [6] 下一页 | 
