Description:
Documentation (https://dev.mysql.com/doc/refman/5.6/en/create-procedure.html) states that:
If a user value is given for the DEFINER clause, it should be a MySQL account specified as 'user_name'@'host_name' (the same format used in the GRANT statement), CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE PROCEDURE or CREATE FUNCTION or statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.
and this is apparently working ok at the PROCEDURE level, but when the EXECUTE and ALTER ROUTINE grant is issued (per https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_automatic_sp_p...), USER() is being used, instead of CURRENT_USER().
Moreover, if replication is set up, the EXECUTE and ALTER ROUTINE grant is correctly issued in the slave using the value one would see for current_user() in the master server; which leaves the servers inconsistent (this does not stop the replication process, though).
This is happening for latest 5.5 and 5.6 versions. Outputs below are for 5.6 only, but I have checked with both.
Some additional information about the environment:
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.6.24 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.24-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+------------------------------+
mysql> select @@global.binlog_format, @@global.sql_mode, @@global.automatic_sp_privileges;
+------------------------+------------------------+----------------------------------+
| @@global.binlog_format | @@global.sql_mode | @@global.automatic_sp_privileges |
+------------------------+------------------------+----------------------------------+
| ROW | NO_ENGINE_SUBSTITUTION | 1 |
+------------------------+------------------------+----------------------------------+
How to repeat:
Create the new user in the master:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE ROUTINE ON *.* TO 'testgrants'@'%' IDENTIFIED BY 'pass';
Login to the master with the new user, check user list, create a new procedure, check user list again and grants (notice the new user created with @localhost):
$ mysql -h127.0.0.1 -P21489 -utestgrants -ppass
<...cut...>
mysql> SELECT user, host FROM mysql.user WHERE user='testgrants';
+------------+------+
| user | host |
+------------+------+
| testgrants | % |
+------------+------+
1 row in set (0.00 sec)
mysql> CREATE SCHEMA foo;
Query OK, 1 row affected (0.00 sec)
mysql> DELIMITER //
mysql>
mysql> CREATE PROCEDURE foo.bar()
-> SQL SECURITY DEFINER
-> BEGIN
-> SELECT 1;
-> END;
->
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> SELECT user, host FROM mysql.user WHERE user='testgrants';
+------------+-----------+
| user | host |
+------------+-----------+
| testgrants | % |
| testgrants | localhost |
+------------+-----------+
2 rows in set (0.00 sec)
mysql> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------+
| Grants for testgrants@% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE ROUTINE ON *.* TO 'testgrants'@'%' IDENTIFIED BY PASSWORD |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR testgrants@localhost;
+-----------------------------------------------------------------------------------+
| Grants for testgrants@localhost |
+-----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testgrants'@'localhost' |
| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `foo`.`bar` TO 'testgrants'@'localhost' |
+-----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select user(), current_user();
+----------------------+----------------+
| user() | current_user() |
+----------------------+----------------+
| testgrants@localhost | testgrants@% |
+----------------------+----------------+
1 row in set (0.00 sec)
Now, for the second part of this report, login to the slave, and check user list and grants:
mysql> SELECT user, host FROM mysql.user WHERE user='testgrants';
+------------+------+
| user | host |
+------------+------+
| testgrants | % |
+------------+------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR testgrants;
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for testgrants@% |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE ROUTINE ON *.* TO 'testgrants'@'%' IDENTIFIED BY PASSWORD '*196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7' |
| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `foo`.`bar` TO 'testgrants'@'%' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Suggested fix:
Use the same user/host as seen with CURRENT_USER() in the master server for the implicit GRANT after creating a procedure.
