Security Hardening

Learning Focus

Use this lesson to harden your MySQL installation beyond basic user management — covering TLS encryption, password policies, network access controls, and security monitoring.

Concept Overview

Why Security Hardening?

Creating users and granting privileges is necessary but not sufficient. A hardened MySQL installation also:

• Encrypts connections — prevents eavesdropping on queries and results
• Enforces strong passwords — blocks weak, easily-guessed credentials
• Restricts network access — limits which IPs can even attempt to connect
• Monitors activity — detects suspicious behavior before damage occurs

The Security Layers

Layer	What It Protects	Tools
Network	Who can reach the MySQL port	Firewall rules, bind-address, host restrictions
Authentication	Who can log in	Password policies, account lockout, TLS certificates
Authorization	What authenticated users can do	Privileges, roles, views
Encryption	Data in transit	TLS/SSL connections
Monitoring	Detection of suspicious activity	Audit log, connection log, query log

---

Basic Syntax & Rules

TLS/SSL Configuration

Check TLS status:

SHOW VARIABLES LIKE '%ssl%';
SHOW VARIABLES LIKE 'require_secure_transport';

Require TLS for a user:

ALTER USER 'app_user'@'%' REQUIRE SSL;

-- Require specific certificate
ALTER USER 'secure_user'@'%'
    REQUIRE X509;

Require TLS globally:

# my.cnf
[mysqld]
require_secure_transport = ON

Password Policy (MySQL 8.0+)

-- Check current policy
SHOW VARIABLES LIKE 'validate_password%';

Variable	Recommended Value	Purpose
validate_password.policy	STRONG	Require mixed case, numbers, special chars
validate_password.length	12	Minimum password length
validate_password.mixed_case_count	1	At least 1 uppercase + 1 lowercase
validate_password.number_count	1	At least 1 digit
validate_password.special_char_count	1	At least 1 special character

Set password policy:

SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;

Or in my.cnf:

[mysqld]
validate_password.policy = STRONG
validate_password.length = 12

Account Lockout

-- Lock after 5 failed attempts, lock for 2 days
CREATE USER 'secure_user'@'%'
    IDENTIFIED BY 'Str0ng_P@ss!'
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME 2;

Network Restrictions

Bind to specific interface:

# my.cnf — only listen on private network
[mysqld]
bind-address = 10.0.1.100

# Or localhost only (most secure)
bind-address = 127.0.0.1

Firewall rules (iptables/ufw):

# Allow MySQL port only from application servers
ufw allow from 10.0.1.0/24 to any port 3306
ufw deny 3306

---

Step-by-Step Examples

Example 1: Security Audit Checklist

Run these checks on any MySQL installation:

-- 1. Check for users with no password
SELECT User, Host FROM mysql.user
WHERE authentication_string = '' OR authentication_string IS NULL;

-- 2. Check for users accessible from anywhere
SELECT User, Host FROM mysql.user WHERE Host = '%';

-- 3. Check for users with SUPER or ALL PRIVILEGES
SHOW GRANTS FOR 'root'@'localhost';

-- 4. Check if remote root login is possible
SELECT User, Host FROM mysql.user WHERE User = 'root' AND Host != 'localhost';

-- 5. Check TLS enforcement
SHOW VARIABLES LIKE 'require_secure_transport';

-- 6. Check password policy
SHOW VARIABLES LIKE 'validate_password%';

-- 7. Check for anonymous users
SELECT User, Host FROM mysql.user WHERE User = '';

---

Example 2: Secure a Fresh Installation

Step 1: Run the security script

mysql_secure_installation

This interactively:

• Sets root password
• Removes anonymous users
• Disables remote root login
• Removes test database
• Reloads privilege tables

Step 2: Remove remaining risks

-- Remove anonymous users (if any remain)
DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';

-- Ensure root can only connect locally
DROP USER IF EXISTS 'root'@'%';

-- Verify
SELECT User, Host FROM mysql.user;

Step 3: Enable TLS

SET GLOBAL require_secure_transport = ON;

Step 4: Set password policy

INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;

---

Example 3: Monitor Connection Activity

-- See currently connected users
SELECT
    USER AS connected_user,
    HOST AS from_host,
    DB   AS current_database,
    COMMAND,
    TIME AS seconds_active,
    STATE
FROM INFORMATION_SCHEMA.PROCESSLIST
ORDER BY TIME DESC;

-- Check failed login attempts (MySQL 8.0+)
SELECT * FROM performance_schema.host_cache
WHERE COUNT_AUTHENTICATION_ERRORS > 0;

---

Example 4: Disable Dangerous Features

