Transcript Oracle Forensic

ORACLE FORENSIC
BY GITA FAROUGHI
Over view












Why Oracle Forensic
California Breach security Act
Oracle Logical Structure
Oracle System Change Number
Oracle Data Block Structure
Oracle Memory Structure
Redo logs
Automatic Undo management
Flash back Queries
Recycle Bin
Finding Evidence of Data Theft in the Absence of Auditing
Conclusion
Why Oracle Forensic
 Database servers hold critical and sensitive
information
 Database Security Breaches
 In Jan 2007 TJX announced that they have suffered
a database security breach with 4.5 million credit
card information stolen
 CardSystem Solution announce that 200,000
credit/debit information stolen
California Security Breach
Information Act
 Began on July 1 of 2003
 government agencies and companies must notify
customers if personal information maintained in
computerized data files have been compromised by
unauthorized access.
 34 more states have passed similar legislation
 The details of this law can be found at
http://www.leginfo.ca.gov
Logical Structure
 Specifies how the physical space of a database is used
 consisting of tablespaces, segments,extents, and
blocks
System change Number (SCN)
 used by Oracle to keep track of changes made to
the database server.
 With each change the SCN is incremented.
 The database's SMON background process
keeps track of these SCNs and their timestamps
in the SMON_SCN_TIME table.
 SCN and its timestamp
 whether a block of data has been changed
 useful in those cases where there is an absence of
other evidence
Database Block

Data is stored in tables and, at the file level, these tables are split
across data blocks.
 Each data block contains
 A header
 Located at bytes 9 to 12 of the data block header is a 4 byte
SCN.
 The SCN is updated each time the data block is written
 the value of the SCN at the time of the last committed update
insert or delete to occur on data in that block.
 A row directory
 The row directory contains a list of offsets pointing to each row
of data
 Flag indicating if the row is deleted or not
 The data itself which is stored in rows
Block Structure
Memory Structure
 An Oracle Instance:

Is a mean to access an Oracle database
 Consists of memory and background process
Database Buffer cache
 Stores copies of data blocks that have been
retrieved from the datafiles
Redo log Buffer
 Records all changes made to the database data blocks

Changes recorded within a redo log buffer are called redo entries
 Redo entries contain information to reconstruct or redo changes
LGWR process
 LGWR writes:
 At commit
 When one-third full
 When there is 1 MB of redo
 Every three seconds
Archiver Process (ARCn)
Automatically archives online redo logs when ARCHIVELOG mode is
set
 Preserves the record of all changes made to the database
Redo Log Insert Entry
Automatic Undo Management
 An undo tablespace is maintained
 contains 10 undo segments.
 Whenever a transaction takes place an image of the
data before changes, is recorded in an undo segment
 UPDATE
 A copy of data before changes is stored
 DELETE
 A copy of the data that was deleted is stored

INSERT

The file number, row and slot is stored
Undo Segment Mangement
To get a hex dump of undo segment
 SQL> SELECT FILE_ID, BLOCKS FROM
DBA_DATA_FILES WHERE
TABLESPACE_NAME ='UNDOTBS1';
FILE_ID BLOCKS
---------- ---------2
4480
 SQL> ALTER SYSTEM DUMP DATAFILE 2
BLOCK MIN 0 BLOCK MAX 4480;
Flash Back Queries
 query data from an older version or snapshot of
a given table
 Data for flashback queries
 undo data
 and the redo logs
 may not be available for long.
 On a “quiet” system data may linger for a day or two but
considerably less so in a “busy” system.
 an incident responder or DBA gets there in “time” they
will be able to quickly ascertain what an attacker may or
may not have done.
Flash Back Query
To find new objects that aren’t in the older
version of database execute:
SQL> SELECT NAME FROM SYS.OBJ$ MINUS
SELECT NAME FROM SYS.OBJ$ AS OF
TIMESTAMP(SYSDATE - INTERVAL '156'
MINUTE);
NAME
-----------------------------TESTTEST
Flashback Queries
 To find recently dropped objects execute:
SQL> SELECT NAME FROM SYS.OBJ$ AS OF
TIMESTAMP(SYSDATE - INTERVAL '156'
MINUTE) MINUS SELECT NAME FROM
SYS.OBJ$;
NAME
-----------------------------GET_DBA_FUNCTION
The Oracle Recycle Bin
 Any dropped objects are moved to the Recycle
