MySQL User Permissions Best Practices: Least Privilege and Password Policy

-- Create application user with only needed privileges
CREATE USER 'app_service'@'10.0.%' IDENTIFIED BY 'str0ng_p@ss';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_service'@'10.0.%';

-- Read-only analytics user
CREATE USER 'analytics'@'10.0.%' IDENTIFIED BY 'read_p@ss';
GRANT SELECT ON myapp.* TO 'analytics'@'10.0.%';

-- Schema migration user (restricted to deploy window)
CREATE USER 'migrator'@'localhost' IDENTIFIED BY 'mig_p@ss';
GRANT ALTER, CREATE, DROP, INDEX ON myapp.* TO 'migrator'@'localhost';

-- 'user'@'%' allows connections from anywhere — avoid in production
-- 'user'@'10.0.0.0/255.255.0.0' limits to subnet
-- 'user'@'app.internal' limits to specific host

-- Check current users and hosts
SELECT user, host, plugin, password_expired
FROM mysql.user
ORDER BY user, host;

-- Enable password validation plugin
INSTALL COMPONENT 'file://component_validate_password';

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;

-- Expire password every 90 days for interactive users
CREATE USER 'dba_user'@'localhost'
  IDENTIFIED BY 'p@ssword'
  PASSWORD EXPIRE INTERVAL 90 DAY;

-- Lock account after 5 failed attempts
ALTER USER 'dba_user'@'localhost'
  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

-- Unlock a locked account
ALTER USER 'dba_user'@'localhost' ACCOUNT UNLOCK;

-- Find users with dangerous global privileges
SELECT user, host
FROM information_schema.user_privileges
WHERE privilege_type IN ('SUPER','FILE','PROCESS','REPLICATION SLAVE')
ORDER BY user;

-- Find users with wildcard host access
SELECT user, host FROM mysql.user WHERE host = '%';