mysql 创建函数 权限_MySQL赋予用户执行自定义Function的权限

将远程数据库导入到本地之后，一启动项目就报错“execute command denied to user”，提示用户没权限执行自定义FunctionCaused by: java.sql.SQLSyntaxErrorException: execute command denied to user 'cxcs'@'localhost' for routine 'hzcx.getOrgName'

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:975)

at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:392)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at sun.reflect.GeneratedMethodAccessor50.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)

at com.sun.proxy.$Proxy25.execute(Unknown Source)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:56)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)

at sun.reflect.GeneratedMethodAccessor49.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)

at com.sun.proxy.$Proxy23.query(Unknown Source)

at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:54)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354)

... 19 more

检查导入sql，发现这个function是在root用户下面的，所以将已定义的function全部删除重建，全部在cxcs这个用户下面，结果发现还是不行，错误依旧。

后来查阅资料时，看到有博主碰到过用户没有给execute权限的情况：http://blog.csdn.net/lwei_998/article/details/44177741

想起来好像自己也没有给用户赋予执行权限，使用show命令查看一下show grants for 'cxcs'@'localhost'

果然是这个原因，用户cxcs只有“select,insert,update,delete,create,drop,alter”这几个权限，赶紧grant一个execute给用户cxcsgrant execute on hzcx.* to 'cxcs'@'localhost'

问题解决！