Bin.
 Recycle Bin is implemented as a table
 RECYCLEBIN$ in the SYSTEM tablespace.
 When a table is dropped
 name of the table is changed in SYS.OBJ$
 A row is inserted into the RECYCLEBIN$




original table name
the object ID
the owner
the time
Recycle Bin

The SQL below shows the relationship between a dropped object’s row data in
SYS.OBJ$ and SYS.RECYCLEBIN$:
SQL> SELECT DROPTIME, OBJ#, OWNER#, ORIGINAL_NAME
FROM SYS.RECYCLEBIN$;
DROPTIME
OBJ#
--------------------2007-08-16 09:27:45
-------53137
OWNER#
------104
ORIGINAL_NAME
-------------------FOOBAR
SQL> SELECT MTIME, OBJ#, OWNER#, NAME FROM SYS.OBJ$
WHERE OBJ#=53137;
MTIME
OBJ#
---------------------------2007-08-16 09:27:46 53137
OWNER#
------104
NAME
-----------------BIN$tjjNZzJ2RSWgPAOcVwnmQg==$0
Finding Evidence of Data Theft
in the Absence of Auditing
 when data is stolen, only a copy is taken and
the original remains.
 If an attacker breaks in and simply silently
SELECTs some data, evidence can be found in
tables used by
 Cost-Based Optimizer
 Fixed V$ views in the Shared Pool
Cost Base Optimizer (CBO)
 Whenever a user executes a SQL query,
 the server compiles the query into an execution
plan.
 Statistics about the CBO are recorded in
COL_USAGE$ table
 COL_USAGE$ table holds information
 Which Tables used in the from clause
 Which columns used in a WHERE clause
 Which predicates such as equals, like, range
Cost Base Optimizer cont..
SQL> SELECT C.TIMESTAMP, O.NAME,
C.INTCOL#, C.LIKE_PREDS FROM
COL_USAGE$ C, OBJ$ O WHERE
C.OBJ#=O.OBJ# AND C.LIKE_PREDS > 0;
TIMESTAMP
NAME
INTCOL#
-------------------
--------------
-------
LIKE_PREDS
----------
2007-08-08 06:10:27 COL$
6
1
2007-08-09 18:06:55
4
2
OBJ$
V$ views in the Shared Pool
 Maintained for performance purposes
 Accessible to DBAs
 Often contain evidence of attacks
 Two of these views
 V$SQL
 V$DB_OBJECT_CACHE.
V$SQL views
 The V$SQL view
 Contains a list of recently executed queries
 It is a circular buffer so as it fills up new
information pushes out old information.
 buffer can hold a large number of queries (7000).
 can be cleared executing
‘ALTER SYSTEM FLUSH
SHARED_POOL’.
V$DB_OBJECT_CACHE.
V$DB_OBJECT_CACHE.
 Contains details about objects in the library cache
 if an object exists in the cache then it has probably
been accessed recently
 can contain snippets of recently executed queries
 To access a list of recently accessed tables and
procedures :
SQL> SELECT OWNER, NAME FROM
V$DB_OBJECT_CACHE WHERE NAMESPACE =
'TABLE/PROCEDURE' ORDER BY 1;
 V$DB_OBJECT_CACHE view cannot be clear by an
attacker
Oracle Forensic Tool
 Orablock
 To dump data from a "cold" Oracle data file
 To locate "stale" data (deleted)
 To dump SCNs for data blocks
 no need to load up the data file in the database
which would cause the data file to be modified
 using orablock preserves the evidence.
 http://www.databasesecurity.com/.
Forensic Tool
 Oracle LogMiner

part of Oracle Database
 query
 online redo log and
 archived redo log
Oracle Forensic Book
Oracle Forensics
Oracle Security Best
Practices
Paul M. Wright
Summary
 Evidence of an attack can found
 SCN
 Redo log file
 Archive redo log file
 Recycle Bin
 Undo segment
 Flash Back queries
 Cost Base Optimizer
 Views$ share pool
References
 http://www.databasesecurity.com/dbsec/oracle




forensics-scns.pdf
http://www.databasesecurity.com/dbsec/oracleforensics-6.pdf
http://www.datagovernance.com/adl_data_laws_cal
ifornia_security_breach_notifi.html
http://www.databasesecurity.com/dbsec/OracleFore
nsicsPt5.pdf
http://www.databasesecurity.com/dbsec/dissectingthe-redo-logs.pdf
http://www.databasesecurity.com/dbsec/LocatingDropped-Objects.pdf
QUESTIONS ?