com.kingbase8.util.KSQLException: ERROR: permission denied for table xxx

前言

在信创改造中，数据库替换为国产数据库是不可缺少的一部分。而可替换选项中多数选项无非是人大金仓和达梦数据库二选一。本文将介绍人大金仓在使用过程的问题以及解决办法。

问题

在使用人大金仓数据库后，程序运行报错 com.kingbase8.util.KSQLException: ERROR: permission denied for table config_info

详细错误如下

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'externalDumpService': Invocation of init method failed; nested exception is ErrCode:500, ErrMsg:Nacos Server did not start because dumpservice bean construction failure :
StatementCallback; bad SQL grammar [SELECT MAX(id) FROM config_info]; nested exception is com.kingbase8.util.KSQLException: ERROR: permission denied for table config_infoat org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:160)at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:440)at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1796)at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620)at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542)at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:336)at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:334)at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:209)at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1391)at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1311)at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:904)at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:781)... 19 common frames omitted
Caused by: com.alibaba.nacos.api.exception.NacosException: Nacos Server did not start because dumpservice bean construction failure :
StatementCallback; bad SQL grammar [SELECT MAX(id) FROM config_info]; nested exception is com.kingbase8.util.KSQLException: ERROR: permission denied for table config_infoat com.alibaba.nacos.config.server.service.dump.DumpService.dumpOperate(DumpService.java:293)at com.alibaba.nacos.config.server.service.dump.ExternalDumpService.init(ExternalDumpService.java:65)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.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:389)at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:333)at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:157)... 32 common frames omitted
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT MAX(id) FROM config_info]; nested exception is com.kingbase8.util.KSQLException: ERROR: permission denied for table config_infoat org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1575)at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:398)at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:466)at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:476)at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:509)at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:516)at com.alibaba.nacos.config.server.service.repository.extrnal.ExternalConfigInfoPersistServiceImpl.findConfigMaxId(ExternalConfigInfoPersistServiceImpl.java:676)at com.alibaba.nacos.config.server.service.repository.extrnal.ExternalConfigInfoPersistServiceImpl$$FastClassBySpringCGLIB$$a645f555.invoke(<generated>)at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:792)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:64)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:707)at com.alibaba.nacos.config.server.service.repository.extrnal.ExternalConfigInfoPersistServiceImpl$$EnhancerBySpringCGLIB$$f587fde5.findConfigMaxId(<generated>)at com.alibaba.nacos.config.server.service.dump.processor.DumpAllProcessor.process(DumpAllProcessor.java:62)at com.alibaba.nacos.config.server.service.dump.DumpService.dumpAllConfigInfoOnStartup(DumpService.java:332)at com.alibaba.nacos.config.server.service.dump.DumpService.dumpOperate(DumpService.java:263)... 40 common frames omitted
Caused by: com.kingbase8.util.KSQLException: ERROR: permission denied for table config_infoat com.kingbase8.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:3551)at com.kingbase8.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2993)at com.kingbase8.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)at com.kingbase8.jdbc.KbStatement.executeInternal_(KbStatement.java:784)at com.kingbase8.jdbc.KbStatement.execute(KbStatement.java:676)at com.kingbase8.jdbc.KbStatement.executeWithFlags(KbStatement.java:579)at com.kingbase8.jdbc.KbStatement.executeCachedSql(KbStatement.java:553)at com.kingbase8.jdbc.KbStatement.executeWithFlags(KbStatement.java:514)at com.kingbase8.jdbc.KbStatement.executeQuery(KbStatement.java:415)at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111)at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:453)at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:383)... 61 common frames omitted

分析

错误信息 com.kingbase8.util.KSQLException: ERROR: permission denied for table xxx 表示在尝试访问或操作数据库中的 xxx 表时，遇到了权限问题。Kingbase（金仓数据库）是一个关系型数据库管理系统，这个错误通常意味着当前数据库用户没有足够的权限来执行请求的操作。

解决

确认当前用户的权限

检查执行操作的数据库用户是否具有足够的权限来访问或修改表 xxx。你可以联系数据库管理员来获取这些信息。

授予权限

如果你有足够的权限，可以手动授予访问表的权限。这通常涉及到执行一个 GRANT 语句。例如：

GRANT SELECT, INSERT, UPDATE, DELETE ON xxx TO 'username';

这会给用户 username 授予对表 xxx 的查询、插入、更新和删除权限。确保替换 xxx、username 和所需的权限列表以符合你的具体情况。

检查权限是否已经授予

使用 SHOW GRANTS 语句来查看当前用户或特定用户对特定表的权限：

SHOW GRANTS ON xxx FOR 'username';

这将列出用户 username 对表 xxx 的所有权限。

如果还是不行使用终极办法使用如下步骤重新建库

终极办法

此方式有点危险，线上环境禁用。另外此方式仅适用于以上无法解决问题的情况

备份旧的库将表数据和结构导出为SQL

可以使用工具

移除现在的库

# test修改为真实的库名
DROP DATABASE test;

确认已有的用户是否存在最好用户名与数据库名相同

重新创建库

# 数据库名修改为对应的数据库 以及test修改对应的用户
create database test owner test;

创建schema

# 创建schema 与数据库名称相同
create schema test;

执行SQL

使用工具导入SQL执行（之前导出的数据表和结构 sql）仅适用于数据量较少的情况
如果数据量较多可以使用其他两种方式

数据库命令行执行SQL
使用人大金仓的自带的dts 传输数据

good day！！！