Oracle%20Forensic%20By%20Gita%20Faroughi

1
Oracle ForensicBy Gita Faroughi

2
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

3
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

4
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

5
Logical Structure

• Specifies how the physical space of a database
  is used
• consisting of tablespaces, segments,extents, and
  blocks

6
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

7
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

8
Block Structure
9
Memory Structure

• An Oracle Instance
• Is a mean to access an Oracle database
• Consists of memory and background process

10
Database Buffer cache

• Stores copies of data blocks that have been
• retrieved from the datafiles

11
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

12
LGWR process

• LGWR writes
• At commit
• When one-third full
• When there is 1 MB of redo
• Every three seconds

13
Archiver Process (ARCn)

• Automatically archives online redo logs when
  ARCHIVELOG mode is set
• Preserves the record of all changes made to the
  database

14
Redo Log Insert Entry
15
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

16
Undo Segment Mangement

• To get a hex dump of undo segment
• SQLgt SELECT FILE_ID, BLOCKS FROM DBA_DATA_FILES
  WHERE TABLESPACE_NAME 'UNDOTBS1'
• FILE_ID BLOCKS
• ---------- ----------
• 2 4480
• SQLgt ALTER SYSTEM DUMP DATAFILE 2 BLOCK MIN 0
  BLOCK MAX 4480

17
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.

18
Flash Back Query

• To find new objects that arent in the older
  version of database execute
• SQLgt SELECT NAME FROM SYS.OBJ MINUS SELECT NAME
  FROM SYS.OBJ AS OF
• TIMESTAMP(SYSDATE - INTERVAL '156' MINUTE)
• NAME
• ------------------------------
• TESTTEST

19
Flashback Queries

• To find recently dropped objects execute
• SQLgt SELECT NAME FROM SYS.OBJ AS OF
  TIMESTAMP(SYSDATE - INTERVAL '156'
• MINUTE) MINUS SELECT NAME FROM SYS.OBJ
• NAME
• ------------------------------
• GET_DBA_FUNCTION

20
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

21
Recycle Bin

• The SQL below shows the relationship between a
  dropped objects row data in SYS.OBJ and
  SYS.RECYCLEBIN
• SQLgt SELECT DROPTIME, OBJ, OWNER, ORIGINAL_NAME
  FROM SYS.RECYCLEBIN
• DROPTIME OBJ
  OWNER
  ORIGINAL_NAME
• --------------------- --------
  ------- --------------------
• 2007-08-16 092745 53137 104
  FOOBAR
• SQLgt SELECT MTIME, OBJ, OWNER, NAME FROM
  SYS.OBJ WHERE OBJ53137
• MTIME OBJ
  OWNER
  NAME
• --------------------- --------
  -------
  -------------------
• 2007-08-16 092746 53137 104
  BINtjjNZzJ2RSWgPAOcVwnmQg0

22
Finding Evidence of Data Theftin 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

23
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

24
Cost Base Optimizer cont..

• SQLgt SELECT C.TIMESTAMP, O.NAME, C.INTCOL,
  C.LIKE_PREDS FROM COL_USAGE C, OBJ O WHERE
  C.OBJO.OBJ AND C.LIKE_PREDS gt 0
• TIMESTAMP NAME INTCOL
  LIKE_PREDS
• ------------------- --------------
  ------- ----------
• 2007-08-08 061027 COL 6
  1
• 2007-08-09 180655 OBJ 4
  2

25
V views in the Shared Pool

• Maintained for performance purposes
• Accessible to DBAs
• Often contain evidence of attacks
• Two of these views
• VSQL
• VDB_OBJECT_CACHE.

26
VSQL views

• The VSQL 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.

27
VDB_OBJECT_CACHE.VDB_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
• SQLgt SELECT OWNER, NAME FROM
  VDB_OBJECT_CACHE WHERE NAMESPACE
• 'TABLE/PROCEDURE' ORDER BY 1
• VDB_OBJECT_CACHE view cannot be clear by an
  attacker

28
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/.

29
Forensic Tool

• Oracle LogMiner
• part of Oracle Database
• query
• online redo log and
• archived redo log

30
Oracle Forensic Book
Oracle ForensicsOracle Security Best
PracticesPaul M. Wright
31
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

32
References

• http//www.databasesecurity.com/dbsec/oracle-foren
  sics-scns.pdf
• http//www.databasesecurity.com/dbsec/oracle-fore
  nsics-6.pdf
• http//www.datagovernance.com/adl_data_laws_califo
  rnia_security_breach_notifi.html
• http//www.databasesecurity.com/dbsec/OracleForens
  icsPt5.pdf
• http//www.databasesecurity.com/dbsec/dissecting-
  the-redo-logs.pdf
• http//www.databasesecurity.com/dbsec/Locating-Dr
  opped-Objects.pdf

33
QUESTIONS ?