-- Install the password validation plugin
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Check if the plugin is loaded
SHOW PLUGINS;

-- Set password policy to STRONG
SET GLOBAL validate_password.policy = 'STRONG';

-- Set minimum password length
SET GLOBAL validate_password.length = 12;

-- Require mixed case characters
SET GLOBAL validate_password.mixed_case_count = 1;

-- Require numeric characters
SET GLOBAL validate_password.number_count = 1;

-- Require special characters
SET GLOBAL validate_password.special_char_count = 1;

-- Change password for a user (MySQL 5.7.6+)
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

-- Alternative method for older versions
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('new_password');

-- Change your own password
SET PASSWORD = PASSWORD('new_password');

-- Set password to expire immediately
ALTER USER 'username'@'localhost' PASSWORD EXPIRE;

-- Set password to never expire
ALTER USER 'username'@'localhost' PASSWORD EXPIRE NEVER;

-- Set password to expire in 90 days
ALTER USER 'username'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Set default password expiration for all users
SET GLOBAL default_password_lifetime = 180;

-- Prevent reuse of last 5 passwords
ALTER USER 'username'@'localhost' PASSWORD HISTORY 5;

-- Set password reuse interval (days)
ALTER USER 'username'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;

-- Global password history settings
SET GLOBAL password_history = 5;
SET GLOBAL password_reuse_interval = 365;

-- Create user with native password authentication
CREATE USER 'user1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

-- Change authentication method
ALTER USER 'user1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

-- Create user with SHA-256 authentication
CREATE USER 'user2'@'localhost' IDENTIFIED WITH sha256_password BY 'password';

-- Enable SHA-256 over secure connections
ALTER USER 'user2'@'localhost' IDENTIFIED WITH sha256_password BY 'password' 
REQUIRE SSL;

-- Create user with caching SHA-2 authentication (default in MySQL 8.0)
CREATE USER 'user3'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

-- Set as default authentication plugin
SET GLOBAL default_authentication_plugin = 'caching_sha2_password';

-- Check password strength
SELECT VALIDATE_PASSWORD_STRENGTH('password123');

-- Example of a strong password policy configuration
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 14;
SET GLOBAL validate_password.mixed_case_count = 2;
SET GLOBAL validate_password.number_count = 2;
SET GLOBAL validate_password.special_char_count = 2;
SET GLOBAL validate_password.dictionary_file = '/path/to/dictionary.txt';

-- Lock account after failed login attempts
CREATE USER 'user4'@'localhost' 
IDENTIFIED BY 'password' 
FAILED_LOGIN_ATTEMPTS 3 
PASSWORD_LOCK_TIME 2;

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

-- Check account status
SELECT user, host, account_locked FROM mysql.user WHERE user = 'user4';

-- Reset password when user is locked out
-- Step 1: Start MySQL in safe mode (command line)
-- mysqld_safe --skip-grant-tables &

-- Step 2: Connect without password
-- mysql -u root

-- Step 3: Reset password
UPDATE mysql.user SET authentication_string = PASSWORD('new_password') 
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

-- Step 4: Restart MySQL normally

-- View current password validation settings
SHOW VARIABLES LIKE 'validate_password%';

-- Check failed login attempts
SELECT * FROM performance_schema.events_statements_history 
WHERE sql_text LIKE '%ACCESS DENIED%';

-- Check users with weak passwords (requires audit)
SELECT user, host, 
       password_last_changed,
       password_lifetime,
       account_locked
FROM mysql.user 
WHERE password_expired = 'Y';

-- Check password expiration status
SELECT user, host, password_expired, password_last_changed
FROM mysql.user
WHERE password_expired = 'Y' OR 
      (password_lifetime IS NOT NULL AND 
       password_last_changed < DATE_SUB(NOW(), INTERVAL password_lifetime DAY));

[mysqld]
# Password validation plugin
plugin-load-add=validate_password.so
validate_password.policy=STRONG
validate_password.length=12
validate_password.mixed_case_count=1
validate_password.number_count=1
validate_password.special_char_count=1

# Default password lifetime (days)
default_password_lifetime=180

# Password history
password_history=5
password_reuse_interval=365

# Account locking
connection_control_failed_connections_threshold=3
connection_control_min_connection_delay=1000
connection_control_max_connection_delay=86400

-- Check if password validation is causing issues
SHOW VARIABLES LIKE 'validate_password%';

-- Test password strength
SELECT VALIDATE_PASSWORD_STRENGTH('test_password');

-- Check user authentication method
SELECT user, host, plugin FROM mysql.user WHERE user = 'username';

-- View password expiration info
SELECT user, host, password_expired, password_last_changed, 
       password_lifetime, account_locked 
FROM mysql.user 
WHERE user = 'username';

-- Temporarily disable password validation
SET GLOBAL validate_password.policy = 'LOW';
SET GLOBAL validate_password.length = 4;

-- Change password
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

-- Re-enable strong password policy
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;