Database Forensic Analysis with DBCarver James Wagner, Alexander - PowerPoint PPT Presentation

Database Forensic Analysis with DBCarver James Wagner, Alexander Rasin , Tanu Malik, Karen Heart, Hugo Jehle, Jonathan Grier 1

Data Systems and Optimization Lab at DePaul Tanu Malik James Wagner Jonathan Grier Hugo Jehle Karen Heart 2

Motivation • Cyber-crime • Detecting (and proving) data theft • JP Morgan/Dow Jones • Mobile device analysis • FBI, 4Discovery • Involves a database 3

Motivation • Example Queries • Reconstruct deleted data • Identify recent access, modifications • Detect catalog/data tampering • Un-trusted environment 4

Forensic Analysis Targets • Logs • Audit, Query, WAL • RAM • Buffer cache, intermediate data • Buffer cache, intermediate data • Query-able DB content • Tables, MVs, Catalog • Un-query-able content • Indexes, Deleted data, Free-listed data 5

Forensic Analysis Targets • Logs DB RECOVERY • Audit, Query, WAL • RAM • Buffer cache, intermediate data • Buffer cache, intermediate data Chain of • Query-able DB content Custody? • Tables, MVs, Catalog • Un-query-able content • Indexes, Deleted data, Free-listed data 6

File Carving (JPEG) Header File Fragment 1 1 File Fragment 2 Footer 7

Forensic Analysis Targets • Logs FILE CARVING • Audit, Query, WAL • RAM • Buffer cache, intermediate data • Buffer cache, intermediate data • Query-able DB content • Tables, MVs, Catalog • Un-query-able content • Indexes, Deleted data, Free-listed data 8

Generalized Page Carving Table Data Page Header Customer Row Directory 20% Row 1 Address Other Row 2 Address Structures Row 3 Address Row 4 Address Row 4 Address Row Data Free space, etc. 80% Row 4 : 4, Mark, Boston Row 3 : 3, Mary, Dallas Row 2 : 2, Jane, Chicago Row 1 : 1, John, Boston 9

Forensic Analysis Targets • Logs DB CARVING • Audit, Query, WAL • RAM • Buffer cache, int. data • Buffer cache, int. data • Query-able DB content • Tables, MVs, Catalog • Un-query-able content • Indexes, Deleted data, Free-listed data 10

DBCarver Architecture Iteratively load synthetic data Database Parameter Management Detector Capture DB storage System Generate DB Generate DB config. file DBMS disk DBMS RAM image image Cached index/data pages Updated, Deleted rows Unallocated (free) pages DB Carver DB config. files Catalog, logs, etc

DBCarver Architecture Iteratively load synthetic data Database Parameter Management Detector Capture DB storage System Generate DB Generate DB config. file DBMS disk DBMS RAM image image Cached index/data pages Updated, Deleted rows Unallocated (free) pages DB Carver DB config. files Catalog, logs, etc

Oracle PostgreSQL SQLite Firebird DB2 SQLServer MySQL Apache Derby Structure Yes No Yes No Identifier Unique Yes No Page ID Row Dir. Top-to-bottom insertion Bottom-to-top insertion Sequence Row No Yes No Yes Identifier Column Column Yes Yes No No Yes Yes No No Yes Yes Count Count 4, Mark, Boston 3-column row Row 4 4, Mark, Boston Row 4 4 4, Mark, Boston Row 4 3 4, Mark, Boston 13

DBCarver Architecture Iteratively load synthetic data Database Parameter Management Detector Capture DB storage System Generate DB Generate DB config. file DBMS disk DBMS RAM image image Cached index/data pages Updated, Deleted rows Unallocated (free) pages DB Carver DB config. files Catalog, logs, etc

DBCarver Output (SQLite on Android) Number of Active Rows Internal RowID … Deleted Row

Forensic Value of an Index (Update) Employee Table Employee Index on (LastName) 111 J. Doe … Emp. 42K Doe 222 J. Smith … Emp. 35K Jack Jack 333 333 A.Locke A.Locke … Mgr. … Mgr. 65K 65K Locke 444 P. Jack … Emp. 37K NotSmith 222 I. NotSmith … Emp. 35K Smith 16

Forensic Value of Caching (Update) Memory (RAM) Disk Storage 111 J. Doe … Emp. 42K 222 J. Smith … Emp. 35K Data Page 333 A.Locke … Mgr. 65K 444 P. Jack … Emp. 37K 17

Forensic Value of Caching (Update) 111 J. Doe … Emp. 42K 222 J. Smith … Emp. 35K Data Page (a copy in 333 A.Locke … Mgr. 65K RAM) 444 P. Jack … Emp. 37K Memory (RAM) Disk Storage 111 J. Doe … Emp. 42K 222 J. Smith … Emp. 35K Data Page 333 A.Locke … Mgr. 65K 444 P. Jack … Emp. 37K 18

Forensic Value of Caching (Update) 111 J. Doe … Emp. 42K 222 J. Smith … Emp. 35K Data Page 333 A.Locke … Mgr. 65K (a copy in 444 P. Jack … Emp. 37K RAM) 222 222 I. NotSmith I. NotSmith … Emp. … Emp. 35K 35K Memory (RAM) Disk Storage 111 J. Doe … Emp. 42K 222 J. Smith … Emp. 35K Data Page 333 A.Locke … Mgr. 65K 444 P. Jack … Emp. 37K 19

Forensic Value of Caching (Update) 111 J. Doe … Emp. 42K 222 J. Smith … Emp. 35K Data Page 333 A.Locke … Mgr. 65K (a copy in 444 P. Jack … Emp. 37K RAM) 222 222 I. NotSmith I. NotSmith … Emp. … Emp. 35K 35K Memory (RAM) Disk Storage 111 J. Doe … Emp. 42K 222 J. Smith … Emp. 35K Data Page 333 A.Locke … Mgr. 65K 444 P. Jack … Emp. 37K 222 I. NotSmith … Emp. 35K 20

Delete Progression • Storage state: – Issue the delete command – ??? (Profit?) – Value is gone – Value is gone • Observe disk and RAM state – In Table, Index (e.g., Unique ), MV 21

Delete Progression • T 0 : Load the data (Table, Index, MV) • T 1 : Delete a unique value (222) • T 2 : Refresh the MV • T 3 : Flush_buffer_cache() • T 4 : Overwrite the buffer cache • T 5 : Vacuum Table, Index and MV 22

Table Index MV Table Index MV Disk RAM T 0 222 222 222 T 1 222 222 222 222 222 T 2 T 2 222 222 222 222 222 222 222 222 222 222 222 222 T 3 222 222 222 222 222 222 T 4 222 222 222 T 5 23

Recover Corrupted Data • Load SSBM Scale1 data • Simulate disk corruption (random writes) DWDate Supplier Customer Part Lineorder Full JOIN 24

Conclusions/Future Work • DB Carving • No apriori assumptions • Forensic Meta-Queries – Reconstruct deleted data – Detect recently updated values – Identify log tampering 25

26