Thrown in the Fire - Database Corruption Investigation

Analyzing an incident when the manufacturer claims that it's an operator error and the operator claims that it is an application error is one of the most daunting tasks of a security officer.
And this is a type of incident that the security officer will be called upon to investigate simply because the management needs an independent observer and has doubts both in the operator as well as the manufacturer. Here is what to do when thrown into the fire

Prerequisites

  1. Do not let the manufacturer's expert be the one that leads the investigation. If he insists to be involved, make it clear that this is your investigation and that he has to ask permission for and explain any action he wants done on the database and application during the investigation.
  2. Know a bit of SQL or bring someone that you trust that knows SQL.
Tools of the trade

  • Toad for Oracle and Query Analyzer
  • MS SQL Server Management Studio for SQL Server
  • Event viewer for Windows and
  • Syslog and text log files for Unix/Linux
  • Notepad, hi-res camera or screenshots for everything.
Incident Investigation Process


  1. Gather as much information as possible - even gossip!
    • Talk to the witnesses of the incident.
    • Establish who else worked with the application during the incident discovery
    • Document the events that lead to the discovery of the problem and their timeline
    • Document any data involved in the process - account numbers, exact names, values, currencies - anything that can be found in the database. Do this for both the clean and and corrupt data
    • Gather screenshots of the application of the events that lead to the discovery of the problem
  2. Establish a time interval of the incident
    • Choose a database backup closest to the time the incident has been identified and Request that a database restore be done and the users to verify that the restored database is in good condition.
    • If the database is 'good' then the incident occurred between that backup and now.
    • If the database is 'bad' repeat with an earlier backup
    • Repeat until you find the closest 'good' and 'bad' backups - the incident has occurred sometime in that interval
  3. If possible, try to reproduce the conditions of the incident
    • Starting from the known 'good' state - a non-corrupt database ask the users to repeat their activities
    • Observe/Film the user while performing the activities in the application
    • Run a profiler/logger type of tool while the users are working to capture all backend activities on the database
    • Follow through until the application is closed and all sessions are torn down - there can be a closing script that is a problem
  4. Identify key data repositories
    • Consult the documentation and captured queries if available to identify the tables that the corrupt data is kept in.
    • If there is no usable source, use trial and error: The tables are usually named in a logical manner related to their purpose - so match them to the statement of events to find which tables are relevant.
    • In order to confirm that the right tables are identified, find at least some of the documented data involved in the incident in these tables. Don't be disappointed if you miss at first - they MUST be somewhere!
  5. Look through the audit and/or the logs of the database to identify which updates or changes were made in the database.
    • This is a very problematic step - some applications and databases will not have any audit, or a small amount of audit.
    • But almost all applications have a form of application trail - a table or set of tables that logs the action to be or was done, mostly because a lot of application actions are dependent on each other so they need to create a unique identifier (key) in one table to be referenced further.
  6. Match the described timeline with the database logged actions as closely as possible.
    • Consult the witnesses of the incident during this process - tell them that you notice certain type of event at certain time - this reminder triggers memory - they'll remember more detailed actions of their work!Add log details and timestamps at each step of the timeline
  7. Discuss Observable Trail With Manufacturer and Users
    • If you find a proof of a bug or human error you're in luck. Write a report and recommend corrective and preventive measures.
    • Most likely, you'll find a gap in the events right where the incident occurred (interval of minutes) but the trail of events will indicate what was the next step: whether the program malfunctioned or the user made a flagrant error. Then you need to confront the manufacturer and users with the problem. Ask for a recreation of the actions with both parties present and with full logging. The log will give the actual event.
      NOTE: Non reproducible errors are possible - If the error cannot be reproduced, then that is a report also. But then you need to increase the logging level to maximal possible level until the problem resurfaces.

      Related Posts
      Tutorial - Mail Header Analysis for Spoof Protection
      Tutorial - Computer Forensics Process for Beginners
      Tutorial - Computer Forensics Evidence Collection
      The SLA Lesson: software bug blues

      Talkback and comments are most welcome

      2 comments:

      Aice Nice Concepts said...

      i notice about your most popular post... i wonder how did you do that?
      if you can share some insights and tips are really appreciated...

      thank you

      my email address is

      icelahora@gmail.com

      thank you so much

      Anonymous said...

      How about enabling audit trail on a RDBMS level, or using application software with auditing capabilities?

      Designed by Posicionamiento Web