MySQL & Database Design

Database Security Best Practices

13 min Lesson 32 of 40

Database Security Best Practices

Database security is critical for protecting sensitive data from unauthorized access, breaches, and attacks. In this lesson, you'll learn comprehensive security practices including the principle of least privilege, SQL injection prevention, server hardening, encrypted connections, and audit logging.

The Principle of Least Privilege

The principle of least privilege means granting users and applications only the minimum permissions necessary to perform their tasks:

Bad Practice:
GRANT ALL PRIVILEGES ON *.* TO 'webapp'@'%';
-- Too permissive! Application has full server control

Good Practice:
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.orders TO 'webapp'@'192.168.1.50';
GRANT SELECT, INSERT, UPDATE ON shop_db.products TO 'webapp'@'192.168.1.50';
-- Only necessary permissions on specific tables from specific host

Best Practice: Create separate database users for different application components. A reporting module should only have SELECT privileges, while the main application can have INSERT, UPDATE, DELETE.

SQL Injection Prevention (Recap)

SQL injection remains one of the most dangerous security vulnerabilities. Always use prepared statements:

Vulnerable PHP Code (NEVER DO THIS):
$email = $_POST['email'];
$sql = "SELECT * FROM users WHERE email = '$email'";
$result = mysqli_query($conn, $sql);
-- Attacker can inject: ' OR '1'='1

Secure PHP Code (ALWAYS DO THIS):
$stmt = $conn->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $_POST['email']);
$stmt->execute();
-- Input is safely escaped automatically

Critical: NEVER concatenate user input directly into SQL queries. Always use prepared statements with parameterized queries. This is your primary defense against SQL injection.

Securing MySQL Server Configuration

Proper server configuration is essential for security. Edit your my.cnf or my.ini configuration file:

Essential Security Settings:

[mysqld]
# Bind to specific IP (not 0.0.0.0)
bind-address = 127.0.0.1

# Disable remote root login
skip-networking = 1   # For local-only access

# Disable LOAD DATA LOCAL INFILE
local-infile = 0

# Enable binary logging for recovery
log-bin = /var/log/mysql/mysql-bin.log

# Log all queries (for audit, disable in production for performance)
general_log = 1
general_log_file = /var/log/mysql/general.log

# Log slow queries
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Set secure file privileges directory
secure_file_priv = /var/lib/mysql-files/

# Validate password strength
validate_password.policy = STRONG
validate_password.length = 12

Important: After changing configuration, restart MySQL: sudo systemctl restart mysql or sudo service mysql restart

Removing Default Accounts and Test Databases

Fresh MySQL installations may have insecure defaults. Run the mysql_secure_installation script:

Shell Command:
$ mysql_secure_installation

This script will:
✓ Set root password
✓ Remove anonymous users
✓ Disable remote root login
✓ Remove test database
✓ Reload privilege tables

Manual cleanup in SQL:
-- Remove anonymous users
DELETE FROM mysql.user WHERE User = '';

-- Remove test database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db = 'test' OR Db = 'test\_%';

-- Disable remote root access
DELETE FROM mysql.user WHERE User = 'root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Apply changes
FLUSH PRIVILEGES;

SSL/TLS Encrypted Connections

Encrypt data in transit between your application and MySQL server using SSL/TLS:

1. Check SSL support:
SHOW VARIABLES LIKE '%ssl%';

-- Look for: have_ssl = YES

2. Configure SSL in my.cnf:
[mysqld]
require_secure_transport = ON
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem

3. Require SSL for specific user:
CREATE USER 'secure_user'@'%'
IDENTIFIED BY 'SecurePass2024!'
REQUIRE SSL;

-- Or for existing user:
ALTER USER 'existing_user'@'%' REQUIRE SSL;

4. Verify SSL connection:
SHOW STATUS LIKE 'Ssl_cipher';
-- Non-empty result means SSL is active

Tip: SSL adds encryption overhead but is essential for production environments, especially when connecting over the internet or untrusted networks.

Firewall Configuration

Use firewall rules to restrict MySQL access to specific IP addresses:

UFW (Ubuntu/Debian):
# Allow MySQL from specific IP only
sudo ufw allow from 192.168.1.50 to any port 3306
sudo ufw deny 3306

FirewallD (CentOS/RHEL):
# Add rich rule for specific IP
sudo firewall-cmd --permanent --add-rich-rule='
  rule family="ipv4"
  source address="192.168.1.50/32"
  port protocol="tcp" port="3306" accept'
