将远程数据库导入到本地之后,一启动项目就报错“executecommanddeniedtouser”,提示用户没权限执行自定义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 mons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at mons.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权限的情况:/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'
问题解决!
如果觉得《mysql 创建函数 权限_MySQL赋予用户执行自定义Function的权限》对你有帮助,请点赞、收藏,并留下你的观点哦!