bc66f439f2
Created migration file: - migrations/001_create_audit_logs_table.sql * Optimized for existing 4wdcsa database schema * 7 columns: log_id, user_id, action, status, ip_address, details, created_at * 8 indexes for performance (primary + 7 covering common queries) * Foreign key to users table with ON DELETE SET NULL * JSON column for flexible metadata storage * Supports all action types (login, payment, booking, membership) * Includes sample monitoring queries Created deployment guide: - DATABASE_MIGRATION_GUIDE.md * 3 deployment options (phpMyAdmin, CLI, GUI tool) * Pre/post deployment checklists * Verification queries * Rollback procedures * Performance impact analysis * Monitoring query examples * Integration instructions Ready for immediate deployment to production!
102 lines
4.9 KiB
SQL
102 lines
4.9 KiB
SQL
-- Migration: Create audit_logs table for security audit trail
|
|
-- Date: 2025-12-02
|
|
-- Description: Adds comprehensive audit logging for authentication and sensitive operations
|
|
-- Status: Ready for deployment
|
|
|
|
-- ============================================================
|
|
-- AUDIT LOGS TABLE
|
|
-- ============================================================
|
|
-- Tracks all sensitive operations for security auditing and compliance
|
|
-- Captured events: logins, password changes, bookings, payments, membership actions
|
|
|
|
CREATE TABLE IF NOT EXISTS `audit_logs` (
|
|
`log_id` int NOT NULL AUTO_INCREMENT COMMENT 'Unique audit log identifier',
|
|
`user_id` int DEFAULT NULL COMMENT 'ID of user performing action (NULL for anonymous attempts)',
|
|
`action` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Action type: login_success, login_failure, password_change, etc.',
|
|
`status` varchar(20) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Status: success, failure, pending',
|
|
`ip_address` varchar(45) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Client IP address (supports IPv4 and IPv6)',
|
|
`details` json DEFAULT NULL COMMENT 'Additional metadata (email, failure reason, amount, etc.)',
|
|
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the action occurred',
|
|
|
|
PRIMARY KEY (`log_id`),
|
|
|
|
-- Indexes for common queries
|
|
KEY `idx_user_id` (`user_id`),
|
|
KEY `idx_action` (`action`),
|
|
KEY `idx_status` (`status`),
|
|
KEY `idx_created_at` (`created_at`),
|
|
KEY `idx_ip_address` (`ip_address`),
|
|
KEY `idx_user_created` (`user_id`, `created_at`),
|
|
|
|
-- Foreign key constraint (optional, remove if cascading deletes cause issues)
|
|
CONSTRAINT `audit_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
COMMENT='Audit trail for security events and sensitive operations';
|
|
|
|
-- ============================================================
|
|
-- ACTION TYPES REFERENCE (for documentation)
|
|
-- ============================================================
|
|
-- login_success - Successful login (email/password or Google OAuth)
|
|
-- login_failure - Failed login attempt (captured with reason)
|
|
-- logout - User logout event
|
|
-- password_change - User changed their password
|
|
-- password_reset - User initiated password reset
|
|
-- booking_create - New booking created
|
|
-- booking_cancel - Booking cancelled
|
|
-- booking_modify - Booking modified
|
|
-- payment_initiate - Payment process started
|
|
-- payment_success - Payment completed successfully
|
|
-- payment_failure - Payment failed
|
|
-- membership_application - Membership application submitted
|
|
-- membership_approval - Membership approved
|
|
-- membership_renewal - Membership renewed
|
|
-- admin_action - Admin performed action
|
|
-- access_denied - Unauthorized access attempt
|
|
|
|
-- ============================================================
|
|
-- SAMPLE QUERIES FOR MONITORING
|
|
-- ============================================================
|
|
|
|
-- View last 10 login attempts
|
|
-- SELECT * FROM audit_logs WHERE action LIKE 'login%' ORDER BY created_at DESC LIMIT 10;
|
|
|
|
-- Count failed login attempts in last 15 minutes
|
|
-- SELECT COUNT(*) as failed_attempts FROM audit_logs
|
|
-- WHERE action = 'login_failure' AND created_at > DATE_SUB(NOW(), INTERVAL 15 MINUTE);
|
|
|
|
-- Get all failed logins for a specific user
|
|
-- SELECT * FROM audit_logs WHERE user_id = 5 AND action = 'login_failure' ORDER BY created_at DESC;
|
|
|
|
-- Track login attempts by IP address (detect brute force)
|
|
-- SELECT ip_address, COUNT(*) as attempt_count, MAX(created_at) as last_attempt
|
|
-- FROM audit_logs
|
|
-- WHERE action = 'login_failure' AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)
|
|
-- GROUP BY ip_address HAVING attempt_count > 5 ORDER BY attempt_count DESC;
|
|
|
|
-- View all payments for a user
|
|
-- SELECT * FROM audit_logs WHERE user_id = 5 AND action LIKE 'payment%' ORDER BY created_at DESC;
|
|
|
|
-- Audit trail for bookings
|
|
-- SELECT * FROM audit_logs WHERE user_id = 5 AND action LIKE 'booking%' ORDER BY created_at DESC;
|
|
|
|
-- Get logs with details decoded (for analysis)
|
|
-- SELECT log_id, user_id, action, status, ip_address,
|
|
-- JSON_EXTRACT(details, '$.email') as email,
|
|
-- JSON_EXTRACT(details, '$.reason') as reason,
|
|
-- created_at
|
|
-- FROM audit_logs WHERE action = 'login_failure' ORDER BY created_at DESC LIMIT 20;
|
|
|
|
-- ============================================================
|
|
-- MAINTENANCE
|
|
-- ============================================================
|
|
|
|
-- Create retention policy (keep 1 year of logs, optional)
|
|
-- DELETE FROM audit_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
|
|
|
|
-- Check table size
|
|
-- SELECT
|
|
-- table_name,
|
|
-- ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
|
|
-- FROM information_schema.TABLES
|
|
-- WHERE table_schema = DATABASE() AND table_name = 'audit_logs';
|