Definers, SQL SECURITY, Stored Objects, Views, and Privilege-Escalation Prevention

Learning outcomes

Chapter 11 introduced MariaDB server-side code. Security changes the question from “does this view/procedure/trigger/event run?” to “under whose privileges does it run, and can an untrusted caller exploit that boundary?” A DEFINER is the MariaDB account recorded as the owner/security context of a stored object. Views and routines can use SQL SECURITY DEFINER or INVOKER; triggers and events have definers but do not expose the same caller-selectable SQL SECURITY switch.

1. Prepare base data and two security identities

DROP DATABASE IF EXISTS servicehub_definer_lab;CREATE DATABASE servicehub_definer_lab;USE servicehub_definer_lab;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY,  customer_name VARCHAR(120) NOT NULL,  status VARCHAR(16) NOT NULL,  internal_note VARCHAR(255) NOT NULL) ENGINE=InnoDB;INSERT INTO tickets VALUES(4001,'Northwind Clinic','open','VIP escalation path'),(4002,'Atlas Lab','assigned','security review pending');DROP USER IF EXISTS 'svc_object_owner'@'localhost';DROP USER IF EXISTS 'app_public'@'localhost';CREATE USER 'svc_object_owner'@'localhost' IDENTIFIED BY 'LabOnly-Owner-42!';CREATE USER 'app_public'@'localhost' IDENTIFIED BY 'LabOnly-App-42!';GRANT SELECT ON servicehub_definer_lab.tickets TO 'svc_object_owner'@'localhost';

The caller deliberately receives no direct SELECT on the base table. We will expose a safe projection through a definer-security view, then verify that direct access remains denied.

2. SQL SECURITY INVOKER preserves the caller’s privilege boundary

CREATE OR REPLACEDEFINER=CURRENT_USERSQL SECURITY INVOKERVIEW v_ticket_public_invoker ASSELECT ticket_id,customer_name,statusFROM tickets;GRANT SELECT ON servicehub_definer_lab.v_ticket_public_invokerTO 'app_public'@'localhost';SHOW CREATE VIEW v_ticket_public_invoker\G

When app_public selects this view, MariaDB checks the invoker’s ability to reach the underlying objects. Because the caller lacks base-table SELECT, the query should fail. That is expected and often preferable when a view is only a reusable query interface rather than a privilege bridge.

3. SQL SECURITY DEFINER is an intentional privilege bridge

CREATE OR REPLACEDEFINER='svc_object_owner'@'localhost'SQL SECURITY DEFINERVIEW v_ticket_public ASSELECT ticket_id,customer_name,statusFROM tickets;GRANT SELECT ON servicehub_definer_lab.v_ticket_publicTO 'app_public'@'localhost';SHOW CREATE VIEW v_ticket_public\GSHOW GRANTS FOR 'svc_object_owner'@'localhost';

Now the caller can read the three exposed columns through the view even without direct access to tickets, because the view runs with the definer’s underlying SELECT. This is safe only if the interface is narrow and the definer is least-privilege. The caller still must not be able to query internal_note directly.

SELECT * FROM servicehub_definer_lab.v_ticket_public;-- Expected denial:SELECT internal_note FROM servicehub_definer_lab.tickets;SELECT USER(),CURRENT_USER(),CURRENT_ROLE();

Successful view access plus failed base-table access demonstrates the intended bridge. It does not prove every query through every stored object is safe; review object definitions for injection, dynamic SQL, data leakage and unintended write paths.

4. Routines, triggers and events have different execution surfaces

SELECT TABLE_NAME,DEFINER,SECURITY_TYPEFROM information_schema.VIEWSWHERE TABLE_SCHEMA='servicehub_definer_lab';SELECT ROUTINE_NAME,ROUTINE_TYPE,DEFINER,SECURITY_TYPEFROM information_schema.ROUTINESWHERE ROUTINE_SCHEMA='servicehub_definer_lab';SELECT TRIGGER_NAME,DEFINER,ACTION_TIMING,EVENT_MANIPULATIONFROM information_schema.TRIGGERSWHERE TRIGGER_SCHEMA='servicehub_definer_lab';SELECT EVENT_NAME,DEFINER,STATUSFROM information_schema.EVENTSWHERE EVENT_SCHEMA='servicehub_definer_lab';

Treat these inventories as release evidence after logical restore, migration and account renaming. A stored object can survive in metadata while its owner or privilege assumptions no longer hold.

5. Deliberately wrong: “fix” all definers by making root the owner

A dump restored to a new environment may contain missing definers. A global text replacement that changes every DEFINER to a powerful administrator can make errors disappear while introducing privilege escalation. The safe response is to map each stored object to an approved service owner or choose INVOKER where elevation is unnecessary.

-- Administrative lab session:DROP USER 'svc_object_owner'@'localhost';-- This view still records the old definer.SELECT TABLE_NAME,DEFINER,SECURITY_TYPEFROM information_schema.VIEWSWHERE TABLE_SCHEMA='servicehub_definer_lab';-- Invoking v_ticket_public should fail because its definer no longer exists.SELECT * FROM servicehub_definer_lab.v_ticket_public;

The expected failure identifies a security-identity deployment error, not corrupted table data. Repair by recreating the designated least-privilege owner and its exact grants, then recreate/verify the object if required by your deployment process.

CREATE USER 'svc_object_owner'@'localhost' IDENTIFIED BY 'LabOnly-Owner-Rotated-43!';GRANT SELECT ON servicehub_definer_lab.tickets TO 'svc_object_owner'@'localhost';SHOW GRANTS FOR 'svc_object_owner'@'localhost';SELECT * FROM servicehub_definer_lab.v_ticket_public;

6. Privilege-escalation review checklist

1. Inventory every view, routine, trigger and event with its definer.
2. Capture SHOW CREATE and review security mode, body and object dependencies.
3. For each definer, capture SHOW GRANTS and remove privileges not required by the object.
4. Test stored interfaces as the intended low-privilege caller.
5. Run direct-access denied tests against the underlying tables.
6. After restore/migration, verify every definer exists before enabling application traffic or scheduled events.

7. Production judgment and cleanup

Prefer INVOKER when the caller can safely hold underlying privileges and the object is primarily an abstraction. Use DEFINER only when you intentionally need a narrow privilege bridge. The definer should be a dedicated, documented service identity—not a departing employee and not a blanket administrator.

DROP DATABASE IF EXISTS servicehub_definer_lab;DROP USER IF EXISTS 'app_public'@'localhost';DROP USER IF EXISTS 'svc_object_owner'@'localhost';

Lesson 4 moves from SQL authorization to the network path: encrypting connections and verifying who is on the other end.