Table of Contents
Database Security Fundamentals
Database security is critical for protecting sensitive data, maintaining compliance, and ensuring business continuity. This lesson covers essential security practices and maintenance strategies.
Security Threats and Vulnerabilities
- SQL Injection: Malicious SQL code injection through user inputs
- Unauthorized Access: Weak authentication and authorization
- Data Breaches: Exposure of sensitive information
- Privilege Escalation: Users gaining unauthorized permissions
- Data Corruption: Accidental or malicious data modification
- Denial of Service: Attacks that make database unavailable
User Management and Access Control
Creating Database Users
-- Create new database user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password_123!';
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'another_strong_password!';
-- Create user with specific host restrictions
CREATE USER 'admin_user'@'192.168.1.%' IDENTIFIED BY 'admin_password_456!';
Granting Permissions
-- Grant specific permissions
GRANT SELECT, INSERT, UPDATE ON ecommerce.customers TO 'app_user'@'localhost';
GRANT SELECT ON ecommerce.products TO 'app_user'@'localhost';
-- Grant all permissions on specific database
GRANT ALL PRIVILEGES ON ecommerce.* TO 'admin_user'@'192.168.1.%';
-- Grant read-only access
GRANT SELECT ON ecommerce.* TO 'readonly_user'@'%';
-- Grant execute permission for stored procedures
GRANT EXECUTE ON PROCEDURE ecommerce.ProcessOrder TO 'app_user'@'localhost';
Revoking Permissions
-- Revoke specific permissions
REVOKE INSERT, UPDATE ON ecommerce.customers FROM 'app_user'@'localhost';
-- Revoke all permissions
REVOKE ALL PRIVILEGES ON ecommerce.* FROM 'old_user'@'localhost';
Viewing User Permissions
-- Show grants for specific user
SHOW GRANTS FOR 'app_user'@'localhost';
-- Show all users
SELECT User, Host FROM mysql.user;
-- Show detailed user permissions
SELECT
User,
Host,
Select_priv,
Insert_priv,
Update_priv,
Delete_priv
FROM mysql.user;
Password Security
Password Policies
-- Set password validation plugin (MySQL 8.0+)
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
-- Configure password policy
SET GLOBAL validate_password.policy = MEDIUM;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;
Changing Passwords
-- Change user password
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_strong_password_789!';
-- Set password expiration
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
-- Force password change on next login
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE;
SQL Injection Prevention
Using Prepared Statements
-- BAD: Vulnerable to SQL injection
-- SELECT * FROM customers WHERE email = ' + userInput + '
-- GOOD: Using prepared statements (example in application code)
-- PREPARE stmt FROM 'SELECT * FROM customers WHERE email = ?';
-- EXECUTE stmt USING @user_email;
Input Validation in Stored Procedures
DELIMITER //
CREATE PROCEDURE GetCustomerByEmail(
IN p_email VARCHAR(100)
)
BEGIN
-- Validate input
IF p_email IS NULL OR p_email = ' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email cannot be empty';
END IF;
IF LENGTH(p_email) > 100 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email too long';
END IF;
-- Safe query using parameter
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE email = p_email;
END //
DELIMITER ;
Data Encryption
Encryption at Rest
-- Enable encryption for new tables (MySQL 8.0+)
CREATE TABLE sensitive_data (
id INT PRIMARY KEY AUTO_INCREMENT,
ssn VARCHAR(11),
credit_card VARCHAR(16)
) ENCRYPTION = 'Y';
-- Encrypt existing table
ALTER TABLE customers ENCRYPTION = 'Y';
Column-Level Encryption
-- Encrypt sensitive columns
CREATE TABLE user_credentials (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password_hash VARBINARY(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert encrypted data
INSERT INTO user_credentials (username, password_hash)
VALUES ('john_doe', AES_ENCRYPT('user_password', 'encryption_key'));
-- Retrieve and decrypt data
SELECT
username,
AES_DECRYPT(password_hash, 'encryption_key') AS decrypted_password
FROM user_credentials
WHERE username = 'john_doe';
Audit and Logging
Enable General Query Log
-- Enable general query log
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;
Audit Table for Sensitive Operations
-- Create audit table
CREATE TABLE audit_log (
audit_id INT PRIMARY KEY AUTO_INCREMENT,
table_name VARCHAR(50),
operation VARCHAR(10),
user_name VARCHAR(50),
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
old_values JSON,
new_values JSON
);
-- Audit trigger example
DELIMITER //
CREATE TRIGGER customers_audit_update
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
INSERT INTO audit_log (table_name, operation, user_name, old_values, new_values)
VALUES (
'customers',
'UPDATE',
USER(),
JSON_OBJECT(
'customer_id', OLD.customer_id,
'first_name', OLD.first_name,
'last_name', OLD.last_name,
'email', OLD.email
),
JSON_OBJECT(
'customer_id', NEW.customer_id,
'first_name', NEW.first_name,
'last_name', NEW.last_name,
'email', NEW.email
)
);
END //
DELIMITER ;
Backup Strategies
Logical Backups with mysqldump
# Full database backup
mysqldump -u root -p --single-transaction --routines --triggers ecommerce > ecommerce_backup.sql
# Backup specific tables
mysqldump -u root -p ecommerce customers orders > partial_backup.sql
# Backup with compression
mysqldump -u root -p --single-transaction ecommerce | gzip > ecommerce_backup.sql.gz
# Backup all databases
mysqldump -u root -p --all-databases > all_databases_backup.sql
Physical Backups
# Using MySQL Enterprise Backup (commercial)
# mysqlbackup --user=root --password --backup-dir=/backup/full backup
# Using Percona XtraBackup (free alternative)
# xtrabackup --backup --target-dir=/backup/full --user=root --password=password
Automated Backup Script
#!/bin/bash
# backup_script.sh
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups/mysql"
DATABASE="ecommerce"
# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR
# Perform backup
mysqldump -u backup_user -p$BACKUP_PASSWORD
--single-transaction
--routines
--triggers
$DATABASE > $BACKUP_DIR/${DATABASE}_${DATE}.sql
# Compress backup
gzip $BACKUP_DIR/${DATABASE}_${DATE}.sql
# Remove backups older than 30 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete
echo "Backup completed: ${DATABASE}_${DATE}.sql.gz"
Database Maintenance
Regular Maintenance Tasks
-- Analyze tables to update statistics
ANALYZE TABLE customers, orders, products;
-- Optimize tables to reclaim space
OPTIMIZE TABLE customers, orders, products;
-- Check table integrity
CHECK TABLE customers, orders, products;
-- Repair corrupted tables (if needed)
REPAIR TABLE table_name;
Index Maintenance
-- Find unused indexes
SELECT
s.table_schema,
s.table_name,
s.index_name,
s.column_name
FROM information_schema.statistics s
WHERE s.table_schema = 'ecommerce'
AND s.index_name != 'PRIMARY'
AND s.table_name NOT LIKE 'tmp%';
-- Monitor index usage (MySQL 8.0+)
SELECT
object_schema,
object_name,
index_name,
count_read,
count_write,
count_read + count_write as total_usage
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'ecommerce'
ORDER BY total_usage DESC;
Cleanup Old Data
-- Archive old orders (example procedure)
DELIMITER //
CREATE PROCEDURE ArchiveOldOrders()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE archive_date DATE DEFAULT DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR);
-- Create archive table if not exists
CREATE TABLE IF NOT EXISTS orders_archive LIKE orders;
-- Move old orders to archive
INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < archive_date;
-- Delete archived orders from main table
DELETE FROM orders
WHERE order_date < archive_date;
-- Log the operation
INSERT INTO maintenance_log (operation, affected_rows, timestamp)
VALUES ('Archive Old Orders', ROW_COUNT(), NOW());
END //
DELIMITER ;
Monitoring and Performance
Key Metrics to Monitor
-- Connection statistics
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Threads_connected';
-- Query performance
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Uptime';
-- Buffer pool usage (InnoDB)
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- Table locks
SHOW STATUS LIKE 'Table_locks%';
Performance Schema Queries
-- Top 10 slowest queries
SELECT
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT/1000000000 AS avg_time_seconds,
SUM_TIMER_WAIT/1000000000 AS total_time_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;
-- Most accessed tables
SELECT
object_schema,
object_name,
count_read,
count_write,
count_read + count_write as total_io
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'ecommerce'
ORDER BY total_io DESC;
Disaster Recovery
Recovery Procedures
# Restore from backup
mysql -u root -p ecommerce < ecommerce_backup.sql
# Point-in-time recovery using binary logs
# 1. Restore from last full backup
mysql -u root -p ecommerce < last_full_backup.sql
# 2. Apply binary logs up to specific point
mysqlbinlog --start-datetime="2024-01-01 00:00:00"
--stop-datetime="2024-01-01 12:30:00"
mysql-bin.000001 | mysql -u root -p ecommerce
Testing Recovery Procedures
-- Create test database for recovery testing
CREATE DATABASE ecommerce_test;
-- Restore backup to test database
-- mysql -u root -p ecommerce_test < ecommerce_backup.sql
-- Verify data integrity
SELECT COUNT(*) FROM ecommerce_test.customers;
SELECT COUNT(*) FROM ecommerce_test.orders;
-- Compare with production (if safe to do so)
-- SELECT COUNT(*) FROM ecommerce.customers;
Security Best Practices Checklist
- ✓ Use strong, unique passwords for all database users
- ✓ Implement principle of least privilege
- ✓ Enable SSL/TLS for database connections
- ✓ Regularly update database software
- ✓ Monitor and audit database access
- ✓ Encrypt sensitive data at rest and in transit
- ✓ Implement proper backup and recovery procedures
- ✓ Use prepared statements to prevent SQL injection
- ✓ Regularly review and remove unused accounts
- ✓ Implement network security (firewalls, VPNs)
Maintenance Schedule Template
Frequency | Task | Purpose |
---|---|---|
Daily | Monitor error logs | Identify issues early |
Daily | Check backup completion | Ensure data protection |
Weekly | Review slow query log | Optimize performance |
Weekly | Monitor disk space | Prevent storage issues |
Monthly | Update table statistics | Maintain query performance |
Monthly | Review user accounts | Security maintenance |
Quarterly | Test backup recovery | Verify disaster recovery |
Quarterly | Archive old data | Manage database size |
Practice Exercises
- Create a comprehensive user management system with different privilege levels
- Implement an audit system to track all data modifications
- Design and test a complete backup and recovery strategy
- Create a maintenance script that performs regular optimization tasks
Conclusion
Database security and maintenance are ongoing processes that require regular attention and updates. By implementing these best practices, you can ensure your database remains secure, performant, and reliable. Remember to stay updated with the latest security patches and continuously monitor your database environment.
Congratulations on completing this comprehensive database course! You now have the knowledge to design, implement, and maintain robust database systems.