Defense in Depth

MySQL security layers:

  1. Network — private subnets, firewall, no public exposure
  2. Authentication — strong passwords, certificate-based auth
  3. Authorization — least-privilege grants per application
  4. Encryption — TLS in transit, TDE at rest
  5. Auditing — log privileged operations and data access
  6. Patching — timely MySQL and OS security updates

User and Privilege Management 

  -- Application user: scoped to one database, DML only
CREATE USER 'app_web'@'10.0.1.%' IDENTIFIED BY 'LongRandomPass2024!';

GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_web'@'10.0.1.%';

-- Migration user: DDL in CI only, not runtime
CREATE USER 'app_migrate'@'10.0.2.10' IDENTIFIED BY 'MigratePass2024!';
GRANT ALL PRIVILEGES ON myapp.* TO 'app_migrate'@'10.0.2.10';

-- Read-only analyst
CREATE USER 'analyst'@'10.0.3.%' IDENTIFIED BY 'AnalystPass2024!';
GRANT SELECT ON myapp.* TO 'analyst'@'10.0.3.%';

FLUSH PRIVILEGES;

Never use root for applications. Never grant SUPER, FILE, or SHUTDOWN to app users.

Principle of Least Privilege 

  -- Column-level restriction (sensitive fields)
GRANT SELECT (id, name, email) ON myapp.users TO 'app_web'@'10.0.1.%';
-- Deny direct access to password_hash column

-- Review existing grants
SHOW GRANTS FOR 'app_web'@'10.0.1.%';

-- Revoke excessive access
REVOKE ALL PRIVILEGES ON *.* FROM 'legacy_user'@'%';
DROP USER 'legacy_user'@'%';

Authentication Plugins

MySQL 8.0 default: caching_sha2_password

  CREATE USER 'app'@'%' IDENTIFIED WITH caching_sha2_password BY 'SecurePass!';
-- Or for older clients:
CREATE USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY 'SecurePass!';

Use LDAP, PAM, or FIDO plugins for enterprise SSO integration where available.

Password Validation 

  INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.length = 14;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;
SET GLOBAL validate_password.policy = 'STRONG';

Rotate credentials quarterly; use secrets manager (Vault, AWS Secrets Manager) for injection.

Encryption in Transit (TLS) 

  [mysqld]
require_secure_transport = ON
ssl_ca = /etc/mysql/certs/ca.pem
ssl_cert = /etc/mysql/certs/server-cert.pem
ssl_key = /etc/mysql/certs/server-key.pem
tls_version = TLSv1.2,TLSv1.3
  
  ALTER USER 'app_web'@'10.0.1.%' REQUIRE SSL;
-- Or REQUIRE X509 for client certificate auth

Verify connection encryption:

  SHOW STATUS LIKE 'Ssl_cipher';

Encryption at Rest (TDE)

Enterprise and cloud options:

  # MySQL Enterprise TDE
early-plugin-load = keyring_okv.so

Cloud managed services (RDS, Cloud SQL) offer checkbox TDE with KMS-managed keys.

Application-level encryption for highly sensitive columns (SSN, PII) before storage adds defense even if database is compromised.

SQL Injection Prevention 

  -- NEVER in application code:
-- query = "SELECT * FROM users WHERE email = '" + userInput + "'"

-- ALWAYS use parameterized queries:
PREPARE stmt FROM 'SELECT id, name FROM users WHERE email = ? AND status = ?';
SET @email = '[email protected]';
SET @status = 'active';
EXECUTE stmt USING @email, @status;

ORMs (SQLAlchemy, Hibernate, Eloquent) parameterize by default — avoid raw query concatenation.

Audit Logging

MySQL Enterprise Audit:

  INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'ALL';
SET GLOBAL audit_log_format = 'JSON';

Open-source alternatives: MariaDB audit plugin, mcafee/mysql-audit, application-level audit tables, or database activity streams (AWS Database Activity Streams).

Log: login failures, GRANT changes, DDL, access to sensitive tables.

Network Hardening 

  [mysqld]
bind-address = 10.0.1.50
skip_networking = OFF
  • Place MySQL in private subnet — no public IP
  • Security group: allow 3306 only from application subnet
  • Use SSH tunnel or VPN for admin access
  • Consider MySQL Enterprise Firewall or ProxySQL query rules

Security Checklist 

  -- Remove anonymous users
DELETE FROM mysql.user WHERE User = '';

-- Remove test database
DROP DATABASE IF EXISTS test;

-- Disable LOCAL INFILE if not needed
SET GLOBAL local_infile = 0;

-- Verify no empty passwords
SELECT user, host FROM mysql.user
WHERE authentication_string = '' OR authentication_string IS NULL;

Common Mistakes

Mistake Risk
MySQL exposed to internet Brute force, ransomware, data exfiltration
Shared credentials across services One leak compromises everything
Overprivileged migration user in app DDL injection, schema destruction
No audit trail Cannot investigate breaches

Troubleshooting

“Access denied” after password change:

  -- Flush privileges; verify host pattern matches client IP
SELECT user, host FROM mysql.user WHERE user = 'app_web';
SHOW GRANTS FOR 'app_web'@'10.0.1.%';

SSL connection required: Ensure client has --ssl-mode=REQUIRED and CA certificate configured.

Production Scenario

A healthcare SaaS deployment:

  • MySQL in private VPC subnet, accessible only via ProxySQL
  • TLS required for all connections
  • Column-level encryption for PHI fields in application
  • app_web user: DML only on application schema
  • Separate break-glass admin account with MFA-protected bastion access
  • Audit logs shipped to SIEM with alerts on DDL and failed login spikes
  • Quarterly penetration test includes SQL injection and privilege escalation scenarios

Security is not a one-time setup — it requires ongoing credential rotation, patch management, access reviews, and incident response readiness.