Users & Privileges

-- 1) Create a user
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong-password';
CREATE USER 'reporting'@'10.0.0.%' IDENTIFIED BY 'pw';
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'pw';

-- Format: user@host
--   localhost    → Unix socket only
--   %            → any host (be careful)
--   10.0.0.%     → CIDR-like wildcard
--   db.app.example.com  → specific host

-- The 'host' part is part of the identity; 'mara@%' and 'mara@localhost' are different users.

-- 2) Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app_user'@'%';
GRANT SELECT ON reporting.* TO 'reporting'@'10.0.0.%';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- Privilege levels:
--   GLOBAL          *.*
--   DATABASE        db.*
--   TABLE           db.table
--   COLUMN          db.table(col)
--   STORED PROC     PROCEDURE db.proc
--   ROUTINE         FUNCTION db.fn

-- 3) Privileges quick reference
--   SELECT, INSERT, UPDATE, DELETE                 — data
--   CREATE, DROP, ALTER, INDEX                      — schema
--   REFERENCES                                       — foreign keys
--   EXECUTE                                          — call stored procedures
--   FILE                                             — read/write server files (dangerous)
--   PROCESS, RELOAD, REPLICATION CLIENT/SLAVE        — admin
--   SUPER (legacy), SESSION_VARIABLES_ADMIN          — system tweaks
--   GRANT OPTION                                     — pass on privileges

-- 4) Apply changes
FLUSH PRIVILEGES;                                    -- usually unneeded after GRANT in MySQL 5.7+; needed after manual privilege table changes

-- 5) Inspect what users can do
SHOW GRANTS FOR 'app_user'@'%';
SHOW GRANTS;                                          -- for the current user
SELECT user, host, plugin FROM mysql.user;
SELECT host, user, db, select_priv FROM mysql.db;

-- 6) Revoke
REVOKE INSERT ON app.* FROM 'app_user'@'%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'%';

-- 7) Drop a user
DROP USER 'app_user'@'%';
-- Or rename
RENAME USER 'old'@'%' TO 'new'@'%';

-- 8) Change password
ALTER USER 'app_user'@'%' IDENTIFIED BY 'new-strong-password';
-- For older MySQL:
SET PASSWORD FOR 'app_user'@'%' = PASSWORD('new-pw');

-- 9) Force password rotation
ALTER USER 'app_user'@'%' PASSWORD EXPIRE;
ALTER USER 'app_user'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'app_user'@'%' PASSWORD HISTORY 3;       -- can't reuse last 3 passwords

-- 10) Lock / unlock accounts
ALTER USER 'app_user'@'%' ACCOUNT LOCK;
ALTER USER 'app_user'@'%' ACCOUNT UNLOCK;

-- 11) Authentication plugins (MySQL 8)
CREATE USER 'modern'@'%' IDENTIFIED WITH caching_sha2_password BY 'pw';
CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password BY 'pw';
CREATE USER 'sock_admin'@'localhost' IDENTIFIED WITH auth_socket;

-- caching_sha2_password is MySQL 8 default — most secure; some old clients can't connect.
-- mysql_native_password — compatible with older drivers.
-- auth_socket — passwordless via Unix peer credentials; great for local services.

-- 12) Roles (MySQL 8+) — like Postgres roles
CREATE ROLE 'app_reader', 'app_writer';
GRANT SELECT ON app.* TO 'app_reader';
GRANT INSERT, UPDATE, DELETE ON app.* TO 'app_writer';

GRANT 'app_reader', 'app_writer' TO 'app_user'@'%';

-- After granting roles, activate them per session (or always):
ALTER USER 'app_user'@'%' DEFAULT ROLE ALL;

SHOW GRANTS FOR 'app_user'@'%' USING 'app_reader', 'app_writer';

-- 13) Resource limits
CREATE USER 'analyst'@'%' IDENTIFIED BY 'pw'
    WITH MAX_QUERIES_PER_HOUR 1000
         MAX_UPDATES_PER_HOUR 100
         MAX_CONNECTIONS_PER_HOUR 50
         MAX_USER_CONNECTIONS 5;

-- 14) SSL / TLS — require encrypted connections
CREATE USER 'app_user'@'%' IDENTIFIED BY 'pw'
    REQUIRE SSL;

-- More strict — client cert auth:
CREATE USER 'svc'@'%' REQUIRE SUBJECT '/CN=svc-app'
                                   ISSUER  '/CN=Internal CA';

-- 15) Multi-factor (MySQL 8.0.27+)
ALTER USER 'mara'@'%' ADD 2 FACTOR IDENTIFIED WITH authentication_fido;

-- 16) Least privilege for app accounts — example
CREATE USER 'app_prod'@'%' IDENTIFIED BY 'pw' REQUIRE SSL
    PASSWORD EXPIRE INTERVAL 90 DAY
    PASSWORD HISTORY 5
    WITH MAX_USER_CONNECTIONS 50;

GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app_prod'@'%';
GRANT EXECUTE ON PROCEDURE app.charge_payment TO 'app_prod'@'%';

REVOKE DROP, ALTER, CREATE ON app.* FROM 'app_prod'@'%';

-- App can't create or drop tables, only operate on data.

-- 17) Audit + monitoring
-- General log captures every statement (heavy; use sparingly)
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- MySQL Enterprise Audit / Percona Audit Log for production-grade
-- audit_log.so plugin records who did what; ship to SIEM

-- Query analysis: performance_schema.events_statements_summary_by_user_by_event_name

-- 18) Common bugs
-- • 'app_user'@'%' + 'app_user'@'localhost' both exist → confusing precedence; remove duplicates
-- • GRANT ALL on *.* to app account → root-equivalent; use scoped GRANTS only
-- • FLUSH PRIVILEGES omitted after raw INSERT into mysql.user → not active until restart or flush
-- • caching_sha2_password unsupported by old driver → 'Authentication plugin' error; install caching_sha2 client or switch to native
-- • Passwords in DSN connection strings logged → use secret stores; mask in logs
-- • REQUIRE SSL but app connects without TLS → 'access denied'; configure TLS in driver
-- • SHOW GRANTS doesn't include role-derived; pass USING to see effective
-- • Dropping a user that's actively connected → existing sessions continue until they reconnect
-- • Wildcard host '%' on admin accounts → world-routable if listening on 0.0.0.0; firewall first
-- • Reused passwords across environments → one breach = total compromise; rotate per env

CREATE USER 'app'@'%' IDENTIFIED BY 's3cret';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app'@'%';
FLUSH PRIVILEGES;