Security Hardening

MySQL Security Hardening Complete Guide

Security is foundational to database operations. This guide covers account management, privilege control, encryption, audit logging, and threat mitigation strategies.

1. Account Management and Authentication

1.1 User Creation and Password Management

ACCOUNT CREATION:

Basic user creation:
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password_here';

-- MySQL 8.0+: Use caching_sha2_password (most secure)
CREATE USER 'app_user'@'%' IDENTIFIED WITH caching_sha2_password BY 'password';

Password requirements:

-- Enforce password policy
SET GLOBAL validate_password.policy='STRONG';
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;

User lifecycle management:

-- Lock account (prevent login)
ALTER USER 'app_user'@'%' ACCOUNT LOCK;

-- Unlock account
ALTER USER 'app_user'@'%' ACCOUNT UNLOCK;

-- Set password expiration (180 days)
ALTER USER 'app_user'@'%' PASSWORD EXPIRE INTERVAL 180 DAY;

-- Force password change at next login
ALTER USER 'app_user'@'%' PASSWORD EXPIRE;

Failed login tracking:

SET GLOBAL connection_control_failed_connections_threshold = 3;
SET GLOBAL connection_control_min_connection_delay = 1000;  -- 1 second delay

Password history (prevent reuse):

SET GLOBAL password_history = 5;  -- Can't reuse last 5 passwords
SET GLOBAL password_reuse_interval = 365;  -- Can't reuse within 365 days

1.2 Privilege Control and Principle of Least Privilege

PRIVILEGE LEVELS:

Database privileges:
GRANT ALL ON mydb.* TO 'app_db_user'@'%';

Table privileges:
GRANT SELECT, UPDATE ON mydb.users TO 'analyst'@'%';

Column privileges (restrictive, use carefully):
GRANT SELECT (id, name, email) ON mydb.users TO 'viewer'@'%';

ROLE-BASED ACCESS CONTROL (MySQL 8.0+):

Create roles instead of individual users:

-- Define roles
CREATE ROLE 'app_role'@'%';
CREATE ROLE 'analyst_role'@'%';

-- Grant privileges to roles
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app_role'@'%';
GRANT SELECT ON mydb.* TO 'analyst_role'@'%';

-- Assign role to users
GRANT 'app_role'@'%' TO 'user1'@'%', 'user2'@'%';

-- Make role default
SET DEFAULT ROLE 'app_role'@'%' FOR 'user1'@'%';

BEST PRACTICE PRIVILEGE MATRIX:

User Type         | Privileges
──────────────────────────────────
Application       | SELECT, INSERT, UPDATE, DELETE on app DB only
Backup user       | SELECT (all tables), no modify
Analytics user    | SELECT on reporting DB only
DBA               | All on all databases
Monitoring user   | SELECT from performance_schema, information_schema

-- Create minimal application user
CREATE USER 'app'@'10.0.1.%' IDENTIFIED WITH caching_sha2_password BY 'pwd';
GRANT SELECT, INSERT, UPDATE, DELETE ON production.* TO 'app'@'10.0.1.%';

-- Check user privileges
SHOW GRANTS FOR 'app_user'@'%';

-- Revoke excessive privileges
REVOKE SUPER ON *.* FROM 'old_user'@'%';
REVOKE FILE ON *.* FROM 'old_user'@'%';

2. Network Security and Firewall Rules

NETWORK ISOLATION:

Restrict connection source by host:

-- Only allow connections from application server
CREATE USER 'app'@'10.0.1.50' IDENTIFIED BY 'password';

-- Allow subnet range
CREATE USER 'app'@'10.0.1.%' IDENTIFIED BY 'password';

-- Localhost only (most secure)
CREATE USER 'local_admin'@'localhost' IDENTIFIED BY 'password';

Firewall rules (iptables example):

# Allow MySQL from app servers only
sudo iptables -A INPUT -p tcp --dport 3306 -s 10.0.1.50 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 3306 -j DROP

# Allow from subnet
sudo iptables -A INPUT -p tcp --dport 3306 -s 10.0.1.0/24 -j ACCEPT

SSL/TLS ENCRYPTION:

Enable SSL for connections:

-- Require SSL for user connections
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

-- Configure server-side SSL
[mysqld]
ssl-ca=/path/to/ca.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

# Use modern TLS version
tls-version = "TLSv1.2,TLSv1.3"

Verify SSL connection:

-- Check connection is encrypted
SHOW STATUS LIKE 'Ssl_cipher';

Result: TLS_AES_256_GCM_SHA384 (encrypted)
Result: (empty) = not encrypted

3. Data Encryption at Rest (TDE)

TRANSPARENT DATA ENCRYPTION (TDE):

MySQL 8.0+ InnoDB tablespace encryption:

-- Enable encryption for new tablespace
CREATE TABLE sensitive_data (
  id INT PRIMARY KEY,
  ssn VARCHAR(11),
  credit_card VARCHAR(19)
) ENCRYPTION='Y';