sudo firewall-cmd --reload

iptables:
# Allow from specific IP
sudo iptables -A INPUT -p tcp -s 192.168.1.50 --dport 3306 -j ACCEPT
# Drop all other MySQL traffic
sudo iptables -A INPUT -p tcp --dport 3306 -j DROP

Password Policies and Authentication

Enforce strong password requirements:

Install and configure password validation plugin:
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

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

-- Set strong 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;

Password expiration policy:
-- Set global default
SET GLOBAL default_password_lifetime = 90;

-- Apply to specific user
ALTER USER 'john'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

Prevent password reuse:
ALTER USER 'john'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;

Audit Logging

Enable audit logging to track database access and changes:

MySQL Enterprise Audit Plugin (Enterprise edition):
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

SET GLOBAL audit_log_policy = ALL;
SET GLOBAL audit_log_format = JSON;

General Query Log (Built-in, for development):
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- View who is doing what:
SELECT * FROM mysql.general_log ORDER BY event_time DESC LIMIT 20;

Log specific tables with triggers:
CREATE TABLE audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(50),
    action VARCHAR(10),
    user VARCHAR(50),
    old_data JSON,
    new_data JSON,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Audit trigger example
CREATE TRIGGER users_audit_insert
AFTER INSERT ON users
FOR EACH ROW
INSERT INTO audit_log (table_name, action, user, new_data)
VALUES ('users', 'INSERT', USER(), JSON_OBJECT(
    'id', NEW.id,
    'email', NEW.email,
    'name', NEW.name
));

Warning: General query log can generate large files and impact performance. Enable only during debugging or audits. Use it sparingly in production.

File Privilege Security

Restrict FILE privilege to prevent unauthorized file access:

-- Check who has FILE privilege
SELECT User, Host FROM mysql.user WHERE File_priv = 'Y';

-- Revoke FILE privilege from regular users
REVOKE FILE ON *.* FROM 'webapp'@'localhost';

-- Set secure file directory in my.cnf
[mysqld]
secure_file_priv = /var/lib/mysql-files/

-- Now LOAD DATA and SELECT INTO OUTFILE only work in that directory
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE products;

Protecting Against Privilege Escalation

Prevent users from escalating their own privileges:

-- Remove dangerous privileges from regular users
REVOKE SUPER, FILE, PROCESS, RELOAD, SHUTDOWN
ON *.* FROM 'regular_user'@'localhost';

-- Ensure only DBA accounts have GRANT OPTION
REVOKE GRANT OPTION ON *.* FROM 'developer'@'localhost';

-- Restrict stored procedure creation
REVOKE CREATE ROUTINE ON *.* FROM 'app_user'@'localhost';

-- Monitor for privilege changes
SELECT * FROM mysql.user WHERE Grant_priv = 'Y';

Data Encryption at Rest

Encrypt sensitive data stored in the database:

Application-level encryption (PHP example):
-- Store encrypted data
INSERT INTO users (email, credit_card)
VALUES (?, AES_ENCRYPT(?, 'encryption_key_here'));

-- Retrieve decrypted data
SELECT email, AES_DECRYPT(credit_card, 'encryption_key_here') AS card
FROM users WHERE id = ?;

MySQL Enterprise Transparent Data Encryption (TDE):
-- Encrypt a table (MySQL 8.0+)
ALTER TABLE sensitive_data ENCRYPTION = 'Y';

-- Encrypt entire tablespace
CREATE TABLESPACE encrypted_space
ADD DATAFILE 'encrypted.ibd'
ENCRYPTION = 'Y';

Best Practice: Never store encryption keys in the database. Use environment variables or external key management systems (KMS).

Regular Security Audits

Perform regular security audits to identify vulnerabilities:

Security Checklist Queries:

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

-- 2. Check for users with ALL PRIVILEGES
SELECT User, Host FROM mysql.user
WHERE Select_priv = 'Y' AND Insert_priv = 'Y'
  AND Update_priv = 'Y' AND Delete_priv = 'Y'
  AND Create_priv = 'Y' AND Drop_priv = 'Y';

-- 3. Check for users with '%' host (any host)
SELECT User, Host FROM mysql.user WHERE Host = '%';

-- 4. Check for old accounts with expired passwords
SELECT User, Host, password_expired, password_last_changed
FROM mysql.user WHERE password_expired = 'Y';

-- 5. List users with SUPER privilege
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';

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

Backup Security

Secure your database backups:

