Archery
Archery copied to clipboard
hhyo
[ 问题咨询 ]mongodb不能查询admin库之外的库表，Authentication failed., full error
问题描述
1.查询一个表的数据时，报错
2.还有就是没有库表的查询权限
但是提交申请的时候报错
版本信息
- 应用版本/分支：Release v1.8.2
- 部署方式：Docker
[2022-02-23 09:38:36,039][MainThread:139984038930240][task_id:default][mysql.py:164][WARNING]- MySQL语句执行报错，语句：show slave status，错误信息Traceback (most recent call last): File "/opt/archery/sql/engines/mysql.py", line 153, in query effect_row = cursor.execute(sql) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query _mysql.connection.query(self, query) MySQLdb._exceptions.OperationalError: (1227, 'Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation')
[2022-02-23 09:38:36,040][MainThread:139984038930240][task_id:default][mysql.py:164][WARNING]- MySQL语句执行报错，语句：select user_id,uname from user_info where id=1 limit -1;，错误信息Traceback (most recent call last): File "/opt/archery/sql/engines/mysql.py", line 153, in query effect_row = cursor.execute(sql) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query _mysql.connection.query(self, query) MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1")
[2022-02-23 09:38:36,039][MainThread:139984038930240][task_id:default][mysql.py:164][WARNING]- MySQL语句执行报错，语句：show slave status，错误信息Traceback (most recent call last): File "/opt/archery/sql/engines/mysql.py", line 153, in query effect_row = cursor.execute(sql) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query _mysql.connection.query(self, query) MySQLdb._exceptions.OperationalError: (1227, 'Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation')
[2022-02-23 09:38:36,040][MainThread:139984038930240][task_id:default][mysql.py:164][WARNING]- MySQL语句执行报错，语句：select user_id,uname from user_info where id=1 limit -1;，错误信息Traceback (most recent call last): File "/opt/archery/sql/engines/mysql.py", line 153, in query effect_row = cursor.execute(sql) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query _mysql.connection.query(self, query) MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1")
我的sql是select user_id,uname from user_info where id=1, 但是透传的时候就变成了 select user_id,uname from user_info where id=1 limit -1所以会报错，另外看日志上只读查询为啥还要show slave status，还报错(1227, 'Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation')
另外，我点“SQL分析菜单”的时候也报错 [2022-02-23 09:49:42,094][MainThread:139984038930240][task_id:default][exception_logging_middleware.py:11][ERROR]- Traceback (most recent call last): File "/opt/venv4archery/lib/python3.8/site-packages/django/core/handlers/base.py", line 181, in _get_response response = wrapped_callback(request, *callback_args, **callback_kwargs) File "/opt/venv4archery/lib/python3.8/site-packages/django/contrib/auth/decorators.py", line 20, in _wrapped_view if test_func(request.user): File "/opt/venv4archery/lib/python3.8/site-packages/django/contrib/auth/decorators.py", line 70, in check_perms raise PermissionDenied django.core.exceptions.PermissionDenied
上线工单那，工单执行后想看回滚语句报
[2022-02-23 10:05:57,390][MainThread:139984038930240][task_id:default][inception.py:226][ERROR]- 获取回滚语句报错，异常信息Traceback (most recent call last): File "/opt/archery/sql/engines/inception.py", line 213, in get_rollback cur.execute(sql_table) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query mysql.connection.query(self, query) MySQLdb.exceptions.ProgrammingError: (1146, "Table '192_168_56_10_4401_qa.$$Inception_backup_information$$' doesn't exist") 是我设置的备份地址不对吗，必须要设置为docker的mysql地址吗，我设置的是外部的一个实例，但是我看回滚信息在docker 的mysql里面是有的
我的sql是select user_id,uname from user_info where id=1, 但是透传的时候就变成了 select user_id,uname from user_info where id=1 limit -1所以会报错，另外看日志上只读查询为啥还要show slave status，还报错(1227, 'Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation')
1.看下申请查询权限时选择的限制行数是多少 2.MySQLdb模块和goinception需要用到复制权限，如果有用到上线功能的话最好把权限给大些
另外，我点“SQL分析菜单”的时候也报错 [2022-02-23 09:49:42,094][MainThread:139984038930240][task_id:default][exception_logging_middleware.py:11][ERROR]- Traceback (most recent call last): File "/opt/venv4archery/lib/python3.8/site-packages/django/core/handlers/base.py", line 181, in _get_response response = wrapped_callback(request, *callback_args, **callback_kwargs) File "/opt/venv4archery/lib/python3.8/site-packages/django/contrib/auth/decorators.py", line 20, in _wrapped_view if test_func(request.user): File "/opt/venv4archery/lib/python3.8/site-packages/django/contrib/auth/decorators.py", line 70, in check_perms raise PermissionDenied django.core.exceptions.PermissionDenied
这个是权限问题，后台授权下这个菜单功能
上线工单那，工单执行后想看回滚语句报
[2022-02-23 10:05:57,390][MainThread:139984038930240][task_id:default][inception.py:226][ERROR]- 获取回滚语句报错，异常信息Traceback (most recent call last): File "/opt/archery/sql/engines/inception.py", line 213, in get_rollback cur.execute(sql_table) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query mysql.connection.query(self, query) MySQLdb.exceptions.ProgrammingError: (1146, "Table '192_168_56_10_4401_qa.$$Inception_backup_information$$' doesn't exist") 是我设置的备份地址不对吗，必须要设置为docker的mysql地址吗，我设置的是外部的一个实例，但是我看回滚信息在docker 的mysql里面是有的
备份库可以与archery库分开，在配置项里设置了还要在inception配置文件里配置，默认是配置到docker mysql的，要改下 https://github.com/hhyo/Archery/blob/1cbd9ddb2165481cc7089bb5dfb7abba6f405375/src/docker-compose/inception/config.toml#L66-L69
上线工单那，工单执行后想看回滚语句报
[2022-02-23 10:05:57,390][MainThread:139984038930240][task_id:default][inception.py:226][ERROR]- 获取回滚语句报错，异常信息Traceback (most recent call last): File "/opt/archery/sql/engines/inception.py", line 213, in get_rollback cur.execute(sql_table) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query mysql.connection.query(self, query) MySQLdb.exceptions.ProgrammingError: (1146, "Table '192_168_56_10_4401_qa.$$Inception_backup_information$$' doesn't exist") 是我设置的备份地址不对吗，必须要设置为docker的mysql地址吗，我设置的是外部的一个实例，但是我看回滚信息在docker 的mysql里面是有的
上线工单那，工单执行后想看回滚语句报 [2022-02-23 10:05:57,390][MainThread:139984038930240][task_id:default][inception.py:226][ERROR]- 获取回滚语句报错，异常信息Traceback (most recent call last): File "/opt/archery/sql/engines/inception.py", line 213, in get_rollback cur.execute(sql_table) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query mysql.connection.query(self, query) MySQLdb.exceptions.ProgrammingError: (1146, "Table '192_168_56_10_4401_qa.$$Inception_backup_information$$' doesn't exist") 是我设置的备份地址不对吗，必须要设置为docker的mysql地址吗，我设置的是外部的一个实例，但是我看回滚信息在docker 的mysql里面是有的
备份库可以与archery库分开，在配置项里设置了还要在inception配置文件里配置，默认是配置到docker mysql的，要改下
https://github.com/hhyo/Archery/blob/1cbd9ddb2165481cc7089bb5dfb7abba6f405375/src/docker-compose/inception/config.toml#L66-L69
确认配置项和config.toml是否配置了同一个备份库信息
上线工单那，工单执行后想看回滚语句报 [2022-02-23 10:05:57,390][MainThread:139984038930240][task_id:default][inception.py:226][ERROR]- 获取回滚语句报错，异常信息Traceback (most recent call last): File "/opt/archery/sql/engines/inception.py", line 213, in get_rollback cur.execute(sql_table) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/opt/venv4archery/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query mysql.connection.query(self, query) MySQLdb.exceptions.ProgrammingError: (1146, "Table '192_168_56_10_4401_qa.$$Inception_backup_information$$' doesn't exist") 是我设置的备份地址不对吗，必须要设置为docker的mysql地址吗，我设置的是外部的一个实例，但是我看回滚信息在docker 的mysql里面是有的
备份库可以与archery库分开，在配置项里设置了还要在inception配置文件里配置，默认是配置到docker mysql的，要改下
https://github.com/hhyo/Archery/blob/1cbd9ddb2165481cc7089bb5dfb7abba6f405375/src/docker-compose/inception/config.toml#L66-L69
谢谢，这个可以了
关于mongodb的查询，也是报认证权限错误，但是实际用该账号去库里查没问题，配置的是dba权限用户
docker日志报错如下： [2022-02-23 13:47:57,173][MainThread:139984038930240][task_id:default][query.py:169][ERROR]- 查询异常报错，查询语句：db.Order.find() ，错误信息：Traceback (most recent call last): File "/opt/archery/sql/query.py", line 87, in query query_engine.get_connection(db_name=db_name) File "/opt/archery/sql/engines/mongo.py", line 510, in get_connection self.conn[self.db_name].authenticate(self.user, self.password, self.db_name) File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/database.py", line 1492, in authenticate self.client._cache_credentials( File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/mongo_client.py", line 781, in _cache_credentials sock_info.authenticate(credentials) File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/pool.py", line 810, in authenticate auth.authenticate(credentials, self) File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/auth.py", line 673, in authenticate auth_func(credentials, sock_info) File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/auth.py", line 591, in _authenticate_default return _authenticate_scram(credentials, sock_info, 'SCRAM-SHA-1') File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/auth.py", line 295, in _authenticate_scram res = sock_info.command(source, cmd) File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/pool.py", line 683, in command return command(self, dbname, spec, slave_ok, File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/network.py", line 159, in command helpers._check_command_response( File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/helpers.py", line 167, in _check_command_response raise OperationFailure(msg % errmsg, code, response, pymongo.errors.OperationFailure: Authentication failed., full error: {'ok': 0.0, 'errmsg': 'Authentication failed.', 'code': 18, 'codeName': 'AuthenticationFailed'}
关于mongodb的查询，也是报认证权限错误，但是实际用该账号去库里查没问题，配置的是dba权限用户
docker日志报错如下： [2022-02-23 13:47:57,173][MainThread:139984038930240][task_id:default][query.py:169][ERROR]- 查询异常报错，查询语句：db.Order.find() ，错误信息：Traceback (most recent call last): File "/opt/archery/sql/query.py", line 87, in query query_engine.get_connection(db_name=db_name) File "/opt/archery/sql/engines/mongo.py", line 510, in get_connection self.conn[self.db_name].authenticate(self.user, self.password, self.db_name) File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/database.py", line 1492, in authenticate self.client._cache_credentials( File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/mongo_client.py", line 781, in _cache_credentials sock_info.authenticate(credentials) File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/pool.py", line 810, in authenticate auth.authenticate(credentials, self) File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/auth.py", line 673, in authenticate auth_func(credentials, sock_info) File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/auth.py", line 591, in _authenticate_default return _authenticate_scram(credentials, sock_info, 'SCRAM-SHA-1') File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/auth.py", line 295, in _authenticate_scram res = sock_info.command(source, cmd) File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/pool.py", line 683, in command return command(self, dbname, spec, slave_ok, File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/network.py", line 159, in command helpers._check_command_response( File "/opt/venv4archery/lib/python3.8/site-packages/pymongo/helpers.py", line 167, in _check_command_response raise OperationFailure(msg % errmsg, code, response, pymongo.errors.OperationFailure: Authentication failed., full error: {'ok': 0.0, 'errmsg': 'Authentication failed.', 'code': 18, 'codeName': 'AuthenticationFailed'}
看下使用的是否是admin下的高权限账号，如果不是，跨库查询时需要在所有库创建相同账号
权限已经很高了，如下，还不够？ { "_id" : "admin.admindba", "userId" : UUID("e9c8d021-6c89-422f-9822-a948959bb3f2"), "user" : "admindba", "db" : "admin", "credentials" : { "SCRAM-SHA-1" : { "iterationCount" : 10000, "salt" : "UCrK/CKGutTL57nwCbnjqQ==", "storedKey" : "kNq9Perlpp3NnM1ncG9gK4U7htQ=", "serverKey" : "dMtoM8kjta3TfWbdWbKNh/eAVo4=" }, "SCRAM-SHA-256" : { "iterationCount" : 15000, "salt" : "yDJXpf1ioVc0sBefuO0Mx/Bt/s01RrhD6DNYoQ==", "storedKey" : "HfArxrivRzL14otYpTtpPXjUwqxNzrGxRz3lgFN4sVY=", "serverKey" : "EU8ppRmEkljZyVTBHlLL32Kcz5NHIkPd1SPTaFZJs0s=" } }, "roles" : [ { "role" : "userAdminAnyDatabase", "db" : "admin" }, { "role" : "dbAdminAnyDatabase", "db" : "admin" }, { "role" : "root", "db" : "admin" }, { "role" : "readWriteAnyDatabase", "db" : "admin" } ] }
还是少了哪一个权限 [2022-02-23 18:10:35,141][MainThread:139984038930240][task_id:default][exception_logging_middleware.py:11][ERROR]- Traceback (most recent call last): File "/opt/venv4archery/lib/python3.8/site-packages/django/core/handlers/base.py", line 181, in _get_response response = wrapped_callback(request, *callback_args, **callback_kwargs) File "/opt/venv4archery/lib/python3.8/site-packages/django/contrib/auth/decorators.py", line 20, in _wrapped_view if test_func(request.user): File "/opt/venv4archery/lib/python3.8/site-packages/django/contrib/auth/decorators.py", line 70, in check_perms raise PermissionDenied django.core.exceptions.PermissionDenied
https://archerydms.com/modules/query/
先看下文档 文档我都看了 下面这些权限都给了，我觉得权限已经很大了啊，正常普通用户就是提交工单和查询，不能有执行权限的 sql|permission|菜单Dashboard sql|permission|菜单SQL查询 sql|permission|菜单权限管理 sql|permission|菜单优化工具 sql|permission|菜单SQL分析 sql|permission|菜单SQL审核 sql|permission|菜单SQL优化 sql|permission|菜单在线查询 sql|permission|菜单SQL上线 sql|permission|执行SOAR sql|permission|执行SQLAdvisor sql|permission|执行SQLTuning sql|permission|申请查询权限 sql|permission|提交SQL查询 sql|permission|执行SQL分析 sql|permission|提交SQL上线工单 sql|permission|管理查询权限 sql|permission|审核查询权限 sql|permission|审核SQL上线工单 sql|permission|可查询所在资源组内的所有实例
下面这两个不能给，普通用户只能提交变更工单和做只读查询，然后由DBA执行 执行上线工单(资源组粒度或者自己提交的) 可查询所有实例，这个要管理员去给，不同用户只能查询对应实例
https://archerydms.com/modules/query/
先看下文档 文档我都看了 下面这些权限都给了，我觉得权限已经很大了啊，正常普通用户就是提交工单和查询，不能有执行权限的 sql|permission|菜单Dashboard sql|permission|菜单SQL查询 sql|permission|菜单权限管理 sql|permission|菜单优化工具 sql|permission|菜单SQL分析 sql|permission|菜单SQL审核 sql|permission|菜单SQL优化 sql|permission|菜单在线查询 sql|permission|菜单SQL上线 sql|permission|执行SOAR sql|permission|执行SQLAdvisor sql|permission|执行SQLTuning sql|permission|申请查询权限 sql|permission|提交SQL查询 sql|permission|执行SQL分析 sql|permission|提交SQL上线工单 sql|permission|管理查询权限 sql|permission|审核查询权限 sql|permission|审核SQL上线工单 sql|permission|可查询所在资源组内的所有实例
下面这两个不能给，普通用户只能提交变更工单和做只读查询，然后由DBA执行 执行上线工单(资源组粒度或者自己提交的) 可查询所有实例，这个要管理员去给，不同用户只能查询对应实例
我新建了一个权限组给了您上面的所有权限，然后新建了一个用户到这个权限组，但是查询的时候仍然报没有库表的查询权限
这个权限是archery层面控制的，并不是数据库层面的权限，这样才能实现archery配置一个数据库账号就可以分配给不同archery用户不同的查询权限，普通用户在archery查询之前都要在archery申请相应的查询权限
即使我用archery的admin用户去查询mongodb，也是报一样的认证错误，而且mongodb用户已经是root权限了
你的mongodb是什么版本的？
v1.8.2 我在issue里搜那个报错，有人好像遇到了，说修改mongo.py配置就可以，我修改了重启archery容器，还是一样
不是archery，我是说你管理的mongo实例的版本号是多少
先拉下最新的 https://github.com/hhyo/Archery/blob/master/sql/engines/mongo.py 到你本地覆盖再看下有没有问题，我本地用最新代码不能复现你的问题