Views, Updatability, ALGORITHM, SQL SECURITY, and Definer Risks

Learning outcomes

ServiceHub has a dispatch application that should see open work orders but not customer billing notes. The first instinct is to duplicate a filtered query in every API endpoint. That creates drift: one endpoint forgets the filter, another exposes a new column, and an emergency fix grants the application direct access to the base table. A view is a named query stored in MariaDB metadata. It can give applications a stable relational interface, but it is not automatically a security barrier, an index, or a materialized result. Its behavior depends on the view definition, the optimizer, its ALGORITHM, whether it is updatable, and whether execution uses the invoker's or definer's privileges.

1. Start with a disposable ServiceHub view lab

The lab separates base-table ownership from the interface an application consumes. Run it only in the disposable schema below. The tables use InnoDB so later trigger and transaction examples have transactional semantics.

DROP DATABASE IF EXISTS servicehub_programmability_lab;CREATE DATABASE servicehub_programmability_lab;USE servicehub_programmability_lab;CREATE TABLE customers (  customer_id BIGINT PRIMARY KEY,  display_name VARCHAR(120) NOT NULL,  region_code CHAR(3) NOT NULL,  billing_note VARCHAR(255) NULL) ENGINE=InnoDB;CREATE TABLE work_orders (  work_order_id BIGINT PRIMARY KEY,  customer_id BIGINT NOT NULL,  status ENUM('open','assigned','closed','cancelled') NOT NULL,  priority TINYINT NOT NULL,  assigned_team VARCHAR(40) NULL,  total_cents INT NOT NULL,  opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  CONSTRAINT fk_wo_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)) ENGINE=InnoDB;INSERT INTO customers VALUES(1,'Northwind Clinic','BAK','contract renewal in Q4'),(2,'Caspian Foods','BAK','credit review pending'),(3,'Atlas Lab','TBZ','annual invoice only');INSERT INTO work_orders(work_order_id,customer_id,status,priority,assigned_team,total_cents) VALUES(1001,1,'open',5,'alpha',18000),(1002,2,'assigned',3,'alpha',7500),(1003,3,'open',2,'beta',4200);

At this point there is no abstraction: anyone with SELECT on customers can request billing_note. A view can expose only the columns and rows required by the dispatch workflow, while base-table privileges remain a separate decision.

2. ALGORITHM describes how MariaDB may process the view

For many simple views, MariaDB can merge the view definition into the outer statement. Conceptually, the optimizer substitutes the view query and optimizes the combined statement. ALGORITHM=MERGE requests that behavior where the view definition is mergeable. ALGORITHM=TEMPTABLE tells MariaDB to materialize the view result into an internal temporary table before the outer statement uses it. ALGORITHM=UNDEFINED leaves the choice to the server and is the default when no algorithm is stated.

CREATE OR REPLACE ALGORITHM=MERGEDEFINER=CURRENT_USERSQL SECURITY INVOKERVIEW v_open_dispatch ASSELECT work_order_id, customer_id, priority, assigned_team, opened_atFROM work_ordersWHERE status='open'WITH CASCADED CHECK OPTION;SHOW CREATE VIEW v_open_dispatch\GSELECT TABLE_NAME,ALGORITHM,IS_UPDATABLE,CHECK_OPTION,DEFINER,SECURITY_TYPEFROM information_schema.VIEWSWHERE TABLE_SCHEMA='servicehub_programmability_lab'  AND TABLE_NAME='v_open_dispatch';EXPLAINSELECT * FROM v_open_dispatchWHERE priority >= 3;

The expected metadata shape is IS_UPDATABLE='YES', a non-NONE check option, and security type INVOKER. EXPLAIN should show access to the underlying table rather than prove that a permanent copy of the view exists. The exact chosen access path depends on indexes and statistics; the evidence proves how this server planned this statement, not that every query against the view will use the same path.

3. Updatability is a property of the definition, not of the word VIEW

A simple single-table view can often be updated because MariaDB can map a view row back to one base-table row. Definitions with grouping, aggregates, DISTINCT, set operations and other non-invertible transformations are not updatable. ALGORITHM=TEMPTABLE also makes a view non-updatable. Treat INFORMATION_SCHEMA.VIEWS.IS_UPDATABLE as useful evidence, then verify the exact write you intend to support.

SELECT * FROM v_open_dispatch ORDER BY work_order_id;UPDATE v_open_dispatchSET assigned_team='dispatch-east'WHERE work_order_id=1001;-- This would make the row stop satisfying status='open'.-- The view does not expose status, so demonstrate the boundary with a second view:CREATE OR REPLACE VIEW v_open_edit ASSELECT work_order_id,status,priority,assigned_teamFROM work_ordersWHERE status='open'WITH CASCADED CHECK OPTION;UPDATE v_open_editSET status='closed'WHERE work_order_id=1001;SELECT work_order_id,status,assigned_teamFROM work_ordersWHERE work_order_id=1001;

The first update is legal because the row remains inside the view predicate. The second update should be rejected by the check option because the resulting row would no longer satisfy status='open'. The base row should therefore remain open. WITH CHECK OPTION protects the predicate of an updatable view; it is not a replacement for table constraints, authorization, or application validation.

CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v_region_open_counts ASSELECT c.region_code,COUNT(*) AS open_count,SUM(w.total_cents) AS open_value_centsFROM work_orders wJOIN customers c ON c.customer_id=w.customer_idWHERE w.status='open'GROUP BY c.region_code;SELECT TABLE_NAME,ALGORITHM,IS_UPDATABLEFROM information_schema.VIEWSWHERE TABLE_SCHEMA='servicehub_programmability_lab'  AND TABLE_NAME='v_region_open_counts';UPDATE v_region_open_countsSET open_count=0WHERE region_code='BAK';

The final statement should fail because an aggregate row does not map to one writable base row. That failure is desirable: the reporting view expresses a derived result, not an update API.

4. SQL SECURITY decides whose privileges are checked

SQL SECURITY INVOKER evaluates the view using the invoking account's privileges. It preserves the caller's privilege boundary and is usually easier to reason about when the caller already has the required base-table access. SQL SECURITY DEFINER evaluates using the privileges of the account recorded as the view's DEFINER. That can intentionally expose a narrow interface over data the caller cannot query directly—but it also creates a privilege-elevation boundary that must be reviewed like application code.

A safe definer is not a human administrator who may leave the organization. Prefer a dedicated service-owned account with the minimum base-object privileges required by the view, managed as infrastructure. If direct login is unnecessary, evaluate an account-lock policy on the exact target version and test that the stored object still behaves as intended.

5. Deliberately wrong: restore a DEFINER that does not exist

A logical dump or copied SHOW CREATE VIEW statement can preserve a definer name from another environment. The object can then exist in metadata but fail when it is invoked because MariaDB cannot resolve the recorded security identity. This is a deployment problem, not an optimizer problem.

-- Run as a lab administrator with CREATE USER / SET USER capability.CREATE USER IF NOT EXISTS 'svc_view_owner'@'localhost' IDENTIFIED BY 'temporary-lab-only';GRANT SELECT ON servicehub_programmability_lab.work_orders TO 'svc_view_owner'@'localhost';CREATE OR REPLACEDEFINER='svc_view_owner'@'localhost'SQL SECURITY DEFINERVIEW v_safe_open_ids ASSELECT work_order_id,priorityFROM servicehub_programmability_lab.work_ordersWHERE status='open';SHOW CREATE VIEW v_safe_open_ids\G-- Deliberate failure injection: never do this to an unreviewed production definer.DROP USER 'svc_view_owner'@'localhost';SELECT * FROM v_safe_open_ids;SELECT TABLE_NAME,DEFINER,SECURITY_TYPEFROM information_schema.VIEWSWHERE TABLE_SCHEMA='servicehub_programmability_lab';

The expected failure is MariaDB error 1449 (the user specified as a definer does not exist) when the orphaned definer is required. The metadata query identifies the stale owner. Repair the deployment by recreating the intended least-privilege service account and grants, or by recreating the view with SQL SECURITY INVOKER when privilege elevation is not actually required. Do not “fix” the symptom by assigning a powerful administrator as definer.

CREATE USER 'svc_view_owner'@'localhost' IDENTIFIED BY 'temporary-lab-only';GRANT SELECT ON servicehub_programmability_lab.work_orders TO 'svc_view_owner'@'localhost';CREATE OR REPLACEDEFINER='svc_view_owner'@'localhost'SQL SECURITY DEFINERVIEW v_safe_open_ids ASSELECT work_order_id,priorityFROM servicehub_programmability_lab.work_ordersWHERE status='open';SELECT * FROM v_safe_open_ids;SHOW GRANTS FOR 'svc_view_owner'@'localhost';

Successful selection proves that the definer now exists and has sufficient privileges for this definition. It does not prove the account is least-privilege across the whole server; that requires reviewing SHOW GRANTS and the deployment manifest.

6. Production judgment, verification, and cleanup

Use views when they provide a stable relational contract, simplify repeated joins/filters, or deliberately expose a reviewed subset of data. Do not use them to hide unbounded query complexity, assume materialization, or bypass an application's authorization model. Monitor query plans and latency through the view exactly as you would for the underlying SQL. For definer-security views, monitor deployment failures, stale definers, privilege drift and access patterns.

1. Verify SHOW CREATE VIEW for every lab view.
2. Query INFORMATION_SCHEMA.VIEWS for algorithm, updatability, check option, definer and security type.
3. Confirm the failed CHECK OPTION write did not change the base row.
4. Confirm the orphaned-definer experiment failed for the expected reason and was repaired.
5. When finished, remove the service owner and drop the disposable database.

DROP VIEW IF EXISTS servicehub_programmability_lab.v_safe_open_ids;DROP USER IF EXISTS 'svc_view_owner'@'localhost';DROP DATABASE IF EXISTS servicehub_programmability_lab;

The next lesson keeps the same security/ownership discipline but moves from named queries to executable server-side programs: procedures, functions, handlers, diagnostics and cursors.