积微成著 不积跬步,无以至千里

首页   >   web开发   >   MySQL赋予用户执行自定义Function的权限

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权限的情况:https://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'

问题解决!

QQ群:积微成著官方群(686430774),验证消息:积微成著

站长Q:1347384268(加好友请注明来意)

分享到:

欢迎分享本文,转载请注明出处!

作者:不忘初心

发布时间:2018-10-30

永久地址:http://www.jiweichengzhu.com/article/65c7bb2d0a094d89a51e92b19544d0a5