How to configure security in MariaDB and what are the security best practices?

MariaDB security configuration is crucial for protecting database security. Here are the main security configuration measures:

1. User Permission Management

sql
-- Create user and set password
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant minimum necessary permissions
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'app_user'@'localhost';

-- Revoke permissions
REVOKE DELETE ON database_name.* FROM 'app_user'@'localhost';

-- Delete user
DROP USER 'app_user'@'localhost';

-- View user permissions
SHOW GRANTS FOR 'app_user'@'localhost';

-- Flush privileges
FLUSH PRIVILEGES;

2. Configuration File Security

ini
# my.cnf security configuration

# Disable remote root login
skip-networking
# or
bind-address = 127.0.0.1

# Disable local file loading
local-infile = 0

# Limit maximum connections
max_connections = 100

# Enable SSL
require-secure-transport = ON
ssl-ca = /path/to/ca-cert.pem
ssl-cert = /path/to/server-cert.pem
ssl-key = /path/to/server-key.pem

# Set default authentication plugin
default-authentication-plugin = mysql_native_password

3. Password Policy

sql
-- Install password validation plugin
INSTALL PLUGIN simple_password_check SONAME 'simple_password_check.so';

-- Configure password policy
SET GLOBAL simple_password_check_minimal_length = 12;
SET GLOBAL simple_password_check_minimal_digit_count = 2;
SET GLOBAL simple_password_check_minimal_special_char_count = 1;
SET GLOBAL simple_password_check_minimal_uppercase_char_count = 1;

-- Force password expiration
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE;
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

4. Network Security

bash
# Configure firewall
# Only allow specific IP access
iptables -A INPUT -p tcp -s 192.168.1.0/24 --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

# Use SSH tunnel
ssh -L 3306:localhost:3306 user@remote_server

5. Data Encryption

sql
-- Enable InnoDB table encryption
-- my.cnf configuration
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4
innodb_encryption_rotate_key_age = 1

-- Create encrypted table
CREATE TABLE sensitive_data (
    id INT PRIMARY KEY,
    data VARCHAR(255),
    ENCRYPTION='Y'
) ENGINE=InnoDB ENCRYPTED=YES;

-- Use encryption functions
SELECT AES_ENCRYPT('sensitive_data', 'encryption_key');
SELECT AES_DECRYPT(encrypted_data, 'encryption_key');

6. Audit Logging

sql
-- Enable audit logging
-- my.cnf configuration
plugin_load_add = server_audit
server_audit_events = CONNECT,QUERY,TABLE
server_audit_logging = ON
server_audit_file_path = /var/log/mariadb/audit.log
server_audit_file_rotate_size = 100M
server_audit_file_rotations = 9

-- View audit logs
SELECT * FROM information_schema.server_audit;

7. Regular Security Checks

sql
-- View all users
SELECT user, host FROM mysql.user;

-- View users with empty passwords
SELECT user, host FROM mysql.user WHERE authentication_string = '';

-- View users with all privileges
SELECT user, host FROM mysql.user WHERE Grant_priv = 'Y';

-- View anonymous users
SELECT user, host FROM mysql.user WHERE user = '';

8. Backup Security

bash
# Encrypt backup files
mysqldump -u root -p database_name | gzip | openssl enc -aes-256-cbc -salt -out backup.sql.gz.enc

# Decrypt backup files
openssl enc -d -aes-256-cbc -in backup.sql.gz.enc | gunzip | mysql -u root -p database_name

9. Security Best Practices

1. Principle of Least Privilege: Only grant necessary permissions
2. Regular Updates: Install security patches promptly
3. Strong Password Policy: Use complex passwords and change regularly
4. Network Isolation: Limit database network access
5. Encrypted Transmission: Use SSL/TLS for encrypted connections
6. Audit Monitoring: Enable audit logs and review regularly
7. Backup Protection: Encrypt backup files and store securely
8. Regular Checks: Conduct regular security audits and vulnerability scans

Through these security configuration measures, you can significantly enhance MariaDB's security and protect data from unauthorized access and attacks.