-- Disable LOCAL INFILE (prevents loading local files into MySQL)
SET GLOBAL local_infile = OFF;

-- Disable LOAD DATA LOCAL (in my.cnf)
-- [mysqld]
-- local_infile = OFF

-- Check symbolic links (should be disabled)
SHOW VARIABLES LIKE 'have_symlink';

---

Practical Use Cases

1. Production Database Lockdown

Remove anonymous users, disable remote root, enforce TLS, restrict to application server IPs.

2. Compliance (PCI-DSS, HIPAA, GDPR)

Password policies, encrypted connections, audit logging, and access reviews satisfy regulatory requirements.

3. Multi-Tenant Isolation

Each tenant's application user is restricted to their database only — with TLS required.

4. Development Environment

Less restrictive but still secure — use a strong root password and restrict to localhost only.

5. Cloud Deployments (AWS RDS, Cloud SQL)

Managed databases handle some hardening automatically but still require proper user management and privilege scoping.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Leaving default root password	Anyone can gain full control	Change root password immediately on setup
Not running mysql_secure_installation	Anonymous users, test DB, remote root exist	Run it on every new installation
Allowing root@'%'	Root accessible from anywhere	Drop root@'%', keep root@'localhost' only
No TLS enforcement	Credentials and data sent in plaintext	Set require_secure_transport = ON
Weak password policy	Users set "password123"	Install validate_password component
No firewall on port 3306	Anyone on the network can attempt login	Restrict with ufw or security groups

---

Best Practices

• Run mysql_secure_installation on every new server
• Require TLS for all non-localhost connections
• Enforce strong passwords with the validate_password plugin
• Restrict bind-address to private IPs or localhost
• Use firewall rules to limit access to the MySQL port
• Disable dangerous features — local_infile = OFF, remove symlinks
• Audit quarterly — review all users, privileges, and password expiry status
• Rotate credentials regularly — at least every 90 days for sensitive accounts
• Use separate accounts — never share MySQL users between applications or people

---

Hands-On Practice

Exercise 1: Security Scan (Easy)

Run the security audit queries from Example 1 on your MySQL server. How many issues do you find?

Exercise 2: Harden a Development Server (Medium)

1. Create a strong root password
2. Remove anonymous users
3. Disable remote root
4. Enable the password policy plugin
5. Set minimum password length to 12
6. Verify all changes

Exercise 3: Full Production Security (Advanced)

Set up a production-ready MySQL installation:

1. Enable TLS (generate certificates if needed)
2. Require TLS for all non-localhost users
3. Set up account lockout (5 attempts, 1 day lock)
4. Enable password expiry (90 days)
5. Create application-specific users with minimal privileges
6. Set up monitoring queries for failed logins

---

Connection to Other Concepts

Related Concept	How It Connects
Users and Privileges	Hardening builds on top of proper user management
Views	Views help implement column-level access control
Stored Procedures	EXECUTE-only access hides table structure from users
Backup and Recovery	Backup users need specific, limited privileges
Performance	TLS adds slight overhead (~5–10%) but is necessary for security

What to Learn Next

• 18. Performance Optimization — optimize your secured database for speed

---

Visual Learning Diagram

flowchart TD
    A["Connection attempt"] --> B["Network layer\n(firewall, bind-address)"]
    B -->|Blocked| C["❌ Connection refused"]
    B -->|Allowed| D["Authentication\n(password, TLS)"]
    D -->|Failed| E["❌ Access denied"]
    D -->|Success| F["Authorization\n(privileges, roles)"]
    F -->|No privilege| G["❌ Permission denied"]
    F -->|Has privilege| H["✅ Query executes"]

    I["Monitoring"] --> D
    I --> F
    I --> H

    classDef block fill:#dc3545,stroke:#fff,color:#fff
    classDef pass fill:#28a745,stroke:#fff,color:#fff
    class C,E,G block
    class H pass

Quick Reference

-- Security audit
SELECT User, Host FROM mysql.user WHERE Host = '%';
SELECT User, Host FROM mysql.user WHERE authentication_string = '';
SHOW VARIABLES LIKE 'require_secure_transport';
SHOW VARIABLES LIKE 'validate_password%';

-- Harden
SET GLOBAL require_secure_transport = ON;
SET GLOBAL local_infile = OFF;
ALTER USER 'user'@'host' REQUIRE SSL;

-- Password policy
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;

-- Account lockout
ALTER USER 'user'@'host' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

-- Monitor
SELECT USER, HOST, COMMAND, TIME FROM INFORMATION_SCHEMA.PROCESSLIST;

What's Next

• 18. Performance Optimization