PROXY, DEFINER, SQL SECURITY, Stored Objects, and Privilege-Escalation Risks

Learning outcomes

Chapter 10 introduced definer and invoker execution. Here the question is explicitly security-focused: can a stored object become a controlled privilege boundary, and when can it accidentally become a privilege-escalation path? We will also place PROXY correctly: it is an authentication-plugin mapping capability, not a generic “assume another role” command.

Execution context: invoker identity is not always privilege identity

USER() identifies the connected client identity; CURRENT_USER() identifies the account whose privileges are currently effective. For ordinary SQL they often align, but definer-context stored objects can deliberately make them differ.

Views and routines can specify SQL SECURITY DEFINER or SQL SECURITY INVOKER. Definer context can be useful: the application receives EXECUTE on a narrow operation while direct table privileges remain absent. The danger is equally clear: a definer with broad global privileges can unintentionally turn a routine into a privilege-escalation interface.

Build a purpose-specific definer account

DROP USER IF EXISTS 'svc11_logic_owner'@'127.0.0.1';CREATE USER 'svc11_logic_owner'@'127.0.0.1'  IDENTIFIED BY 'LabOnly-LogicOwner!2026'  ACCOUNT LOCK;GRANT SELECT,UPDATEON servicehub_security_lab.work_ordersTO 'svc11_logic_owner'@'127.0.0.1';SHOW GRANTS FOR 'svc11_logic_owner'@'127.0.0.1';

The owner account is deliberately narrow and locked against normal login. The goal is not to create a “hidden root”; it is to create an object owner that has exactly the underlying privileges needed by one stored operation.

Create a definer procedure as a narrow API

-- Run as an administrator that is permitted to specify this DEFINER.DROP PROCEDURE IF EXISTS servicehub_security_lab.close_work_order;DELIMITER //CREATE DEFINER='svc11_logic_owner'@'127.0.0.1'PROCEDURE servicehub_security_lab.close_work_order(IN p_work_order_id BIGINT UNSIGNED)SQL SECURITY DEFINERMODIFIES SQL DATABEGIN  UPDATE servicehub_security_lab.work_orders  SET status='CLOSED'  WHERE work_order_id=p_work_order_id;  IF ROW_COUNT()=0 THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='work order not found';  END IF;END//DELIMITER ;REVOKE UPDATEON servicehub_security_lab.work_ordersFROM 'r_servicehub_writer';GRANT EXECUTEON PROCEDURE servicehub_security_lab.close_work_orderTO 'r_servicehub_writer';SHOW CREATE PROCEDURE servicehub_security_lab.close_work_order;

In a fresh application session, direct UPDATE should now be denied while CALL servicehub_security_lab.close_work_order(1001) succeeds. That is a deliberate privilege crossing: the caller gets one reviewed operation, not arbitrary table updates.

SELECT USER(),CURRENT_USER(),CURRENT_ROLE();-- Expected denial:UPDATE servicehub_security_lab.work_ordersSET private_note='should fail'WHERE work_order_id=1001;-- Expected success through the reviewed definer routine:CALL servicehub_security_lab.close_work_order(1001);SELECT work_order_id,statusFROM servicehub_security_lab.work_ordersWHERE work_order_id=1001;

Wrong design: powerful or orphaned definers

Two dangerous patterns deserve separate names:

• Broad definer: a routine/view is owned by a global administrator even though it needs one table privilege. A bug in the object can expose much more authority than intended.
• Orphaned definer: the stored object names an account that does not exist. This complicates execution and ownership, and can create adoption/escalation concerns if a matching account later appears.

MySQL 8.4 includes specific dynamic privileges such as SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER around creation/alteration of objects with arbitrary or nonexistent definers. These are administrator capabilities, not permissions to give migration code casually.

SELECT 'VIEW' AS object_type,TABLE_NAME AS object_name,DEFINER,SECURITY_TYPEFROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_SCHEMA='servicehub_security_lab'UNION ALLSELECT ROUTINE_TYPE,ROUTINE_NAME,DEFINER,SECURITY_TYPEFROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_SCHEMA='servicehub_security_lab'UNION ALLSELECT 'TRIGGER',TRIGGER_NAME,DEFINER,NULLFROM INFORMATION_SCHEMA.TRIGGERSWHERE TRIGGER_SCHEMA='servicehub_security_lab'UNION ALLSELECT 'EVENT',EVENT_NAME,DEFINER,NULLFROM INFORMATION_SCHEMA.EVENTSWHERE EVENT_SCHEMA='servicehub_security_lab';

Where PROXY actually fits

The PROXY privilege is not the same as a role and is not a normal application impersonation command. Proxying depends on an authentication plugin or server-supported mapping that authenticates an external identity and maps it to a different MySQL account whose privileges become effective. The proxy account must also have PROXY on the proxied account.

Common production examples use external-authentication plugins such as PAM or LDAP; some of those are Enterprise Edition capabilities. The mandatory Community Server lab therefore does not fake a proxy login using caching_sha2_password. Instead, learn the metadata and security model locally, then reproduce actual proxy mapping only in an environment whose authentication plugin documents support.

SELECT User,Host,Proxied_user,Proxied_host,With_grantFROM mysql.proxies_privORDER BY User,Host,Proxied_user,Proxied_host;SHOW GRANTS FOR CURRENT_USER;

Production judgment

Hands-on privilege-boundary test

1. Create and lock the narrow logic-owner account.
2. Create the definer procedure and replace direct application UPDATE with EXECUTE.
3. From the application session, prove direct UPDATE is denied and the procedure succeeds.
4. Inspect the routine definer/security type and the owner's grants.
5. Inventory all definers in the lab schema and verify none point to a missing account.
6. Inspect mysql.proxies_priv but do not configure plugin-dependent PROXY mapping unless your environment explicitly supports it.

Summary and bridge to Lesson 5

You have now crossed privilege boundaries deliberately: roles compose privileges within an account; definer objects can expose narrowly elevated operations; PROXY belongs to authentication identity mapping. Lesson 5 turns the whole chapter into an auditable access model with actor/action matrices, fresh-session tests, rotation, revocation, and offboarding procedures.

Authoritative references

• MySQL 8.4 — Stored Object Access Control
• MySQL 8.4 — Privileges Provided by MySQL
• MySQL 8.4 — Pluggable Authentication
• MySQL 8.4 — GRANT / PROXY syntax
• MySQL 8.4 — PAM Authentication (Enterprise)