MySQL Processlist Seems to Show Incorrect User When Running a Custom Function

I am on MySQL 8.0.28 and I have this function:

DELIMITER $

CREATE FUNCTION DelayedHello()
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
  DO SLEEP(3);
  RETURN CONCAT('Hello ', 'World!');
END$

DELIMITER ;

And this was created by the root user.

But when a user called test runs this function, in the show processlist, this is what I see. So, it looks like the user root is running it.

127.0.0.1> select * from information_schema.processlist ;
+-------+-----------------+------------------+-----------+---------+---------+------------------------+----------------------------------------------+
| ID    | USER            | HOST             | DB        | COMMAND | TIME    | STATE                  | INFO                                         |
+-------+-----------------+------------------+-----------+---------+---------+------------------------+----------------------------------------------+
| 48547 | root            | %:40814          | helpscout | Query   | 1       | User sleep             | DO SLEEP(3)                                  |

I tried to query the information_schema.processlist to see the user but it returned the same info that the user root seems to be executing this. So, is there a way to show in the processlist that it was the test who is running this? If not, how do I find out who actually called this function?

This is a combination of the DEFINER, the INVOKER, and the default behavior around both of these concepts when the function is created or altered.

When you create a function, it captures the CURRENT_USER() as the DEFINER unless you explicitly state one. In your example, this defaulted to the root user.

When your function is called, a security check is performed to provide context for execution. Since your function had a DEFINER of root, querying both SHOW PROCESSLIST and SELECT * FROM information_schema.processlist would return the calling user as root even though test was calling it.

If you alter the function to what I have below, it should capture the CURRENT_USER() (e.g. root or the creator) as the DEFINER because of the DEFINER=CURRENT_USER() syntax.

When the function below is called, querying SHOW PROCESSLIST or SELECT * FROM information_schema.processlist; would show the function as being executed by the INVOKER - in this case test because the SQL SECURITY INVOKER syntax is specified.

USE YourDatabase;
DROP FUNCTION IF EXISTS `YourDatabase`.`DelayedHello`;

DELIMITER $
CREATE DEFINER=CURRENT_USER() FUNCTION `DelayedHello`() RETURNS varchar(20)
SQL SECURITY INVOKER
    DETERMINISTIC
BEGIN
    DO SLEEP(30);
    RETURN CONCAT('Hello ','World!');
END$
DELIMITER ;

Keep in mind that:

• Using SQL SECURITY DEFINER means that the function will be executed and reported as the DEFINER and with the permissions available to the DEFINER.

• Using SQL SECURITY INVOKER means your function will be executed with the permissions of the INVOKER and reported as the INVOKER when examining processes.

Edit: Cleaned up some of the example code and explanations.

StackExchange Database Administrators Q#334267, answer score: 2