'app_user'@'localhost'        -- Unix socket or TCP to 127.0.0.1
'app_user'@'127.0.0.1'       -- TCP only to loopback
'app_user'@'%'               -- any host (wildcard)

-- When a client connects, MySQL picks the MOST SPECIFIC matching account:
-- exact hostname > IP address > subnet wildcard > '%'

-- Create user with caching_sha2_password (MySQL 8.0 default)
CREATE USER 'modern_user'@'%'
  IDENTIFIED WITH caching_sha2_password BY 'SecurePass123!';

-- Create user with mysql_native_password (for legacy client compatibility)
CREATE USER 'legacy_app'@'%'
  IDENTIFIED WITH mysql_native_password BY 'LegacyPass123!';

-- Check which plugin each user is using
SELECT user, host, plugin
FROM mysql.user
WHERE user NOT LIKE 'mysql%' AND user NOT LIKE 'sys%';

-- Switch an existing user to mysql_native_password
ALTER USER 'legacy_app'@'%'
  IDENTIFIED WITH mysql_native_password BY 'LegacyPass123!';

-- Basic CREATE USER
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'SecurePass123!';

-- Allow connections from any host
CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppP@ssw0rd!';

-- Create only if the account does not exist (avoids error)
CREATE USER IF NOT EXISTS 'bob'@'localhost' IDENTIFIED BY 'BobsP@ss!';

-- Full options example
CREATE USER 'api_user'@'%'
  IDENTIFIED WITH caching_sha2_password BY 'ApiP@ssword!'
  PASSWORD EXPIRE INTERVAL 180 DAY    -- force password change every 6 months
  FAILED_LOGIN_ATTEMPTS 5             -- lock after 5 consecutive failures
  PASSWORD_LOCK_TIME 1                -- lock for 1 day after max failures
  COMMENT 'API service account for inventory app';

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

-- Typical production settings in my.cnf:
-- [mysqld]
-- 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

-- Test a password against the current policy (returns 0-100)
SELECT VALIDATE_PASSWORD_STRENGTH('weakpassword');   -- low score
SELECT VALIDATE_PASSWORD_STRENGTH('Str0ng@Pass!99'); -- higher score

-- Lock an account (disables login without deleting the account)
ALTER USER 'migrator'@'localhost' ACCOUNT LOCK;

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

-- Expire password immediately (forces change on next login)
ALTER USER 'alice'@'localhost' PASSWORD EXPIRE;

-- Set a password expiration interval
ALTER USER 'alice'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Mark password as never expiring
ALTER USER 'service_account'@'%' PASSWORD EXPIRE NEVER;

-- Force password change after too many failed logins
ALTER USER 'alice'@'localhost'
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME 2;   -- lock for 2 days after 3 failures

-- Change your own password (current user)
ALTER USER USER() IDENTIFIED BY 'NewP@ssword!';

-- Change another user's password (requires CREATE USER or SYSTEM_USER privilege)
ALTER USER 'alice'@'localhost' IDENTIFIED BY 'AliceNewPass!';

-- Check password expiry and lock status
SELECT user, host, account_locked, password_expired,
       password_last_changed, password_lifetime
FROM mysql.user
WHERE user = 'alice';

-- Set resource limits on an account
ALTER USER 'webapp'@'%'
  WITH MAX_CONNECTIONS_PER_HOUR 10000   -- Max new connections per hour
       MAX_QUERIES_PER_HOUR     200000  -- Max queries per hour
       MAX_UPDATES_PER_HOUR      50000  -- Max updates per hour
       MAX_USER_CONNECTIONS         50; -- Max simultaneous connections at any time

-- Reset all limits (set to 0 = unlimited)
ALTER USER 'webapp'@'%'
  WITH MAX_CONNECTIONS_PER_HOUR 0
       MAX_QUERIES_PER_HOUR     0
       MAX_UPDATES_PER_HOUR     0
       MAX_USER_CONNECTIONS     0;

-- Create the real account with actual privileges
CREATE USER 'real_user'@'%' IDENTIFIED BY 'real_pass';
GRANT SELECT ON mydb.* TO 'real_user'@'%';

-- Create the proxy account (no password needed — uses proxy auth)
CREATE USER 'proxy_user'@'%' IDENTIFIED WITH mysql_no_login;

-- Grant proxy privilege
GRANT PROXY ON 'real_user'@'%' TO 'proxy_user'@'%';

-- List all user accounts with key attributes
SELECT user, host, account_locked, password_expired,
       plugin, password_last_changed
FROM mysql.user
ORDER BY user, host;

-- Show grants for a specific user
SHOW GRANTS FOR 'alice'@'localhost';

-- Show grants for the current user
SHOW GRANTS;

-- Detailed grants for a user
SHOW GRANTS FOR 'app_user'@'%'G

-- Rename a user (privileges are transferred)
RENAME USER 'old_name'@'localhost' TO 'new_name'@'localhost';

-- Drop a user (also revokes all their privileges)
DROP USER 'alice'@'localhost';

-- Drop if exists (avoids error if user does not exist)
DROP USER IF EXISTS 'alice'@'localhost';

-- Drop multiple users at once
DROP USER IF EXISTS
  'dev_user'@'localhost',
  'test_user'@'%',
  'old_service'@'10.0.0.5';

