Description:
With the general query log enabled, statements executed inside the body of a SQL SECURITY DEFINER stored program are handled inconsistently and are misattributed:
1. Function and trigger body statements are logged under the DEFINER, with the definer's host (e.g. %) — not the account that invoked the routine. Example user_host: def[def] @ % [%]. The % is a wildcard host pattern, not a host any client connected from, so the entry identifies neither the invoking client nor a real source host.
2. Stored-procedure body statements are not written to the general query log at all. sp_head::execute_procedure() disables the general log for the body, so only the top-level CALL is logged (under the calling client); the body sub-statement is silently absent.
The net effect is that the general query log — commonly relied on as a record of account activity — is neither complete (procedure bodies missing) nor consistently attributed (function/trigger bodies recorded under the definer/%) for stored-program activity.
Relevant background: section 8.2.23 "SQL-Based Account Activity Auditing" states that to identify the account that invoked an action, an application should use USER(), which returns the client's real user name and the host it connected from and never contains wildcards, whereas CURRENT_USER() (the definer, inside a SQL SECURITY DEFINER routine) may contain wildcards. The function/trigger body rows here carry the CURRENT_USER()-style definer value (including %). (Section 8.2.23 documents the USER()/CURRENT_USER() functions; it does not specify the general query log's user_host content, so the log's behavior for routine bodies appears undocumented.)
link: https://dev.mysql.com/doc/refman/8.4/en/account-activity-auditing.html
Relationship to bug #111335 (closed "Not a bug")
link: https://bugs.mysql.com/bug.php?id=111335
BUG #111335 ("SHOW FULL PROCESSLIST shows '%' in Host IP for a wildcard-definer procedure called by another user") was closed as expected behavior, on the grounds that SHOW PROCESSLIST shows the effective user at that instant, which during SQL SECURITY DEFINER execution is the definer. This report is not covered by that ruling:
- Different surface and purpose. BUG #111335 concerns SHOW PROCESSLIST — a live, point-in-time view of what a thread is currently doing, where reflecting the current execution context is reasonable. This report concerns the general query log — a persistent, historical record of which account executed which statements, used for activity auditing/troubleshooting. Attributing a recorded statement to the definer undermines that record's purpose.
- A new, un-adjudicated inconsistency. BUG #111335 said nothing about the general log omitting stored-procedure bodies while logging function/trigger bodies. That inconsistency (a core part of this report) is independent of the "effective user is shown" question and was never considered.
- BUG #111335 did not rule on the general query log. It made no determination about the log's user_host content.
So even accepting BUG 111335's rationale for a live monitoring view, it does not follow that a persistent audit record should attribute statements to the definer — and it does not explain why procedure bodies are dropped from that record while function/trigger bodies are kept.
How to repeat:
Preconditions: log_output = TABLE; log_bin_trust_function_creators = 1 (so a non-SUPER user can create the function/trigger while binary logging is enabled).
DROP DATABASE IF EXISTS bugdb;
DROP USER IF EXISTS 'def'@'%';
CREATE DATABASE bugdb; USE bugdb;
CREATE TABLE t_sp (c INT);
CREATE TABLE t_fn (c INT);
CREATE TABLE t_trg (c INT);
CREATE TABLE t_trg_log (c INT);
CREATE USER 'def'@'%' IDENTIFIED BY 'pass';
GRANT ALL ON bugdb.* TO 'def'@'%';
DELIMITER //
CREATE DEFINER='def'@'%' PROCEDURE sp_body() SQL SECURITY DEFINER
BEGIN INSERT INTO t_sp VALUES (1); END//
CREATE DEFINER='def'@'%' FUNCTION fn_body() RETURNS INT SQL SECURITY DEFINER DETERMINISTIC
BEGIN INSERT INTO t_fn VALUES (1); RETURN 1; END//
CREATE DEFINER='def'@'%' TRIGGER trg AFTER INSERT ON t_trg FOR EACH ROW
INSERT INTO t_trg_log VALUES (1)//
DELIMITER ;
-- Start a clean general-log window AFTER creating the routines:
SET GLOBAL general_log = OFF;
TRUNCATE TABLE mysql.general_log;
SET GLOBAL general_log = ON;
-- Exercise the routine bodies as the connecting client:
CALL sp_body(); -- procedure body -> INSERT INTO t_sp (expected: not logged)
SELECT fn_body(); -- function body -> INSERT INTO t_fn (expected: def @ %)
INSERT INTO t_trg VALUES (1); -- trigger body -> INSERT INTO t_trg_log (expected: def @ %)
SELECT user_host, CONVERT(argument USING utf8mb4) AS argument
FROM mysql.general_log
WHERE CONVERT(argument USING utf8mb4) LIKE 'INSERT INTO t%'
ORDER BY event_time;
Actual result (8.4.10)
+---------------------------------------+----------------------------------+
| user_host | argument |
+---------------------------------------+----------------------------------+
| def[def] @ % [%] | INSERT INTO t_fn VALUES (1) |
| user1[user1] @ [172.31.25.224] | INSERT INTO t_trg VALUES (1) |
| def[def] @ % [%] | INSERT INTO t_trg_log VALUES (1) |
+---------------------------------------+----------------------------------+
INSERT INTO t_fn (stored-function body) is attributed to def[def] @ % [%] (the definer, wildcard host).
INSERT INTO t_trg_log (trigger body) is attributed to def[def] @ % [%] (the definer, wildcard host).
INSERT INTO t_trg (top-level statement) is correctly attributed to the connecting client (user1 @ 172.31.25.224).
There is no INSERT INTO t_sp row — the stored-procedure body was not logged.
Expected result
At minimum, the general query log should handle stored-program body sub-statements consistently across routine types — today procedure bodies are omitted while function and trigger bodies are recorded — and the attribution it uses should be documented (section 8.2.23 specifies USER()/CURRENT_USER() but not the log's user_host for routine bodies).
Ideally, and in line with the auditing guidance in section 8.2.23, a persistent activity record should attribute a routine body to the invoking account (what USER() returns: real user name and connect-from host, no wildcards), matching how the top-level statement is already attributed. Recording it under the definer (host %) makes the entry identify neither the invoking client nor a real source host.
Suggested fix:
For stored-program body statements, source the general-log user_host from the invoking account (as USER() returns) rather than the swapped definer security context, and make body-statement inclusion consistent across procedures vs. functions/triggers.
