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.