Chapter 10 · Stored Programs, Views, Triggers, Events, and Server-Side Logic
Views, Updatability, SQL SECURITY, Definer Context, and Dependency Management
Use MySQL views as deliberate read/write contracts: understand updatability, processing algorithms, DEFINER versus INVOKER security, metadata, privileges, and how schema evolution can invalidate hidden dependencies.
Learning outcomes
Chapter 09 treated query plans as observable engineering evidence. Chapter 10 changes the kind of object you are reasoning about: logic can now live inside the server. A view looks like a table to its consumer, but its query text, processing algorithm, definer, security mode, and dependencies can change who is allowed to see what and whether writes are even legal. The first lesson makes those hidden properties visible.
Define a MySQL view, distinguish its stored definition from materialized data, and inspect its metadata with SHOW CREATE VIEW and INFORMATION_SCHEMA.VIEWS.
Predict when a simple view is updatable and recognize common constructs that make a view non-updatable.
Explain ALGORITHM=MERGE, TEMPTABLE, and UNDEFINED only as documented processing choices rather than performance slogans.
Demonstrate SQL SECURITY DEFINER versus INVOKER with explicit positive and negative authorization tests.
Identify dependency and definer-lifecycle risks, then deploy and clean up view objects without granting application accounts unnecessary base-table access.
A realistic problem: “the app can read data it cannot select directly”
ServiceHub wants a small read contract for dispatchers: open work orders with the site code and summary. The application account must not receive unrestricted SELECT on the underlying tables. A developer proposes a view and says, “a view is just a saved SELECT, so security is the same either way.” That sentence is incomplete in MySQL.
A view is a named database object whose definition is a query. MySQL stores the definition and exposes the view as a virtual table. It does not automatically store a second copy of the result rows. A definer is the account recorded on a stored object. SQL SECURITY determines whether object access to underlying data is checked using the definer's privileges or the invoker's privileges. In MySQL, DEFINER is the default for views.
A view can be an intentional least-privilege boundary, but only if its security context, selected columns, predicates, definer account, and grants are reviewed together. Hiding a table name is not the same as enforcing an authorization policy.
Build the disposable ServiceHub logic lab
Run the following as a local MySQL administrator. The chapter intentionally creates and later drops a dedicated schema and two disposable accounts. Do not run these cleanup statements on a shared production instance.
-- CHAPTER 10 USES A DISPOSABLE SCHEMA. Run only on your local lab instance.DROP DATABASE IF EXISTS servicehub_logic_lab;CREATE DATABASE servicehub_logic_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;USE servicehub_logic_lab;CREATE TABLE sites ( site_id INT UNSIGNED NOT NULL AUTO_INCREMENT, site_code VARCHAR(20) NOT NULL, site_name VARCHAR(100) NOT NULL, region_code VARCHAR(20) NOT NULL, active BOOLEAN NOT NULL DEFAULT TRUE, PRIMARY KEY (site_id), UNIQUE KEY uq_sites_code (site_code)) ENGINE=InnoDB;CREATE TABLE work_orders ( work_order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, site_id INT UNSIGNED NOT NULL, status VARCHAR(20) NOT NULL, priority TINYINT UNSIGNED NOT NULL DEFAULT 2, summary VARCHAR(180) NOT NULL, opened_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), closed_at DATETIME(6) NULL, updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (work_order_id), KEY ix_wo_site_status (site_id,status,work_order_id), CONSTRAINT fk_wo_site FOREIGN KEY (site_id) REFERENCES sites(site_id), CONSTRAINT ck_wo_priority CHECK (priority BETWEEN 1 AND 4)) ENGINE=InnoDB;CREATE TABLE work_order_audit ( audit_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, work_order_id BIGINT UNSIGNED NOT NULL, old_status VARCHAR(20) NULL, new_status VARCHAR(20) NULL, change_source VARCHAR(30) NOT NULL, changed_by VARCHAR(288) NOT NULL, changed_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (audit_id), KEY ix_audit_work_order (work_order_id,changed_at)) ENGINE=InnoDB;CREATE TABLE maintenance_runs ( run_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, job_name VARCHAR(64) NOT NULL, executed_at DATETIME(6) NOT NULL, execution_user VARCHAR(288) NOT NULL, note VARCHAR(255) NOT NULL, PRIMARY KEY (run_id)) ENGINE=InnoDB;INSERT INTO sites(site_code,site_name,region_code) VALUES ('BAKU-N','Baku North Plant','AZ-BAKU'), ('BAKU-H','Baku Harbor Workshop','AZ-BAKU'), ('GANJA-1','Ganja Service Center','AZ-GANJA');INSERT INTO work_orders(site_id,status,priority,summary,opened_at) VALUES (1,'open',1,'Pump P-101 vibration investigation','2026-08-14 08:30:00'), (1,'waiting',2,'Motor M-014 bearing replacement','2026-08-14 10:15:00'), (2,'open',3,'Crane C-003 inspection','2026-08-15 09:00:00'), (3,'closed',2,'Compressor filter replacement','2026-08-12 07:45:00');SELECT VERSION() AS server_version, @@version_comment AS version_comment, DATABASE() AS current_schema, @@session.sql_mode AS session_sql_mode, @@session.time_zone AS session_time_zone;-- Run as a disposable local administrator account.DROP USER IF EXISTS 'logic_owner'@'127.0.0.1';DROP USER IF EXISTS 'logic_app'@'127.0.0.1';CREATE USER 'logic_owner'@'127.0.0.1' IDENTIFIED BY '<Disposable-Lab-Owner-2026!>';CREATE USER 'logic_app'@'127.0.0.1' IDENTIFIED BY '<Disposable-Lab-App-2026!>';GRANT SELECT, INSERT, UPDATE, DELETE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, TRIGGER, EVENTON servicehub_logic_lab.* TO 'logic_owner'@'127.0.0.1';-- logic_app intentionally starts with no base-table privileges.SHOW GRANTS FOR 'logic_owner'@'127.0.0.1';SHOW GRANTS FOR 'logic_app'@'127.0.0.1';The lab separates logic_owner, which creates stored objects and has the required schema privileges, from logic_app, which begins with no direct table privileges. Connect with TCP to make host matching predictable:
mysql -h 127.0.0.1 -u logic_owner -pEnter the disposable password interactively. Do not put real passwords on a shell command line.
Create and inspect a simple updatable view
First create a one-table view that preserves a one-to-one mapping between view rows and base-table rows. Use WITH CHECK OPTION so writes performed through the view cannot move a row outside the view predicate.
USE servicehub_logic_lab;CREATE SQL SECURITY DEFINER VIEW v_open_work_orders ASSELECT work_order_id,site_id,status,priority,summary,opened_at,closed_atFROM work_ordersWHERE status IN ('open','waiting')WITH CASCADED CHECK OPTION;SHOW CREATE VIEW v_open_work_orders\GSELECT TABLE_SCHEMA,TABLE_NAME,CHECK_OPTION,IS_UPDATABLE,DEFINER,SECURITY_TYPEFROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_SCHEMA='servicehub_logic_lab' AND TABLE_NAME='v_open_work_orders';Expected evidence: IS_UPDATABLE should be YES for this simple view, and SECURITY_TYPE should be DEFINER. The metadata proves how MySQL recorded the object; it does not by itself prove that a particular application account can use it.
UPDATE v_open_work_ordersSET priority=4WHERE work_order_id=1;-- This attempts to move a visible row outside the view predicate.UPDATE v_open_work_ordersSET status='closed'WHERE work_order_id=1;SHOW WARNINGS;SELECT work_order_id,status,priorityFROM v_open_work_ordersORDER BY work_order_id;The first update is valid. The second should be rejected by the view check option because the changed row would no longer satisfy status IN ('open','waiting'). This is a view-level write constraint, not a replacement for base-table constraints or application authorization.
Updatable versus non-updatable views
MySQL can update a view only when it can map each changed view row back to one underlying row under the documented rules. Aggregation and grouping are a common boundary because a result row such as “site 1 has two open orders” is not one base row that can simply be updated.
CREATE VIEW v_status_counts ASSELECT site_id,status,COUNT(*) AS order_countFROM work_ordersGROUP BY site_id,status;SELECT TABLE_NAME,IS_UPDATABLE,DEFINER,SECURITY_TYPEFROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_SCHEMA='servicehub_logic_lab'ORDER BY TABLE_NAME;UPDATE v_status_countsSET order_count=99WHERE site_id=1 AND status='open';Expected result: metadata reports the aggregate view as non-updatable, and the UPDATE fails. The failure is desirable: MySQL refuses to invent a meaning for writing an aggregate result back into multiple underlying rows.
| View property | What it means | What it does not mean |
|---|---|---|
| IS_UPDATABLE=YES | The view definition passes MySQL updatability rules | Every INSERT/UPDATE is guaranteed to succeed; constraints and privileges can still reject it |
| SQL SECURITY DEFINER | Underlying privilege checks use the recorded definer context | The caller gains the definer account or can access arbitrary tables |
| SQL SECURITY INVOKER | Underlying privilege checks use the invoking account | The view becomes automatically safer; the invoker may now need direct underlying privileges |
| ALGORITHM=MERGE | MySQL attempts to merge eligible view text into the outer statement | The view is physically materialized or guaranteed faster |
| ALGORITHM=TEMPTABLE | The view result is processed through a temporary-table approach | The result is a persistent materialized view |
| ALGORITHM=UNDEFINED | MySQL chooses an applicable processing algorithm | The optimizer ignores the view definition |
Positive and negative authorization tests
Return to the administrator session and grant only SELECT on the definer-security view to the application account.
GRANT SELECT ON servicehub_logic_lab.v_open_work_ordersTO 'logic_app'@'127.0.0.1';SHOW GRANTS FOR 'logic_app'@'127.0.0.1';Now connect as the application account and run both tests:
SELECT USER() AS authenticated_identity, CURRENT_USER() AS privilege_identity, CONNECTION_ID() AS connection_id;SHOW SESSION STATUS LIKE 'Ssl_cipher';SELECT work_order_id,status,summaryFROM servicehub_logic_lab.v_open_work_ordersORDER BY work_order_id;-- Negative test: no direct base-table SELECT was granted.SELECT work_order_id,statusFROM servicehub_logic_lab.work_orders;The view query should succeed while direct table access should be denied. An empty Ssl_cipher on a loopback-only lab means the session is not using TLS; it does not invalidate the privilege test. Production transport policy is a separate security requirement covered later in the course.
Next create an invoker-security twin as logic_owner, then let the administrator grant it to the app:
-- Run as logic_owner.CREATE SQL SECURITY INVOKER VIEW v_open_work_orders_invoker ASSELECT work_order_id,site_id,status,priority,summaryFROM work_ordersWHERE status IN ('open','waiting');-- Run as admin.GRANT SELECT ON servicehub_logic_lab.v_open_work_orders_invokerTO 'logic_app'@'127.0.0.1';-- Run as logic_app. This should fail because the app still lacks base-table SELECT.SELECT * FROM servicehub_logic_lab.v_open_work_orders_invoker;This comparison demonstrates the security boundary directly. Do not “fix” the failure by granting SELECT ON servicehub_logic_lab.*; that would erase the least-privilege design being tested.
The wrong deployment pattern: orphaned or accidental definers
A common deployment mistake is exporting a view from one environment with a human administrator as its definer and restoring it elsewhere. Modern MySQL tightens definer handling, but the core operational lesson remains: definers are part of the object contract. Deleting or renaming the account, or restoring an object with an inappropriate definer, can break execution or create a privilege boundary nobody intended.
Use a dedicated, non-human object-owner account; create objects through a controlled deployment identity; review SQL SECURITY explicitly; grant callers only the view/routine privileges they need; and include DEFINER/SECURITY_TYPE in schema-drift review. Do not treat definers as harmless dump-file decoration.
Dependency management: views can become invalid
MySQL freezes a view definition at creation. Adding a new base-table column does not silently expand a SELECT * view definition, and removing a referenced column can invalidate dependent views. Demonstrate this only on a disposable copy:
CREATE TABLE wo_dependency_demo LIKE work_orders;INSERT INTO wo_dependency_demo SELECT * FROM work_orders;CREATE VIEW v_dependency_demo ASSELECT work_order_id,summary,status FROM wo_dependency_demo;-- Migrate the base table in two explicit steps. The stored view definition-- still references the old column name and therefore becomes invalid.ALTER TABLE wo_dependency_demo ADD COLUMN description VARCHAR(180) NULL;UPDATE wo_dependency_demo SET description=summary;ALTER TABLE wo_dependency_demo DROP COLUMN summary;-- Expected to fail because the stored view definition references summary.SELECT * FROM v_dependency_demo;CHECK TABLE v_dependency_demo;CREATE OR REPLACE VIEW v_dependency_demo ASSELECT work_order_id,description AS summary,statusFROM wo_dependency_demo;SELECT * FROM v_dependency_demo ORDER BY work_order_id;DROP VIEW v_dependency_demo;DROP TABLE wo_dependency_demo;The repair is not “restart MySQL.” The dependency contract changed, so the view definition must change in the same versioned migration. This is why view definitions belong in source control next to schema changes.
Hands-on lab acceptance checklist
SHOW CREATE VIEWandINFORMATION_SCHEMA.VIEWSagree on definer/security metadata.- The simple view is updatable; the grouped view is not.
WITH CHECK OPTIONrejects a write that would violate the view predicate.logic_appcan read the definer-security view but cannot directly readwork_orders.- The invoker-security view fails for
logic_appuntil the underlying privilege is intentionally granted. - A disposable broken dependency is diagnosed and repaired by replacing the view definition.
Knowledge check
- Does a MySQL view store a permanent copy of its result rows?
- What is the default SQL SECURITY mode for a MySQL view?
- Why can a grouped COUNT view not normally be updated?
- What does WITH CHECK OPTION protect?
- Why record the DEFINER in schema review?
Reveal answers
- No. A normal view stores a query definition and presents a virtual table; TEMPTABLE processing is not a persistent materialized-view feature.
- DEFINER.
- A grouped result row does not have the required one-to-one mapping to one underlying base-table row.
- It rejects writes through an updatable view that would produce rows outside the view predicate, subject to LOCAL/CASCADED scope.
- The definer controls privilege context for definer-security objects and can become an availability or privilege-escalation risk if unmanaged.
Production judgment and next step
Views are strongest when they expose a stable relational contract, centralize a safe projection/predicate, or create a deliberate privilege boundary. They are weaker when nested deeply, used to hide expensive queries, or left with unmanaged definers and undocumented dependencies. Monitor view query plans like ordinary SQL, test authorization with real low-privilege accounts, and migrate view definitions together with base tables.
Lesson 2 moves from stored queries to stored programs: procedures and functions with parameters, control flow, error handlers, and diagnostics.