Server-Side Logic Testing, Deployment, Privileges, and Application-Boundary Decisions

Learning outcomes

A release can pass application tests and still fail in production because a restored view names a missing definer, a trigger is created before the routine it calls, an event becomes enabled before its grants are ready, or a rollback script restores table DDL but not stored objects. Server-side logic is executable production code. It needs source control, dependency order, privilege review, repeatable tests, deployment evidence and a decision about whether the database is actually the right ownership boundary.

1. Build a scratch deployment fixture

This lesson is independently runnable. Start from a disposable schema and create one representative view, procedure, function, trigger and disabled event. The definitions are deliberately small so deployment mechanics—not business complexity—remain visible.

DROP DATABASE IF EXISTS servicehub_programmability_lab;CREATE DATABASE servicehub_programmability_lab;USE servicehub_programmability_lab;CREATE TABLE work_orders (  work_order_id BIGINT PRIMARY KEY,  status ENUM('open','closed') NOT NULL,  priority TINYINT NOT NULL,  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;CREATE TABLE work_order_audit (  audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,  work_order_id BIGINT NOT NULL,  old_status VARCHAR(20) NOT NULL,  new_status VARCHAR(20) NOT NULL,  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;INSERT INTO work_orders VALUES(5001,'open',5,'2026-08-20 10:00:00'),(5002,'open',2,'2026-08-20 10:05:00');CREATE OR REPLACE ALGORITHM=MERGEDEFINER=CURRENT_USERSQL SECURITY INVOKERVIEW v_open_dispatch ASSELECT work_order_id,priority,updated_atFROM work_ordersWHERE status='open'WITH CASCADED CHECK OPTION;DELIMITER $$CREATE OR REPLACE PROCEDURE sp_close_work_order(IN p_id BIGINT)SQL SECURITY INVOKERMODIFIES SQL DATABEGIN  UPDATE work_orders  SET status='closed',updated_at=CURRENT_TIMESTAMP  WHERE work_order_id=p_id AND status='open';END$$CREATE OR REPLACE FUNCTION fn_priority_band(p_priority TINYINT)RETURNS VARCHAR(12)DETERMINISTICNO SQLSQL SECURITY INVOKERRETURN CASE WHEN p_priority >= 4 THEN 'high' ELSE 'normal' END$$CREATE OR REPLACE TRIGGER tr_wo_after_update_auditAFTER UPDATE ON work_ordersFOR EACH ROWBEGIN  IF NOT (OLD.status <=> NEW.status) THEN    INSERT INTO work_order_audit(work_order_id,old_status,new_status)    VALUES(NEW.work_order_id,OLD.status,NEW.status);  END IF;END$$DELIMITER ;CREATE OR REPLACEDEFINER=CURRENT_USEREVENT ev_expire_claimsON SCHEDULE EVERY 1 DAYDISABLEDO UPDATE work_orders   SET updated_at=CURRENT_TIMESTAMP WHERE status='open';

The event is intentionally DISABLEd: deployment tests should inspect and exercise dependencies manually before scheduled execution is permitted. Run this setup as a disposable lab administrator with the object-creation privileges required by your local Community Server.

2. Treat stored objects as a dependency graph

A database release has objects that depend on other objects and security identities. The exact order varies by schema, but a useful default is: establish controlled accounts/roles and base grants; deploy base tables; create routines that do not depend on later objects; create views; create triggers; create events disabled; run verification; then enable scheduled execution. If a view calls a function or an event calls a procedure, adjust the graph accordingly.

Dependency order is not cosmetic. MariaDB can accept some definitions that later fail at execution when a referenced security identity or privilege is unavailable. The release gate must include execution tests, not only successful DDL.

3. Capture a pre-change evidence packet

Before changing server-side logic, record both definition and effective metadata. SHOW CREATE is the closest representation of what MariaDB stored, including definer/security clauses that a hand-written migration may omit. Information Schema provides fleet-friendly inventory queries.

SELECT TABLE_NAME,ALGORITHM,IS_UPDATABLE,CHECK_OPTION,DEFINER,SECURITY_TYPEFROM information_schema.VIEWSWHERE TABLE_SCHEMA='servicehub_programmability_lab';SELECT ROUTINE_NAME,ROUTINE_TYPE,DATA_ACCESS,IS_DETERMINISTIC,SECURITY_TYPE,DEFINERFROM information_schema.ROUTINESWHERE ROUTINE_SCHEMA='servicehub_programmability_lab';SELECT TRIGGER_NAME,EVENT_OBJECT_TABLE,EVENT_MANIPULATION,ACTION_TIMING,ACTION_ORDER,DEFINERFROM information_schema.TRIGGERSWHERE TRIGGER_SCHEMA='servicehub_programmability_lab'ORDER BY EVENT_OBJECT_TABLE,EVENT_MANIPULATION,ACTION_TIMING,ACTION_ORDER;SELECT EVENT_NAME,DEFINER,TIME_ZONE,STATUS,LAST_EXECUTEDFROM information_schema.EVENTSWHERE EVENT_SCHEMA='servicehub_programmability_lab';

SHOW CREATE VIEW servicehub_programmability_lab.v_open_dispatch\GSHOW CREATE PROCEDURE servicehub_programmability_lab.sp_close_work_order\GSHOW CREATE FUNCTION servicehub_programmability_lab.fn_priority_band\GSHOW CREATE TRIGGER servicehub_programmability_lab.tr_wo_after_update_audit\GSHOW CREATE EVENT servicehub_programmability_lab.ev_expire_claims\G

In a real release, store these outputs as review evidence or compare them to canonical SQL in source control. Normalize only nondeterministic formatting that your diff tooling understands; do not blindly delete DEFINER clauses from dumps because the security identity is material behavior.

4. Versioned deployment scripts need explicit ownership and rollback

One practical layout keeps each migration immutable and pairs forward change with a tested rollback or compensating migration. DDL is not an application transaction that you can always reverse with ROLLBACK. A rollback script must recreate the prior object definition and security attributes deliberately.

-- deploy_release.sql-- Each SOURCE target is an immutable, reviewed file in the same release artifact.SOURCE 001_security_owner.sql;SOURCE 010_routines.sql;SOURCE 020_views.sql;SOURCE 030_triggers.sql;SOURCE 040_events_disabled.sql;SOURCE 090_verify.sql;SOURCE 100_enable.sql;

-- Run through a controlled deployment identity with CREATE USER/GRANT authority.CREATE USER IF NOT EXISTS 'svc_program_owner'@'localhost'  IDENTIFIED BY 'LabOnly-ChangeMe-42!';GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,TRIGGER,EVENT  ON servicehub_programmability_lab.*  TO 'svc_program_owner'@'localhost';SHOW GRANTS FOR 'svc_program_owner'@'localhost';

The remaining numbered files contain the reviewed definitions exercised in this chapter: routines first, then dependent views/triggers, then events created disabled, verification, and finally a small enablement step. The manifest is intentionally explicit so a failed file stops the release at a known boundary. In production, inject credentials through your deployment secret mechanism instead of committing password material; the visible password above is only for this disposable local account.

Each numbered file is a reviewed deployment unit, not an ad-hoc paste. Production account creation should normally go through the organization’s privileged identity-management path. The local fixture exists only to make dependency, ownership and verification behavior observable.

-- Example principle, not a generic automatic rollback:ALTER EVENT servicehub_programmability_lab.ev_expire_claims DISABLE;-- Recreate the prior trigger/routine/view definitions from the release artifact.-- Re-run behavior tests before re-enabling jobs.-- Only remove a definer account after inventory proves no object depends on it.SELECT 'views' AS object_type,TABLE_NAME AS object_name,DEFINERFROM information_schema.VIEWSWHERE TABLE_SCHEMA='servicehub_programmability_lab'UNION ALLSELECT 'routine',ROUTINE_NAME,DEFINERFROM information_schema.ROUTINESWHERE ROUTINE_SCHEMA='servicehub_programmability_lab'UNION ALLSELECT 'trigger',TRIGGER_NAME,DEFINERFROM information_schema.TRIGGERSWHERE TRIGGER_SCHEMA='servicehub_programmability_lab'UNION ALLSELECT 'event',EVENT_NAME,DEFINERFROM information_schema.EVENTSWHERE EVENT_SCHEMA='servicehub_programmability_lab';

5. Deliberately wrong: use a dump as an unreviewed deployment artifact

mariadb-dump is valuable for logical backup/migration. Triggers are included by default unless disabled; procedures/functions and events require their corresponding options. A dump can also carry definers and environment-specific assumptions. Applying it blindly to another environment can create missing-owner failures or accidentally preserve an over-privileged owner.

# shell / PowerShell concept: run from a trusted client hostmariadb-dump --single-transaction --routines --events --triggers   --no-data servicehub_programmability_lab > servicehub_objects.sql# Review the artifact before restore/deployment.# Search specifically for DEFINER=, SQL SECURITY, CREATE TRIGGER and CREATE EVENT.

The wrong repair is a global text replacement such as “replace every DEFINER with root.” That can create privilege escalation and erase intentional INVOKER boundaries. The safer process inventories each stored object's owner, maps source owners to approved target owners, provisions least-privilege accounts first, and runs behavior tests after restore.

A backup is also not a deployment manifest. Backup tooling answers “can I restore the database state?” A release system answers “which reviewed change should be applied, in what order, with what owner, and how do I prove/undo it?” You need both.

6. Test privileges from the caller's perspective

Testing only as an administrator hides privilege bugs. Create or use a disposable application account with the intended grants, then verify both allowed and denied operations. For SQL SECURITY INVOKER, the caller needs underlying access. For DEFINER objects, the caller typically needs access to the stored interface while the definer owns the underlying privileges required by the body. Exact privilege requirements depend on object type and statement.

SHOW GRANTS FOR CURRENT_USER;SHOW PRIVILEGES;-- For every dedicated definer account:SHOW GRANTS FOR 'svc_program_owner'@'localhost';-- Inventory definers again after deployment:SELECT TABLE_NAME AS object_name,DEFINER,SECURITY_TYPEFROM information_schema.VIEWSWHERE TABLE_SCHEMA='servicehub_programmability_lab';SELECT ROUTINE_NAME AS object_name,ROUTINE_TYPE,DEFINER,SECURITY_TYPEFROM information_schema.ROUTINESWHERE ROUTINE_SCHEMA='servicehub_programmability_lab';

Current MariaDB uses the SET USER privilege for assigning a different definer in relevant stored-object creation. Do not grant that capability broadly to application accounts. Keep deployment identities separate from runtime identities and record who is authorized to change stored security boundaries.

7. Decide whether the logic belongs in MariaDB or in the application

A practical boundary is often hybrid: table constraints for simple invariants, views for stable read interfaces, a few small procedures for atomic data-local workflows, triggers only for unavoidable writer-independent behavior, and external/application orchestration for distributed workflows. Do not move code into the database merely to reduce file count, and do not move every invariant into the application merely to avoid SQL/PSM.

8. Release acceptance matrix and production rollback triggers

Monitoring signals include routine/trigger statement latency, lock waits, event run gaps/failures, error-log entries, deployment drift in SHOW CREATE, missing definers, privilege changes and replica/Galera symptoms. A server-side feature is production-ready only when the operational team knows where those signals live.

9. Chapter checkpoint, cleanup, and bridge to security

1. Build an inventory of every Chapter 11 object: type, schema, name, definer, security mode, dependencies and owner.
2. Capture canonical SHOW CREATE definitions.
3. Run success and deliberate-failure tests as the intended caller, not only as admin.
4. Keep events disabled until verification passes and scheduler ownership is clear.
5. Test rollback by restoring the previous object definitions in a scratch schema.
6. Remove disposable users and schemas only after the dependency inventory is empty.

-- In the disposable lab only:DROP DATABASE IF EXISTS servicehub_programmability_lab;DROP USER IF EXISTS 'svc_program_owner'@'localhost';

Chapter 11 established the first major server-side code/security boundary in the MariaDB course. Chapter 12 now deepens that boundary into accounts, roles, authentication, authorization and hardening: who can connect, which privileges they inherit, and how definers and stored objects fit into least-privilege server governance.