MySQL Bugs: #102491: Created function does not use SQL SECURITY DEFINER when running

Bug #102491	Created function does not use SQL SECURITY DEFINER when running
Submitted:	5 Feb 2021 10:29	Modified:	8 Feb 2021 14:12
Reporter:	Peter Reinhold	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.23	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
When creating a function that sets SQL SECURITY DEFINER, calling that function from a user without EXECUTE privileges fails with the error:

ERROR 1370 (42000): execute command denied to user 'user'@'localhost' for routine 'SYSNOW'

Defining user is a root user, and running the function on that user works as intended.

How to repeat:
Create the following function with a user that has EXECUTEprivilege

CREATE FUNCTION `SYSNOW`()
RETURNS DATETIME
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
  DECLARE TS DATETIME;
  SELECT NOW() INTO TS;
  RETURN (TS);
END

Call it from a users without EXECUTE privilege

Hi Mr. Reinhold,

Thank you for your bug report.

However, this is not a bug. This is exactly how the system is designed to work, which also makes it compliant with SQL standard. This is described in our Reference Manual.

Not a bug.

From the manual, https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

--
The SQL SECURITY characteristic can be DEFINER or INVOKER to specify the security context; that is, whether the routine executes using the privileges of the account named in the routine DEFINER clause or the user who invokes it. This account must have permission to access the database with which the routine is associated. The default value is DEFINER. The user who invokes the routine must have the EXECUTE privilege for it, as must the DEFINER account if the routine executes in definer security context.
--

Unless the security characteristics of the function itself, but this is very unclear in the text above, specifically "This account must have permission to access the database with which the routine is associated." alludes to the invoking user only having to have access to the database, and not the EXECUTE privilege.

Hi Mr. Reinhold,

I agree with you.

Verified as a documentation bug.

Posted by developer:
 
re:

"
From the manual,
https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
--

The SQL SECURITY characteristic can be DEFINER or INVOKER to specify the
security context; that is, whether the routine executes using the privileges

of the account named in the routine DEFINER clause or the user who invokes
it. This account must have permission to access the database with which the
routine is associated. The default value is DEFINER. The user who invokes the
routine must have the EXECUTE privilege for it, as must the DEFINER account

if the routine executes in definer security context.
--

Unless the security characteristics of the function itself, but this is very
unclear in the text above, specifically "This account must have permission to
access the database with which the routine is associated." alludes to the
invoking user only having to have access to the database, and not the EXECUTE
privilege.

"

The part quoted from the manual explicitly says this:

The user who invokes the
routine must have the EXECUTE privilege for it

So the requirement for EXECUTE by the invoking user is already
stated.

Not a bug.