Users & Privileges

-- Create a user (MySQL 8+)
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password_123';

-- Create a user that can connect from any host
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password_123';

-- Create a user with specific auth plugin
CREATE USER 'app_user'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'password';

-- View existing users
SELECT user, host, plugin FROM mysql.user;

-- Grant specific privileges on a specific table
GRANT SELECT, INSERT, UPDATE ON company_db.employees TO 'app_user'@'localhost';

-- Grant on ALL tables in a database
GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.* TO 'app_user'@'localhost';

-- Grant ALL privileges on a database
GRANT ALL PRIVILEGES ON company_db.* TO 'developer'@'localhost';

-- Grant global privileges (across all databases)
GRANT CREATE, DROP ON *.* TO 'admin'@'localhost';

-- Grant SELECT only (read-only user)
GRANT SELECT ON company_db.* TO 'readonly_user'@'%';

-- Always apply changes
FLUSH PRIVILEGES;

-- ❌ Too much: app user doesn't need DROP/CREATE
GRANT ALL PRIVILEGES ON company_db.* TO 'app_user'@'localhost';

-- ✅ Correct: only what the app needs
GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.* TO 'app_user'@'localhost';

-- Revoke specific privilege
REVOKE DELETE ON company_db.* FROM 'app_user'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON company_db.* FROM 'app_user'@'localhost';

-- Check what privileges a user has
SHOW GRANTS FOR 'app_user'@'localhost';

-- Create a role
CREATE ROLE 'read_only', 'read_write', 'admin_role';

-- Grant privileges to the role
GRANT SELECT ON company_db.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.* TO 'read_write';
GRANT ALL PRIVILEGES ON company_db.* TO 'admin_role';

-- Assign role to users
GRANT 'read_only' TO 'reporting_user'@'localhost';
GRANT 'read_write' TO 'app_user'@'localhost';

-- Set default role (auto-activated on login)
SET DEFAULT ROLE 'read_write' TO 'app_user'@'localhost';

-- Activate role manually
SET ROLE 'read_only';

-- View current roles
SELECT CURRENT_ROLE();

-- Show all users
SELECT User, Host FROM mysql.user;

-- Show grants for current user
SHOW GRANTS;

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

-- Drop a user
DROP USER 'old_user'@'localhost';

✅ Use strong passwords (12+ chars, mixed case, numbers, symbols)
✅ Create separate users for each application
✅ Grant minimum necessary privileges (least privilege)
✅ Use roles to manage permissions consistently
✅ Regularly audit user privileges
✅ Remove unused accounts

❌ Never use root for applications
❌ Don't grant ALL PRIVILEGES to app users
❌ Avoid using '%' host (allow from any host) when possible