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

      5 Reasons to Consult Your SysAdmin for New Systems

      A lot of organizations isolate system administrators from new system implementations, lead by the premise that their admin teams need to focus on maintenance, and that they may not bring benefit to the implementation, especially when consultants are engaged to implement the entire new system.
      But always bear in mind that system admins have very specific insight that any project manager will find useful. 

      Here are the 5 reasons why organizations should always include your system admins in all phases of system implementation:
      1. SysAdmins know the infrastructure and the interactions between systems - every corporate IT infrastructure is a complex set of systems, firewalls, security rules and networking connections. The SysAdmin can provide invaluable information about what the new system will communicate to, under which conditions and by which rules - questions that need to be properly answered in any implementation.  
      2. SysAdmins know the utilized capacities of current systems -  introducing a new system is never self-sufficient. The new system will add load to the switching infrastructure, firewalls, can require additional licenses for monitoring systems and possibly database servers. All these prerequisites need to be addressed in a timely manner, so the full implementation does not grind to a halt at the last mile because there are no available ports.
      3. SysAdmins can assist in evaluating required capacity - Based on what is used in the current network, SysAdmins can provide very relevant observation on whether the offered hardware is appropriate in terms of processing power, memory and disk capacity usage.
      4. SysAdmins can provide fresh insight into possible risks in implementation - While the risk analysis is part of the preparation for implementation, SysAdmins can provide good input on possible risks in implementation - they know the users and usage patterns, the client systems and the entire environment. 
      5. SysAdmins need to be in the loop for the new element - The SysAdmins need to know and understand the new element in the infrastructure, so they can prepare to welcome the element - prepare capacity on related systems, read about the product, properly organize day-to-day maintenance tasks for the new system.
      Related posts

      Talkback and comments are most welcome

      Designed by Posicionamiento Web