Description:
The issue here is even with SUPER privileges, function specified as DETERMINISTIC and binlog_format=ROW, creating store function throw an error as below, which is misleading,
ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might want to use the less safe log_bin_trust_function_creators variable)*
Reproducible test: MySQL 8.0.33
As per MySQL doc : https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html#stored-objects-securi...
The following rules determine which accounts you can specify as the DEFINER attribute for a stored object:
If you have the SET_USER_ID privilege (or the deprecated SUPER privilege), you can specify any account as the DEFINER attribute. If the account does not exist, a warning is generated. Additionally, to set a stored object DEFINER attribute to an account that has the SYSTEM_USER privilege, you must have the SYSTEM_USER privilege.
When creating a function with SET_USER_ID privilege, it throws an error as below,
ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might want to use the less safe log_bin_trust_function_creators variable)*
mysql [localhost] {testuser1} (app1) > select current_user();show grants for testuser1@'%'; show grants;show variables like 'binlog_format';
+----------------+
| current_user() |
+----------------+
| testuser1@% |
+----------------+
1 row in set (0.00 sec)
+-----------------------------------------------------+
| Grants for testuser1@% |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `testuser1`@`%` |
| GRANT SET_USER_ID ON *.* TO `testuser1`@`%` |
| GRANT CREATE ROUTINE ON `app1`.* TO `testuser1`@`%` |
+-----------------------------------------------------+
3 rows in set (0.00 sec)
+-----------------------------------------------------+
| Grants for testuser1@% |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `testuser1`@`%` |
| GRANT SET_USER_ID ON *.* TO `testuser1`@`%` |
| GRANT CREATE ROUTINE ON `app1`.* TO `testuser1`@`%` |
+-----------------------------------------------------+
3 rows in set (0.00 sec)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql [localhost] {testuser1} (app1) > DELIMITER //
mysql [localhost] {testuser1} (app1) > CREATE DEFINER=`testuser1`@`%` FUNCTION f1(i INT)
-> RETURNS INT
-> DETERMINISTIC
-> READS SQL DATA
-> BEGIN
-> RETURN i;
-> END;//
ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
mysql [localhost] {testuser1} (app1) > DELIMITER ;
mysql [localhost] {testuser1} (app1) >
####################
Let's add SUPER privileges to user testuser1@% even though the user already has SET_USER_ID privilege.
mysql [localhost] {root} ((none)) > GRANT SUPER ON *.* to `testuser1`@`%`;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql [localhost] {root} ((none)) > show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1287 | The SUPER privilege identifier is deprecated |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)
Still getting the same error,
mysql [localhost] {testuser1} (app1) > DELIMITER //
mysql [localhost] {testuser1} (app1) > CREATE DEFINER=`testuser1`@`%` FUNCTION f1(i INT)
-> RETURNS INT
-> DETERMINISTIC
-> READS SQL DATA
-> BEGIN
-> RETURN i;
-> END;//
ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
mysql [localhost] {testuser1} (app1) > DELIMITER ;
mysql [localhost] {testuser1} (app1) >
Here message error message is misleading as user has SUPER privilege but still it says "You do not have the SUPER privilege "
Also, "binary logging is enabled (you *might want to use the less safe log_bin_trust_function_creators variable)" is confusing as the function specified as *DETERMINISTIC and binlog_format | ROW
As per MySQL doc ,
log_bin_trust_function_creators : This variable applies when binary logging is enabled. It controls whether stored function creators can be trusted not to create stored functions that may cause unsafe events to be written to the binary log. If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation. This variable also applies to trigger creation.
https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html
Because MySQL does not check if a function really is deterministic at creation time, the invocation of a stored function with the DETERMINISTIC keyword might carry out an action that is unsafe for statement-based logging, or invoke a function or procedure containing unsafe statements. If this occurs when binlog_format=STATEMENT is set, a warning message is issued. If row-based or mixed binary logging is in use, no warning is issued, and the statement is replicated in row-based format.
To relax the preceding conditions on function creation (that you must have the SUPER privilege and that a function must be declared deterministic or to not modify data), set the global log_bin_trust_function_creators system variable to 1. By default, this variable has a value of 0, but you can change it like this:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Setting log_bin_trust_function_creators=1; fixes the issue, but it's not what the expected by MySQL document explains.
mysql [localhost] {root} ((none)) > set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {testuser1} (app1) > DELIMITER //
mysql [localhost] {testuser1} (app1) > CREATE DEFINER=`testuser1`@`%` FUNCTION f1(i INT)
-> RETURNS INT
-> DETERMINISTIC
-> READS SQL DATA
-> BEGIN
-> RETURN i;
-> END;//
Query OK, 0 rows affected (0.03 sec)
mysql [localhost] {testuser1} (app1) > DELIMITER ;
mysql [localhost] {testuser1} (app1) > show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql [localhost] {testuser1} (app1) > show grants;
+-------------------------------------------------------------------------+
| Grants for testuser1@% |
+-------------------------------------------------------------------------+
| GRANT SUPER ON *.* TO `testuser1`@`%` |
| GRANT SET_USER_ID ON *.* TO `testuser1`@`%` |
| GRANT CREATE ROUTINE ON `app1`.* TO `testuser1`@`%` |
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `app1`.`f1` TO `testuser1`@`%` |
+-------------------------------------------------------------------------+
4 rows in set (0.00 sec)
How to repeat:
create database app1;
create user `testuser1`@`%` identified by 'msandbox';
GRANT SET_USER_ID on *.* to `testuser1`@`%`;
GRANT CREATE ROUTINE on app1.* to `testuser1`@`%`;
#This function is deterministic (and does not modify data), so it is safe
DELIMITER //
CREATE DEFINER=`testuser1`@`%` FUNCTION f1(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN i;
END;//
DELIMITER ;
##add SUPER privileges to user testuser1@% even though the user already has SET_USER_ID privilege.
It will throw the same error.
Suggested fix:
As the function specified as DETERMINISTIC, has valid privileges (SET_USER_ID,CREATE ROUTINE) and binlog_format = ROW it should not throw an error.
