User Management in MySQL

-- Create user
CREATE USER 'john'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'app'@'%' IDENTIFIED BY 'secure_password';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE ON database.* TO 'john'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- Specific table permissions
GRANT SELECT, INSERT ON database.users TO 'app'@'%';

-- Show grants
SHOW GRANTS FOR 'john'@'localhost';

-- Revoke privileges
REVOKE INSERT ON database.* FROM 'john'@'localhost';

-- DETAILED_TAB:
-- Password management
ALTER USER 'john'@'localhost' IDENTIFIED BY 'new_password';

-- Password expiry
ALTER USER 'john'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Account locking
ALTER USER 'john'@'localhost' ACCOUNT LOCK;
ALTER USER 'john'@'localhost' ACCOUNT UNLOCK;

-- Resource limits
ALTER USER 'app'@'%' WITH
  MAX_QUERIES_PER_HOUR 1000
  MAX_CONNECTIONS_PER_HOUR 100
  MAX_USER_CONNECTIONS 10;

-- List all users
SELECT user, host FROM mysql.user;

-- Drop user
DROP USER 'john'@'localhost';

-- Flush privileges (if modifying mysql tables directly)
FLUSH PRIVILEGES;