Views use SQL SECURITY INVOKER, never the DEFINER default

Every CREATE VIEW in Sql/*.sql had omitted the SQL SECURITY clause, so MariaDB defaulted each view to SQL SECURITY DEFINER, pinning it to DEFINER=<migration_user>@<host the migration ran from>. When the migration runner’s source IP changed, MariaDB could no longer resolve that definer account, and every view read by the runtime app user started failing — a production incident that took ~11 hours to diagnose because the failure surfaced far from its cause. We now require SQL SECURITY INVOKER on every view, so it runs with the querying user’s own privileges instead of a frozen creator identity. This applies to any future stored procedure, function, trigger, or event too — all four carry a DEFINER in MariaDB/MySQL. Triggers are the worst case, since they fire during someone else’s statement and the failure surfaces even further from the cause.

Considered options

• Give the migration runner a stable source IP (e.g. routing migrations through a fixed WireGuard tunnel endpoint) so the definer host never changes. Rejected as the primary fix: it only narrows the blast radius and re-breaks on the next infrastructure change (rotated tunnel endpoint, runner rebuild). Worth keeping as defense in depth, not as the fix.
• SQL SECURITY INVOKER on every view. Chosen — it removes the dependency on who created the view or where they connected from entirely.

Consequences

• Sql/0027.convert_views_to_sql_security_invoker.sql recreates all views existing at the time with CREATE OR REPLACE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW ... (verified byte-identical SELECT bodies against a live MariaDB instance — no behavior change, only the security clause).
• CI (.github/workflows/build.yml) rejects new migration files that CREATE VIEW without SQL SECURITY INVOKER or that carry an explicit DEFINER= clause, scoped to files added in the current PR (historical migration files are never edited, per this repo’s migration convention, so the gate can’t run against the full history).
• Tools/db-view-smoke-test.sh fails the build if any view’s security_type is DEFINER, and functionally SELECTs from every view as the connecting user. It runs in CI after migrations (shared test user) and in deploy.yml after migrations, authenticated as the actual runtime app user (MYSQL_USER/MYSQL_PASSWORD, distinct from the migration user) — the step that would have caught the original incident before it reached production.
• Grants still matter independently: SQL SECURITY INVOKER means the connecting user needs its own privileges on the underlying tables, not just the view.