【原】MySQL赋予用户执行自定义Function的权限

将远程数据库导入到本地之后，一启动项目就报错“execute command denied to user”，提示用户没权限执行自定义Function

Caused 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给用户cxcs

grant execute on hzcx.* to 'cxcs'@'localhost'

问题解决！

mysqlfunctiongrant

看完文章，有任何疑问，请加入群聊一起交流！！！

扫码关注本站微信公众号，第一时间获取网站更新动态！

本文地址：https://www.jiweichengzhu.com/article/65c7bb2d0a094d89a51e92b19544d0a5

关于转载：若站内作品和文章得到了你的青睐，欢迎各位朋友分享到个人站长或者朋友圈，但转载请注明文章出处和原文链接！

很赞哦！ ()