mysql_secure_installation

-- Check for anonymous users
SELECT user, host, authentication_string FROM mysql.user WHERE user = '';

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

-- Remove the test database (accessible to anonymous users by default)
DROP DATABASE IF EXISTS test;

-- Remove test DB permissions from the db table
DELETE FROM mysql.db WHERE Db = 'test' OR Db = 'test\_%';

-- Reload privileges
FLUSH PRIVILEGES;

-- Verify no anonymous users remain
SELECT user, host FROM mysql.user WHERE user = '';
-- Should return: Empty set

-- Application user: only DML on its own database
CREATE USER 'webapp'@'10.0.0.%'
  IDENTIFIED WITH caching_sha2_password BY 'V3ry$tr0ng!Pass';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'webapp'@'10.0.0.%';

-- Read-only replica user: SELECT only
CREATE USER 'readonly'@'10.0.0.%' IDENTIFIED BY 'R3adOnly!Pass';
GRANT SELECT ON myapp.* TO 'readonly'@'10.0.0.%';

-- DBA user: full access but only from bastion host
CREATE USER 'dba'@'10.0.0.10' IDENTIFIED BY 'DBA$ecure99!';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'10.0.0.10' WITH GRANT OPTION;

FLUSH PRIVILEGES;

-- Never grant these to application users:
-- FILE      — reads OS files via LOAD DATA INFILE / INTO OUTFILE
-- SUPER     — bypasses read_only, can kill any connection
-- DROP/CREATE/ALTER — can destroy schema
-- GRANT OPTION — can grant their privileges to others
-- PROCESS   — sees all running queries including other users

-- Create roles
CREATE ROLE 'app_readwrite', 'app_readonly', 'app_admin';

-- Grant privileges to roles
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_readwrite';
GRANT SELECT ON myapp.*                           TO 'app_readonly';
GRANT ALL PRIVILEGES ON myapp.*                  TO 'app_admin';

-- Assign roles to users
GRANT 'app_readwrite' TO 'webapp'@'10.0.0.%';
GRANT 'app_readonly'  TO 'reporting'@'10.0.0.%';
GRANT 'app_admin'     TO 'dba'@'10.0.0.10';

-- Set default roles (auto-activated on login)
SET DEFAULT ROLE 'app_readwrite' TO 'webapp'@'10.0.0.%';

-- View role grants
SHOW GRANTS FOR 'webapp'@'10.0.0.%' USING 'app_readwrite';

# my.cnf — bind to private network interface only (not 0.0.0.0)
[mysqld]
bind-address = 10.0.0.5   # Only listen on the private IP

# Disable skip-grant-tables — NEVER leave this in production my.cnf
# skip-grant-tables  <-- REMOVE if present

# Allow MySQL only from app server IPs (Ubuntu UFW)
ufw allow from 10.0.0.5 to any port 3306
ufw allow from 10.0.0.6 to any port 3306
ufw deny 3306

# Or with iptables
iptables -A INPUT -p tcp --dport 3306 -s 10.0.0.0/24 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

# my.cnf — enable SSL with real certificates
[mysqld]
ssl_ca   = /etc/mysql/certs/ca.pem
ssl_cert = /etc/mysql/certs/server-cert.pem
ssl_key  = /etc/mysql/certs/server-key.pem
require_secure_transport = ON   # reject non-SSL connections entirely

-- Require SSL for a specific user
ALTER USER 'webapp'@'%' REQUIRE SSL;

-- Require a specific cipher for high-security environments
ALTER USER 'webapp'@'%' REQUIRE CIPHER 'ECDHE-RSA-AES256-GCM-SHA384';

-- Verify SSL status of your current connection
SHOW STATUS LIKE 'Ssl_cipher';
-- Or: s  (client status command)

-- Check SSL variables
SHOW VARIABLES LIKE '%ssl%';

# my.cnf — enable keyring plugin (must load before InnoDB initializes)
[mysqld]
early-plugin-load          = keyring_file.so
keyring_file_data          = /var/lib/mysql-keyring/keyring

# MySQL 8.0.34+ uses component-based keyring
# early-plugin-load         = component_keyring_file

-- Verify keyring plugin is loaded
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS
WHERE PLUGIN_NAME LIKE '%keyring%';

-- Create a new encrypted table
CREATE TABLE sensitive_data (
  id   INT NOT NULL AUTO_INCREMENT,
  ssn  VARCHAR(20),
  PRIMARY KEY (id)
) ENCRYPTION = 'Y';

-- Encrypt an existing table (requires a full table rebuild)
ALTER TABLE sensitive_data ENCRYPTION = 'Y';

-- Create an encrypted database (all new tables inherit encryption)
CREATE DATABASE secure_db ENCRYPTION = 'Y';

-- Check which tables are encrypted
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS
FROM information_schema.TABLES
WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';

