Bug #77186 Implicit GRANT incorrectly issued after CREATE PROCEDURE
Submitted: 28 May 2015 22:13 Modified: 8 Jun 2015 11:06
Reporter: Agustín G Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.5 5.6, 5.6.26, 5.7.17 OS:Any
Assigned to: CPU Architecture:Any

[28 May 2015 22:13] Agustín G
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.
[8 Jun 2015 11:06] Umesh Shastry
Hello Agustín G,

Thank you for the report.
Observed this with latest 5.6.26 build.

Thanks,
Umesh
[8 Jun 2015 11:13] Umesh Shastry
Bug #76622 marked as duplicate of this.
[16 Mar 2017 8:09] Umesh Shastry
Bug #85472 marked as duplicate of this