Monitoring and Auditing Database Access

Monitoring and Auditing Database Access

Comprehensive auditing provides both security monitoring and compliance evidence. Database audit logs should capture schema changes, permission modifications, failed authentication attempts, and access to sensitive data. However, excessive logging can impact performance and generate overwhelming data volumes. Selective auditing based on data sensitivity and user roles balances security needs with system performance.

Real-time monitoring enables rapid response to security incidents. Unusual query patterns might indicate SQL injection attempts or data exfiltration. Sudden increases in data access could signal compromised credentials. Database Activity Monitoring (DAM) solutions provide specialized capabilities for identifying suspicious database activities, though they require careful tuning to minimize false positives.

// Example: Implementing database audit logging system
import java.sql.*;
import java.time.Instant;
import java.util.concurrent.*;
import com.fasterxml.jackson.databind.ObjectMapper;

public class DatabaseAuditLogger {
    private final ExecutorService auditExecutor;
    private final BlockingQueue<AuditEvent> auditQueue;
    private final Connection auditConnection;
    private final ObjectMapper objectMapper;
    
    public DatabaseAuditLogger(DataSource auditDataSource) throws SQLException {
        this.auditExecutor = Executors.newSingleThreadExecutor();
        this.auditQueue = new LinkedBlockingQueue<>(10000);
        this.auditConnection = auditDataSource.getConnection();
        this.objectMapper = new ObjectMapper();
        
        // Start audit processor
        this.auditExecutor.submit(this::processAuditQueue);
    }
    
    public void logDataAccess(DataAccessEvent event) {
        AuditEvent auditEvent = new AuditEvent();
        auditEvent.eventType = "DATA_ACCESS";
        auditEvent.timestamp = Instant.now();
        auditEvent.userId = event.getUserId();
        auditEvent.sessionId = event.getSessionId();
        auditEvent.ipAddress = event.getIpAddress();
        auditEvent.query = sanitizeQuery(event.getQuery());
        auditEvent.tables = event.getTablesAccessed();
        auditEvent.rowCount = event.getRowCount();
        auditEvent.executionTime = event.getExecutionTime();
        auditEvent.sensitivityLevel = determineSensitivityLevel(event.getTablesAccessed());
        
        // Async queue to prevent blocking main execution
        if (!auditQueue.offer(auditEvent)) {
            // Queue full - log to file as fallback
            logToFile(auditEvent);
        }
    }
    
    private void processAuditQueue() {
        try (PreparedStatement stmt = auditConnection.prepareStatement(
            "INSERT INTO audit_log (event_type, timestamp, user_id, session_id, " +
            "ip_address, query_hash, tables_accessed, row_count, execution_time, " +
            "sensitivity_level, event_data) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?::jsonb)"
        )) {
            
            while (!Thread.currentThread().isInterrupted()) {
                AuditEvent event = auditQueue.take();
                
                stmt.setString(1, event.eventType);
                stmt.setTimestamp(2, Timestamp.from(event.timestamp));
                stmt.setString(3, event.userId);
                stmt.setString(4, event.sessionId);
                stmt.setString(5, event.ipAddress);
                stmt.setString(6, hashQuery(event.query));
                stmt.setArray(7, auditConnection.createArrayOf("varchar", event.tables));
                stmt.setInt(8, event.rowCount);
                stmt.setLong(9, event.executionTime);
                stmt.setString(10, event.sensitivityLevel);
                stmt.setString(11, objectMapper.writeValueAsString(event));
                
                stmt.executeUpdate();
                
                // Check for suspicious patterns
                checkSuspiciousActivity(event);
            }
        } catch (Exception e) {
            logger.error("Audit processing error", e);
        }
    }
    
    private void checkSuspiciousActivity(AuditEvent event) {
        // Check for potential SQL injection patterns
        if (containsSQLInjectionPatterns(event.query)) {
            alertSecurityTeam("Potential SQL injection detected", event);
        }
        
        // Check for unusual data access volumes
        if (event.rowCount > 10000 && event.sensitivityLevel.equals("HIGH")) {
            alertSecurityTeam("Large sensitive data access", event);
        }
        
        // Check for after-hours access
        if (isAfterHours(event.timestamp) && event.sensitivityLevel.equals("HIGH")) {
            alertSecurityTeam("After-hours sensitive data access", event);
        }
    }
    
    private String sanitizeQuery(String query) {
        // Remove sensitive values while preserving query structure
        return query
            .replaceAll("'[^']*'", "'?'")  // Replace string literals
            .replaceAll("\\b\\d+\\b", "?")  // Replace numbers
            .replaceAll("\\s+", " ")        // Normalize whitespace
            .trim();
    }
    
    private boolean containsSQLInjectionPatterns(String query) {
        String[] suspiciousPatterns = {
            "';--", "' OR ", "' AND ", "UNION SELECT", "exec(",
            "xp_cmdshell", "sp_executesql", "'; DROP "
        };
        
        String upperQuery = query.toUpperCase();
        for (String pattern : suspiciousPatterns) {
            if (upperQuery.contains(pattern.toUpperCase())) {
                return true;
            }
        }
        
        return false;
    }
    
    // Performance monitoring for database operations
    public class PerformanceMonitor {
        private final Map<String, QueryStatistics> queryStats = new ConcurrentHashMap<>();
        
        public void recordQueryExecution(String queryHash, long executionTime) {
            queryStats.compute(queryHash, (key, stats) -> {
                if (stats == null) {
                    stats = new QueryStatistics();
                }
                stats.recordExecution(executionTime);
                return stats;
            });
            
            // Alert on performance degradation
            QueryStatistics stats = queryStats.get(queryHash);
            if (stats.isPerformanceDegraded()) {
                alertOperationsTeam("Query performance degradation", queryHash, stats);
            }
        }
    }
    
    static class AuditEvent {
        String eventType;
        Instant timestamp;
        String userId;
        String sessionId;
        String ipAddress;
        String query;
        String[] tables;
        int rowCount;
        long executionTime;
        String sensitivityLevel;
    }
}