一、错误描述

使用mybatis连接mysql库查询报"java.sql.SQLSyntaxErrorException: SELECT command denied to user 'xwood_dev'@'192.168.1.213' for table 'user_info_temp'"异常，详细日志如下

java.sql.SQLSyntaxErrorException: SELECT command denied to user 'xwood_dev'@'192.168.1.213' for table 'user_info'@b@	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)@b@	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)@b@	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)@b@	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974)@b@	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1024)@b@	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3188)@b@	at com.alibaba.druid.wall.WallFilter.preparedStatement_executeQuery(WallFilter.java:648)@b@	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3185)@b@	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)@b@	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3185)@b@	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:181)@b@	at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)@b@	at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.queryTotal(PaginationInterceptor.java:241)@b@	at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:201)@b@	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)@b@	at com.sun.proxy.$Proxy213.prepare(Unknown Source)@b@	at sun.reflect.GeneratedMethodAccessor319.invoke(Unknown Source)@b@	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)@b@	at java.lang.reflect.Method.invoke(Method.java:498)@b@	at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)@b@	at com.xwood.cloud.ccommon.data.mybatis.plugin.sqlcheck.SqlCheckInterceptor.intercept(SqlCheckInterceptor.java:74)@b@	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)@b@	at com.sun.proxy.$Proxy213.prepare(Unknown Source)@b@	at sun.reflect.GeneratedMethodAccessor319.invoke(Unknown Source)@b@	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)@b@	at java.lang.reflect.Method.invoke(Method.java:498)@b@	at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)@b@	at com.xwood.cloud.ccommon.data.mybatis.plugin.datascope.DataScopeInterceptor.intercept(DataScopeInterceptor.java:54)@b@	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)@b@	at com.sun.proxy.$Proxy213.prepare(Unknown Source)@b@	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92)@b@	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:66)@b@	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)@b@	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)@b@	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:136)@b@	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)@b@	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)@b@	at sun.reflect.GeneratedMethodAccessor334.invoke(Unknown Source)@b@	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)@b@	at java.lang.reflect.Method.invoke(Method.java:498)@b@	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)@b@	at com.sun.proxy.$Proxy197.selectList(Unknown Source)@b@	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)@b@	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:134)@b@	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:96)@b@	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)@b@	at com.sun.proxy.$Proxy267.selectOrderAccessReports(Unknown Source)@b@	at com.xwood.cloud.common.report.service.impl.UserInfoServiceImpl.getUsers(UserInfoServiceImpl.java:164)@b@	at com.xwood.cloud.common.report.controller.UserInfoController.querUserList(UserInfoController.java:77)@b@	at com.xwood.cloud.common.report.controller.UserInfoController$$FastClassBySpringCGLIB$$b8f45f0a.invoke(<generated>)@b@	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)@b@	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)@b@	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)@b@	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)@b@	at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:64)@b@	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)@b@	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)@b@	at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:57)@b@	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)@b@	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)@b@	at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:58)@b@	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)@b@	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)@b@	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)@b@	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)@b@	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)@b@	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)@b@	at com.xwood.cloud.common.report.controller.UserInfoController$$EnhancerBySpringCGLIB$$ec4d547c.orderAccessList(<generated>)@b@	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)@b@	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)@b@	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)@b@	at java.lang.reflect.Method.invoke(Method.java:498)@b@	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)@b@	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)@b@	at com.xwood.cloud.ccommon.core.tangram.enditpoint.TangramInvocableHandlerMethod.invokeAndHandle(TangramInvocableHandlerMethod.java:79)@b@	at com.xwood.cloud.ccommon.core.tangram.enditpoint.TangramMethodHandlerAdapter.invokeHandlerMethod(TangramMethodHandlerAdapter.java:829)@b@	at com.xwood.cloud.ccommon.core.tangram.enditpoint.TangramMethodHandlerAdapter.handleInternal(TangramMethodHandlerAdapter.java:743)@b@	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)@b@	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060)@b@	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962)@b@	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)@b@	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)@b@	at javax.servlet.http.HttpServlet.service(HttpServlet.java:665)@b@	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)@b@	at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)@b@	at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:327)@b@	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:115)@b@	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:81)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)@b@	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:105)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:149)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at com.xwood.cloud.ccommon.core.security.component.AfsUserInfoInjectFilter.doFilterInternal(AfsUserInfoInjectFilter.java:65)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.oauth2.server.resource.web.BearerTokenAuthenticationFilter.doFilterInternal(BearerTokenAuthenticationFilter.java:129)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:103)@b@	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:89)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90)@b@	at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:110)@b@	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:80)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:55)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:211)@b@	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:183)@b@	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358)@b@	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:93)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at com.xwood.cloud.ccommon.core.holder.RequestHeaderFilter.doFilter(RequestHeaderFilter.java:72)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at com.xwood.cloud.ccommon.core.security.filter.IgnoreUriFilter.doFilter(IgnoreUriFilter.java:48)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)@b@	at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)@b@	at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)@b@	at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)@b@	at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)@b@	at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:117)@b@	at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)@b@	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)@b@	at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)@b@	at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)@b@	at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)@b@	at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)@b@	at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)@b@	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)@b@	at io.undertow.servlet.handlers.SendErrorPageHandler.handleRequest(SendErrorPageHandler.java:52)@b@	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)@b@	at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:269)@b@	at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:78)@b@	at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:133)@b@	at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:130)@b@	at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)@b@	at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)@b@	at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:249)@b@	at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:78)@b@	at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:99)@b@	at io.undertow.server.Connectors.executeRootHandler(Connectors.java:387)@b@	at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:841)@b@	at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)@b@	at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:2019)@b@	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1558)@b@	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1423)@b@	at java.lang.Thread.run(Thread.java:748)

二、解决方法

因切换不同数据库环境（如从测试环境切换至开发环境），开发环境数据库为"sysos_dev"

<select id="getUserInfo" resultMap="userVoResultMap">@b@        SELECT@b@        <include refid="userRoleSql"/>@b@        FROM@b@        sysos.user_info AS 'user'@b@        LEFT JOIN sysos.rel_user_role AS ur ON ur.user_id = 'user'.user_id@b@        LEFT JOIN sysos.role_info AS r ON r.role_id = ur.role_id@b@        WHERE 'user'.username = #{username}@b@    </select>

改为

<select id="getUserInfo" resultMap="userVoResultMap">@b@        SELECT@b@        <include refid="userRoleSql"/>@b@        FROM@b@        sysos_dev.user_info AS 'user'@b@        LEFT JOIN sysos_dev.rel_user_role AS ur ON ur.user_id = 'user'.user_id@b@        LEFT JOIN sysos_dev.role_info AS r ON r.role_id = ur.role_id@b@        WHERE 'user'.username = #{username}@b@    </select>