Secure backup practices:

# 1. Encrypt backup files
mysqldump -u backup_user -p shop_db | \
  openssl enc -aes-256-cbc -salt -out backup_encrypted.sql.enc

# 2. Set secure permissions
chmod 600 backup_encrypted.sql.enc
chown mysql:mysql backup_encrypted.sql.enc

# 3. Store backups off-site
rsync -avz --delete /backups/ remote_server:/secure_backups/

# 4. Regularly test backup restoration
mysql -u root -p test_restore_db < backup.sql

Decrypt backup when needed:
openssl enc -aes-256-cbc -d -in backup_encrypted.sql.enc -out backup.sql

Security Monitoring

Monitor MySQL for suspicious activity:

-- Monitor active connections
SELECT * FROM information_schema.PROCESSLIST
WHERE Command != 'Sleep' ORDER BY Time DESC;

-- Check failed login attempts (if audit plugin enabled)
SELECT * FROM mysql.general_log
WHERE argument LIKE '%Access denied%'
ORDER BY event_time DESC LIMIT 50;

-- Monitor privilege grants
SELECT * FROM mysql.general_log
WHERE argument LIKE '%GRANT%'
ORDER BY event_time DESC;

-- Track suspicious queries
SELECT * FROM mysql.general_log
WHERE argument LIKE '%DROP%'
   OR argument LIKE '%DELETE%'
   OR argument LIKE '%TRUNCATE%'
ORDER BY event_time DESC LIMIT 100;

Application Security Best Practices

✓ Use Prepared Statements:
  Prevents SQL injection attacks

✓ Validate All Input:
  Never trust user input, validate format and type

✓ Principle of Least Privilege:
  Grant minimum necessary permissions

✓ Separate Credentials:
  Different users for dev, staging, production

✓ Encrypt Connections:
  Use SSL/TLS for all database connections

✓ Secure Configuration Files:
  Protect .env files, set permissions to 600

✓ Regular Updates:
  Keep MySQL updated with security patches

✓ Monitor and Log:
  Enable audit logging and review regularly

✓ Backup and Test:
  Regular encrypted backups with tested restoration

✓ Network Security:
  Use firewalls, VPNs for remote access

Security Audit Exercise:

Task: Perform a security audit on your MySQL installation:

Check for users without passwords
Identify users with excessive privileges
Verify SSL is enabled
Check password expiration policies
Review remote access permissions

Solution:

-- 1. Check for empty passwords
SELECT User, Host FROM mysql.user
WHERE authentication_string = '' OR authentication_string IS NULL;

-- 2. Find users with too many privileges
SELECT User, Host,
  CONCAT(Select_priv, Insert_priv, Update_priv, Delete_priv,
         Create_priv, Drop_priv) AS privileges
FROM mysql.user
WHERE Select_priv = 'Y' AND Insert_priv = 'Y'
  AND Update_priv = 'Y' AND Delete_priv = 'Y';

-- 3. Verify SSL
SHOW VARIABLES LIKE 'have_ssl';
SELECT User, Host, ssl_type FROM mysql.user;

-- 4. Check password policies
SELECT User, Host, password_expired, password_lifetime
FROM mysql.user;

-- 5. Review remote access
SELECT User, Host FROM mysql.user
WHERE Host NOT IN ('localhost', '127.0.0.1', '::1');

Incident Response Plan

Have a plan ready for security incidents:

If Breach Suspected:

1. Immediately change all database passwords
   ALTER USER 'user'@'host' IDENTIFIED BY 'NewSecurePass';

2. Review audit logs
   SELECT * FROM mysql.general_log WHERE event_time > '2024-01-15';

3. Identify compromised accounts
   SHOW PROCESSLIST;
   SELECT User, Host FROM information_schema.PROCESSLIST;

4. Lock compromised accounts
   ALTER USER 'compromised_user'@'host' ACCOUNT LOCK;

5. Restore from clean backup if data corrupted

6. Update firewall rules

7. Document incident and lessons learned

Summary

In this lesson, you learned:

Principle of least privilege for minimal access rights
SQL injection prevention with prepared statements
Securing MySQL server configuration
Removing default accounts and test databases
Implementing SSL/TLS encrypted connections
Firewall configuration for network security
Password policies and authentication best practices
Audit logging and monitoring
File privilege and encryption security
Regular security audits and incident response

Next Up: In the next lesson, we'll cover backup and recovery strategies including full and incremental backups, point-in-time recovery, and disaster recovery planning.