-- Enable for existing table
ALTER TABLE old_table ENCRYPTION='Y';

-- Check encryption status
SELECT
  TABLESPACE_NAME,
  ENCRYPTION
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE TABLESPACE_NAME LIKE '%sensitive%';

Key management:

-- Use keyring plugin (AWS, Vault, etc.)
[mysqld]
early-plugin-load=keyring_aws.so
keyring_aws_cmk_id=arn:aws:kms:...
keyring_aws_region=us-east-1

-- Rotate encryption keys periodically
ALTER INSTANCE ROTATE INNODB MASTER KEY;

Performance impact:

-- Encryption overhead: 3-5% CPU
-- No space overhead (transparent)
-- Query performance: negligible for most workloads

Backup security:

mysqldump --single-transaction encrypted_db | gzip | gpg --encrypt > backup.sql.gpg

4. Audit Logging and Compliance

AUDIT PLUGIN (MySQL Enterprise / Percona):

Enable audit logging:

[mysqld]
plugin-load-add=audit_log.so
audit_log_events=CONNECT,QUERY,QUERY_DDL,QUERY_DML

-- Verify plugin loaded
SHOW PLUGINS;
-- Should show: audit_log | ACTIVE

AUDITABLE EVENTS:

Connection events:
├─ User login/logout
├─ Failed authentication
├─ User created/dropped
└─ Privilege grants/revokes

Query events:
├─ SELECT on sensitive columns
├─ INSERT/UPDATE/DELETE operations
├─ DDL (CREATE/ALTER/DROP)
└─ Data changes

GENERAL LOG (development only, expensive):

-- Enable general query log
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

-- Query log table
SELECT * FROM mysql.general_log ORDER BY event_time DESC LIMIT 100;

-- Disable (performance critical in production)
SET GLOBAL general_log = 'OFF';

Compliance reporting:

-- Privileged user access
SELECT user, host, Super_priv, Grant_priv FROM mysql.user
WHERE Super_priv = 'Y' OR Grant_priv = 'Y';

-- Failed login attempts
SELECT EVENT_TIME, USER_HOST, SQLTEXT FROM mysql.audit_log
WHERE SQLTEXT LIKE '%Access denied%'
AND EVENT_TIME > DATE_SUB(NOW(), INTERVAL 1 DAY);

5. Common Vulnerabilities and Mitigation

VULNERABILITY 1: SQL Injection

Attack example:
SELECT * FROM users WHERE id = 1 OR 1=1;  -- Returns all users

Prevention:

✅ Parameterized queries (prepared statements):
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
EXECUTE stmt USING @user_id;

❌ String concatenation (vulnerable):
const query = "SELECT * FROM users WHERE id = " + userId;  -- Unsafe

VULNERABILITY 2: Weak Passwords

✅ Enforce strong passwords
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;

✅ Implement rate limiting
SET GLOBAL connection_control_failed_connections_threshold = 3;
SET GLOBAL connection_control_min_connection_delay = 5000;  -- 5 seconds

VULNERABILITY 3: Unnecessary Privileges

✅ Audit privileges
SELECT user, host, Super_priv, File_priv, Grant_priv
FROM mysql.user WHERE Super_priv='Y';

✅ Remove unnecessary
REVOKE SUPER ON *.* FROM 'app_user'@'%';
REVOKE FILE ON *.* FROM 'app_user'@'%';

VULNERABILITY 4: Anonymous Users

Check for anonymous accounts:
SELECT user, host FROM mysql.user WHERE user = '';

Remove them:
DROP USER ''@'localhost';
DROP USER ''@'%';

VULNERABILITY 5: Root without Password

-- Set root password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'secure_password';

VULNERABILITY 6: Unencrypted Replication

-- Slave configuration with SSL
CHANGE REPLICATION SOURCE TO
  MASTER_HOST='master.example.com',
  MASTER_PORT=3306,
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_SSL=1,
  MASTER_SSL_CA='/path/to/ca.pem',
  MASTER_SSL_CERT='/path/to/client-cert.pem',
  MASTER_SSL_KEY='/path/to/client-key.pem';

6. Security Best Practices Checklist

PRE-PRODUCTION SECURITY AUDIT:

Account Security:
[ ] Change default root password
[ ] Remove anonymous accounts (DROP USER ''@'%')
[ ] Remove test database
[ ] Audit all user accounts ( DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- Alert if > 10 in 1 hour

File operations (should be none):
SELECT EVENT_TIME, USER_HOST, SQLTEXT
FROM mysql.audit_log
WHERE SQLTEXT LIKE '%INTO OUTFILE%' OR SQLTEXT LIKE '%LOAD%INFILE%';

Conclusion

Security is not a one-time setup but an ongoing practice. Implement defense-in-depth: strong authentication, least privilege, network isolation, encryption, audit logging, and continuous monitoring. Regular security audits and incident response drills ensure your MySQL systems remain protected against evolving threats.