MySQL のセキュリティ強化

mysql_secure_installation

SELECT user, host FROM mysql.user WHERE user = 'root';
-- Should show only 'localhost', '127.0.0.1', '::1'

DROP USER 'root'@'%';

-- Remove any anonymous accounts
DELETE FROM mysql.user WHERE user = '';
FLUSH PRIVILEGES;

-- Remove the test database (readable by anonymous users by default)
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE db = 'test' OR db = 'test\\_%';
FLUSH PRIVILEGES;

INSTALL COMPONENT 'file://component_validate_password';

SHOW VARIABLES LIKE 'validate_password%';

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;
SET GLOBAL validate_password.policy = 'MEDIUM';

ALTER USER 'admin'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Read-only replica account
CREATE USER 'app_read'@'10.0.1.%' IDENTIFIED BY 'strong_password';
GRANT SELECT ON myapp.* TO 'app_read'@'10.0.1.%';

-- Application write account (no schema changes)
CREATE USER 'app_write'@'10.0.1.%' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_write'@'10.0.1.%';

-- Migration account (schema changes allowed, used only by deploy scripts)
CREATE USER 'app_migrate'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON myapp.* TO 'app_migrate'@'localhost';

-- List all user accounts
SELECT user, host, account_locked, password_expired
FROM mysql.user
ORDER BY user, host;

-- Check for overly broad grants
SELECT user, host, Select_priv, Insert_priv, Update_priv, Delete_priv,
       Super_priv, Grant_priv
FROM mysql.user
WHERE host = '%' OR Super_priv = 'Y';

ALTER USER 'old_account'@'%' ACCOUNT LOCK;
-- Or drop entirely:
DROP USER 'old_account'@'%';

SHOW VARIABLES LIKE '%ssl%';
-- ssl_ca, ssl_cert, ssl_key should show file paths
-- have_ssl no longer exists on MySQL 8.4 (ERROR 1193). To confirm TLS is in
-- use, run SHOW STATUS LIKE 'Ssl_cipher' on an actual TLS connection.

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

SHOW STATUS LIKE 'Ssl_cipher';
-- Non-empty value means the current connection is encrypted

[mysqld]
bind-address = 127.0.0.1     # local only
# or
bind-address = 10.0.1.5      # specific internal IP

-- Prevent MySQL from reading/writing files on the server filesystem
-- (prevents SQL injection attacks that use LOAD DATA INFILE to read /etc/passwd)
SET GLOBAL local_infile = 0;

[mysqld]
local_infile = 0
skip_name_resolve = 1       # don't do reverse DNS lookups; use IPs in grants