MySQL Security

MySQL Practical Commands with Security

Installation and Initial Setup

# Update package index
sudo apt update

# Install MySQL server
sudo apt install mysql-server

# Secure MySQL installation
sudo mysql_secure_installation

During the secure installation, you'll be prompted to set the root password, remove anonymous users, disallow root login remotely, remove test databases, and reload privilege tables. This aligns with OWASP A2: Broken Authentication and A5: Security Misconfiguration.

Create a Database and User with Least Privilege

# Log in to MySQL
sudo mysql -u root -p

# Create a database
CREATE DATABASE my_secure_db;

# Create a user with limited privileges
CREATE USER 'secure_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

# Grant privileges to the user on the specific database
GRANT SELECT, INSERT, UPDATE, DELETE ON my_secure_db.* TO 'secure_user'@'localhost';

# Apply changes
FLUSH PRIVILEGES;

Following the NIST SP 800-53 AC-2 guideline, create users with the least privilege necessary.

Enforce Strong Password Policies

# Install the validate_password plugin
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

# Configure password policy
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 = 2; # STRONG policy

OWASP A2 and NIST SP 800-63 recommend enforcing strong passwords.

Enable Secure Connections (SSL/TLS)

# Generate SSL certificates
sudo openssl genrsa 2048 > ca-key.pem
sudo openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem
sudo openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
sudo openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
sudo openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
sudo openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

# Move certificates to /etc/mysql directory
sudo mkdir /etc/mysql/ssl
sudo mv *.pem /etc/mysql/ssl
sudo chown mysql:mysql /etc/mysql/ssl/*

# Update MySQL configuration
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Add the following lines to the [mysqld] section:
[mysqld]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

# Restart MySQL service
sudo systemctl restart mysql

OWASP A3: Sensitive Data Exposure and NIST SP 800-52 recommend using encryption.

Regular Backups and Logging

# Schedule regular backups using cron
sudo crontab -e

# Add the following line to backup MySQL database daily at 2 AM
0 2 * * * /usr/bin/mysqldump -u root -p'StrongPassword!' my_secure_db > /var/backups/my_secure_db.sql

# Enable and configure MySQL logging
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Add the following lines to enable logging:
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log
log_error = /var/log/mysql/error.log

# Restart MySQL service to apply changes
sudo systemctl restart mysql

OWASP A10: Insufficient Logging & Monitoring and NIST SP 800-92 guidelines recommend regular backups and monitoring.

Audit and Monitor

# Enable the audit log plugin (available in MySQL Enterprise Edition)
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

# Configure audit log
SET GLOBAL audit_log_policy = 'ALL';

OWASP A10 and NIST SP 800-92 also recommend enabling auditing and monitoring.

Post a Comment