Bug #91158 Incorrect execute authority used for function when using information schema
Submitted: 6 Jun 2018 11:48 Modified: 6 Jun 2018 12:51
Reporter: Simon Greatrix Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.46 OS:Any
Assigned to: CPU Architecture:Any

[6 Jun 2018 11:48] Simon Greatrix
Description:
Execution permission is denied for the wrong user.

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: execute command denied to user 'test_owner'@'localhost' for routine 'test.hiFive'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
	at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2118)
	at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1992)
	at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3403)
	at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:471)
	at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3115)
	at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2344)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2739)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
	at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:837)
	at MySqlFunctionTest.main(MySqlFunctionTest.java:9)

How to repeat:
Test SQL
------------------------------------------------------------------------------------
CREATE DATABASE IF NOT EXISTS `test`;
CREATE USER IF NOT EXISTS `tester`@`localhost` IDENTIFIED BY 'tester';
CREATE USER IF NOT EXISTS `test_owner`@`localhost` IDENTIFIED BY 'tester';
GRANT ALL ON test.* TO `tester`@`localhost`;

USE `test`;

DROP FUNCTION IF EXISTS `hiFive`;

DELIMITER $$
CREATE DEFINER=`test_owner`@`localhost` FUNCTION `hiFive`() RETURNS INTEGER
BEGIN
   RETURN 5;
END;
$$
DELIMITER ;

GRANT EXECUTE ON FUNCTION hiFive TO `tester`@`localhost`;
GRANT EXECUTE ON FUNCTION hiFive TO `test_owner`@`localhost`;
------------------------------------------------------------------------------------

Java Program:

import java.sql.*;
public class MySqlFunctionTest {
  public static void main(String[] args) throws Exception {
    Connection conn = DriverManager.getConnection("jdbc:mysql://address=(host=localhost)(protocol=tcp)(useSSL=false)(noAccessToProcedureBodies=true)"
        + "(useInformationSchema=true)"
        + "(port=3306)/test", "tester", "tester");
    CallableStatement callableStatement = conn.prepareCall("{? = CALL hiFive()}");
    callableStatement.registerOutParameter(1, Types.INTEGER);
    callableStatement.execute();
    System.out.println("Function returned: " + callableStatement.getInt(1));
  }
}

Suggested fix:
None known at this time.
[6 Jun 2018 12:43] Chiranjeevi Battula
Hello Simon Greatrix,

Thank you for the bug report and testcase.
I could not repeat the issue at our end using with Connector / J  5.1.46, MySQL 5.7.22 version.

Thanks,
Chiranjeevi.
[6 Jun 2018 12:44] Chiranjeevi Battula
Screenshot

Attachment: Bug_91158.PNG (image/png, text), 64.88 KiB.

[6 Jun 2018 12:51] Simon Greatrix
Thanks for attempting to replicate the issue. I am using MySQL release 5.7.20, and I note that the bug fixes for 5.7.22 include some to do with permissions on stored procedures. I am happy to accept that this issue has already been fixed. Unfortunately I am not able to upgrade my local system at this time to validate.

I am happy for this bug to be closed.