Lab 5 - Containment Breach Response System

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───

Executive Summary

• This SQL implementation creates an automated containment breach response system for high-security facilities managing biological specimens and hazardous materials. The system monitors specimen status changes, tracks facility access patterns, escalates critical incidents, and provides emergency response procedures. The architecture emphasizes real-time altering, automated threat assessment, and comprehensive audit trails to ensure rapid response to containment failures and security breaches.

Package Overview

Specimen Monitoring Package

• TRG_SPECIMEN_STATUS_CHANGE → monitors specimen status updates and threat level changes, automatically generating incident reports when specimens escape containment or when threat levels increase significantly (≥ 3 levels)
• PROC_ASSESS_FACILITY_RISK → evaluates facility risk based on high-threat specimen count, categorizing facilities as low, medium, or high risk

Access Control Package

• TRG_MONITOR_ACCESS_PATTERNS → tracks facility access attempts, flagging suspicious activity during restricted hours (10pm to 6am) or when employee clearance is under the facility security requirements
• PROC_INITIATE_LOCKDOWN → implements facility quarantine procedures, updating operational status and creating containment breach incident reports

Emergency Response Package

• TRG_ESCALATE_INCIDENTS → automatically categorizes and escalates incidents based on severity levels and incident types, with special handling for containment breaches
• EMERGENCY_ALERTS → table for custom emergency alert system for critical notifications separate from standard incident reporting
• PROC_GENERATE_EMERGENCY_ALERT → creates emergency alerts with unique identifiers and facility-specific messaging

System Validation Package

• This package is used to test specimen containment failures, security breach detection, incident escalation, and manual procedure execution

Trigger Logic

Specimen Status Monitoring

• The TRG_SPECIMEN_STATUS_CHANGE trigger fires on specimen table updates, using a cursor to process multiple simultaneous changes. Key activation conditions include:
  • When specimen status changes from any state to ‘Escaped’, automatically creates severity-9 incident reports and facility alerts
  • When threat levels increase by 3+ points, generates severity-8 incident reports for containment protocol review
  • Links incidents to specific facilities through specimen containment relationships, ensuring proper facility attribution

Access Pattern Analysis

• The TRG_MONITOR_ACCESS_PATTERNS trigger activates on access log insertions, analyzing temporal and authorization patterns:
  • Flags access attempts between 22:00-06:00 as potential security violations
  • Compares employee security clearance against facility security requirements, generating alerts for insufficient authorization
  • Uses cursor-based processing to handle bulk access logging while maintaining individual violation tracking

Incident Escalation Logic

• The TRG_ESCALATE_INCIDENTS trigger processes new incident reports with severity-based escalation:
  • Severity levels 8+ trigger immediate high-priority notifications
  • Special handling for containment breach incidents regardless of severity level
  • Medium severity incidents (6-7) receive standard alerting, while critical incidents demand immediate action

Testing Results

Specimen Containment Testing

• Test 1 → successfully detected PATHOGEN1 escape, generating incident report INC<6-digit> with severity 9 and facility lockdown recommendation
• Validation → incident report correctly linked specimen to facility through containment relationships, with accurate timestamp and description

Security Breach Validation

• Test 2 → identified after-hours access attempt by low-clearance employee (clearance <8) at high-security facility (security ≥8)
• Result → created security violation incident with severity 7, including access log reference ACC000001 and temporal analysis
• Alert System → console output confirmed real-time notification with employee ID, facility ID, and timestamp details

Incident Escalation Testing

• Test 3 → manual insertion of severity-9 containment breach incident triggered automatic escalation protocols
• Response → system correctly identified high-severity incident and containment breach type, generating dual alert messages
• Performance → trigger processed incident classification and escalation within acceptable response timeframes

Procedure Integration Testing

• Risk Assessment → PROC_ASSESS_FACILITY_RISK accurately calculated facility risk levels based on high-threat specimen counts
• Lockdown Protocol → PROC_INITIATE_LOCKDOWN successfully updated facility status to ‘Quarantined’ and created comprehensive incident documentation
• Emergency Alerts → PROC_GENERATE_EMERGENCY_ALERT created unique alert identifiers and facility-specific messaging

Performance Analysis

• Using cursors ensures complete data processing, but it might impact performance if there are high-volume operations.
• Trigger executions are efficient for single-record changing, and they have overhead for multi-record updates.
• The use of SET NOCOUNT ON and proper deallocation prevents memory leaks and reduces network traffic as well.

Collaboration Reflection

• I collaborated with Riker on this implementation, and we both worked on different trigger components. We created our own versions and then we combined them together to see what would be the best solution. I also used Claude AI to fix up some of my grammar and help out with my documentation and code styling.

Key Takeaways

• Proper cursor lifecycle management with open, fetch, close, deallocate prevents memory leaks and ensures reliable trigger execution
• Database triggers enable immediate threat detection and automated incident creation
• Cursor-based processing makes sure the entire data is handled, but it needs to be optimized for high-volume operations. (Wasn’t really a concern with our lab, though)

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───