-- Install the validate_password component (MySQL 8.0)
INSTALL COMPONENT 'file://component_validate_password';

-- Configure minimum requirements
SET GLOBAL validate_password.policy          = MEDIUM;  -- LOW, MEDIUM, or 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;

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

-- Test a password against the policy
SELECT VALIDATE_PASSWORD_STRENGTH('MyP@ssw0rd!5');
-- Returns 0-100: 0=failed, 25=LOW, 50=MEDIUM, 75=STRONG, 100=very strong

# Install Percona Audit Log Plugin
# Add to my.cnf first, then restart:
[mysqld]
plugin-load-add   = audit_log.so
audit_log_policy  = ALL      # ALL, QUERIES, LOGINS, or NONE
audit_log_format  = JSON     # JSON or OLD (legacy)
audit_log_file    = /var/log/mysql/audit.log
audit_log_rotate_on_size = 1073741824  # Rotate at 1GB

-- After installing, verify audit is active
SHOW PLUGINS;  -- should show audit_log as ACTIVE

-- Audit all current grants periodically
SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') AS stmt
FROM mysql.user
WHERE user != ''
ORDER BY user, host;

-- Check a specific user
SHOW GRANTS FOR 'webapp'@'10.0.0.%';

-- Watch for failed login attempts (performance_schema)
SELECT
  event_name,
  count_star AS failed_logins,
  last_seen
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%Access denied%'
ORDER BY count_star DESC
LIMIT 10;

-- Monitor currently running queries for suspicious patterns
SELECT
  id,
  user,
  host,
  db,
  command,
  time AS seconds_running,
  info AS query_text
FROM information_schema.PROCESSLIST
WHERE time > 30               -- running more than 30 seconds
  AND command != 'Sleep'
ORDER BY time DESC;

-- Kill a suspicious long-running query
KILL QUERY 1234;   -- kill just the query (keep connection)
KILL 1234;         -- kill the connection entirely

-- Check for unusual privilege grants made recently (via general log)
SHOW VARIABLES LIKE 'general_log%';
-- Enable briefly: SET GLOBAL general_log = ON;
-- Then review: SELECT * FROM mysql.general_log WHERE argument LIKE '%GRANT%';
-- Disable: SET GLOBAL general_log = OFF;

# Check if skip-grant-tables is in your config files
grep -r 'skip.grant' /etc/mysql/

# If found — REMOVE IT from my.cnf, then restart:
sudo systemctl restart mysql

# Verify it is not active
SHOW VARIABLES LIKE 'skip_grant_tables';
-- Value should be OFF

-- Check if general log is on
SHOW VARIABLES LIKE 'general_log';

-- It must be OFF in production
SET GLOBAL general_log = OFF;

-- If you need it temporarily for debugging:
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/tmp/mysql_general.log';
-- ... debug your issue ...
SET GLOBAL general_log = OFF;

-- Prefer the slow query log instead — only logs slow queries
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- log queries taking > 1 second

-- Quick security audit queries
-- 1. Check for anonymous users
SELECT user, host FROM mysql.user WHERE user = '';

-- 2. Check root remote access
SELECT user, host FROM mysql.user WHERE user = 'root' AND host != 'localhost';

-- 3. List users with FILE privilege (can read OS files)
SELECT user, host FROM mysql.user WHERE File_priv = 'Y';

-- 4. List users with SUPER privilege
SELECT user, host FROM mysql.user WHERE Super_priv = 'Y';

-- 5. Check SSL requirement per user
SELECT user, host, ssl_type FROM mysql.user WHERE ssl_type = '';
-- ssl_type empty = SSL not required for that user

-- 6. Check if skip-grant-tables is active (should be OFF)
SHOW VARIABLES LIKE 'skip_grant_tables';

-- 7. Check require_secure_transport
SHOW VARIABLES LIKE 'require_secure_transport';

-- Find users with FILE privilege (very dangerous — can read OS files)
SELECT user, host FROM mysql.user WHERE File_priv = 'Y' AND user != 'root';

-- Find users with SUPER privilege (can bypass read_only, kill all connections)
SELECT user, host FROM mysql.user WHERE Super_priv = 'Y' AND user != 'root';

-- Find users with DROP or CREATE privileges
SELECT user, host FROM mysql.user
WHERE Create_priv = 'Y' OR Drop_priv = 'Y' OR Alter_priv = 'Y'
ORDER BY user;

-- Find users with global (*.* level) privileges
SELECT user, host, Grant_priv, Super_priv, File_priv
FROM mysql.user
WHERE Select_priv = 'Y'
  AND user NOT IN ('root', 'mysql.sys', 'mysql.infoschema', 'mysql.session')
ORDER BY user;

-- Revoke a specific dangerous privilege from a user
REVOKE FILE ON *.* FROM 'webapp'@'%';
REVOKE SUPER ON *.* FROM 'webapp'@'%';
FLUSH PRIVILEGES;