-- 1. Application account — least privilege needed to run the app
CREATE USER 'app_user'@'10.0.0.%'
  IDENTIFIED WITH caching_sha2_password BY 'W3bAppP@ss!'
  COMMENT 'Main web application service account';

GRANT SELECT, INSERT, UPDATE, DELETE
  ON myapp_db.*
  TO 'app_user'@'10.0.0.%';

-- 2. Read-only reporting / BI account
CREATE USER 'readonly_user'@'10.0.0.%'
  IDENTIFIED BY 'R3adOnlyP@ss!'
  COMMENT 'Read-only access for BI tools and analysts';

GRANT SELECT ON myapp_db.* TO 'readonly_user'@'10.0.0.%';

-- 3. Backup account — minimal privileges for mysqldump
CREATE USER 'backup_user'@'localhost'
  IDENTIFIED BY 'Backup@gent!99'
  COMMENT 'Database backup via mysqldump';

GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD
  ON *.* TO 'backup_user'@'localhost';

-- 4. Migration account — schema changes only during deployments
CREATE USER 'migrator'@'localhost'
  IDENTIFIED BY 'M1grateP@ss!9'
  COMMENT 'Schema migration account — lock when not deploying';

GRANT CREATE, ALTER, DROP, INDEX, REFERENCES, INSERT, SELECT, UPDATE, DELETE
  ON myapp_db.*
  TO 'migrator'@'localhost';

-- Lock migration account until a deployment is in progress
ALTER USER 'migrator'@'localhost' ACCOUNT LOCK;

-- Apply all privilege changes immediately
FLUSH PRIVILEGES;

-- See all active connections
SHOW PROCESSLIST;

-- More detail from information_schema
SELECT id, user, host, db, command, time, state, info
FROM information_schema.PROCESSLIST
ORDER BY time DESC;

-- Count active connections per user
SELECT user, COUNT(*) AS connections
FROM information_schema.PROCESSLIST
GROUP BY user
ORDER BY connections DESC;

-- Kill a specific connection (use with caution)
KILL CONNECTION 42;

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

-- Require a specific cipher
ALTER USER 'secure_app'@'%' REQUIRE CIPHER 'ECDHE-RSA-AES256-GCM-SHA384';

-- Require a client certificate (mutual TLS)
ALTER USER 'secure_app'@'%'
  REQUIRE SUBJECT '/CN=app-client/O=MyOrg/C=US'
  AND ISSUER '/CN=MyCA/O=MyOrg/C=US';

-- Remove SSL requirement
ALTER USER 'secure_app'@'%' REQUIRE NONE;

-- Check if SSL is required for a user
SELECT user, host, ssl_type, ssl_cipher
FROM mysql.user WHERE user = 'secure_app';

-- Create user with failed-login protection
CREATE USER 'alice'@'%'
  IDENTIFIED BY 'SecurePass!'
  FAILED_LOGIN_ATTEMPTS 5      -- lock after 5 consecutive failures
  PASSWORD_LOCK_TIME 1;        -- lock for 1 day

-- Manually check lock status
SELECT user, host, account_locked,
       Password_reuse_history,
       Password_reuse_time
FROM mysql.user WHERE user = 'alice';

-- Manually unlock after a lockout
ALTER USER 'alice'@'%' ACCOUNT UNLOCK;

-- Check authentication history (MySQL 8.0+)
SELECT user, event_time, status, ip
FROM mysql.general_log
WHERE argument LIKE '%alice%'
ORDER BY event_time DESC
LIMIT 20;

-- Create roles
CREATE ROLE 'app_read_write', 'analytics_read', 'devops_admin';

-- Grant privileges to the roles
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'app_read_write';
GRANT SELECT ON myapp_db.*           TO 'analytics_read';
GRANT ALL PRIVILEGES ON *.*          TO 'devops_admin';

-- Assign roles to users
GRANT 'app_read_write' TO 'webapp'@'%';
GRANT 'analytics_read' TO 'readonly_user'@'10.0.0.%';
GRANT 'devops_admin'   TO 'alice'@'localhost';

-- Set default roles (activated automatically on login)
SET DEFAULT ROLE 'app_read_write' TO 'webapp'@'%';
SET DEFAULT ROLE ALL TO 'alice'@'localhost';

-- Users can also activate roles in their session
SET ROLE 'analytics_read';

-- View current active roles
SELECT CURRENT_ROLE();

-- Show all role grants
SHOW GRANTS FOR 'alice'@'localhost' USING 'devops_admin';

-- Grant specific privileges on a specific database
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'webapp'@'%';

-- Grant on a specific table only
GRANT SELECT ON myapp_db.products TO 'readonly_user'@'%';

-- Grant on a specific column only (column-level privileges)
GRANT SELECT (id, name, price) ON myapp_db.products TO 'price_viewer'@'%';

-- Grant all privileges on a database
GRANT ALL PRIVILEGES ON myapp_db.* TO 'dba_user'@'localhost';

-- Grant global privileges (use with caution)
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor_user'@'%';

-- Revoke specific privileges
REVOKE DELETE ON myapp_db.* FROM 'webapp'@'%';

-- Revoke all privileges
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_user'@'localhost';

-- Apply changes (usually automatic but ensures they take effect)
FLUSH PRIVILEGES;