def testSendMail(self, _quit, sendmail, login, _): """有密码测试""" some_sub = 'test_subject' some_body = 'mail_body' some_to = ['mail_to'] archer_config = SysConfig() archer_config.set('mail_ssl', '') archer_config.set('mail_smtp_password', self.smtp_password) sender = MsgSender() sender.send_email(some_sub, some_body, some_to) login.assert_called_once() sendmail.assert_called_with(self.smtp_user, some_to, ANY) _quit.assert_called_once()
def get_backup_connection(): archer_config = SysConfig() backup_host = archer_config.get('inception_remote_backup_host') backup_port = int(archer_config.get('inception_remote_backup_port', 3306)) backup_user = archer_config.get('inception_remote_backup_user') backup_password = archer_config.get('inception_remote_backup_password') return MySQLdb.connect(host=backup_host, port=backup_port, user=backup_user, passwd=backup_password, charset='utf8mb4', autocommit=True )
def tablesapce(request): instance_name = request.POST.get('instance_name') # 判断是RDS还是其他实例 if len( AliyunRdsConfig.objects.filter(instance_name=instance_name, is_enable=1)) > 0: if SysConfig().sys_config.get('aliyun_rds_manage'): result = aliyun_sapce_status(request) else: raise Exception('未开启rds管理,无法查看rds数据!') else: sql = ''' SELECT table_schema, table_name, engine, TRUNCATE((data_length+index_length+data_free)/1024/1024,2) AS total_size, table_rows, TRUNCATE(data_length/1024/1024,2) AS data_size, TRUNCATE(index_length/1024/1024,2) AS index_size, TRUNCATE(data_free/1024/1024,2) AS data_free, TRUNCATE(data_free/(data_length+index_length+data_free)*100,2) AS pct_free FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'test', 'sys') ORDER BY total_size DESC LIMIT 14;'''.format(instance_name) table_space = Dao(instance_name=instance_name).mysql_query( 'information_schema', sql) column_list = table_space['column_list'] rows = [] for row in table_space['rows']: row_info = {} for row_index, row_item in enumerate(row): row_info[column_list[row_index]] = row_item rows.append(row_info) result = {'status': 0, 'msg': 'ok', 'data': rows} # 返回查询结果 return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True), content_type='application/json')
def login_authenticate(username, password): """ 登录认证,包含一个登录失败计数器,5分钟内连续失败5次的账号,会被锁定5分钟 """ sys_config = SysConfig().sys_config if sys_config.get('lock_cnt_threshold'): lock_cnt_threshold = int(sys_config.get('lock_cnt_threshold')) else: lock_cnt_threshold = 5 if sys_config.get('lock_time_threshold'): lock_time_threshold = int(sys_config.get('lock_time_threshold')) else: lock_time_threshold = 300 # 服务端二次验证参数 if username == "" or password == "" or username is None or password is None: result = {'status': 2, 'msg': '登录用户名或密码为空,请重新输入!', 'data': ''} elif username in login_failure_counter and login_failure_counter[username][ "cnt"] >= lock_cnt_threshold and ( datetime.datetime.now() - login_failure_counter[username] ["last_failure_time"]).seconds <= lock_time_threshold: result = {'status': 3, 'msg': '登录失败超过5次,该账号已被锁定5分钟!', 'data': ''} else: # 登录 user = authenticate(username=username, password=password) # 登录成功 if user: # 如果登录失败计数器中存在该用户名,则清除之 if username in login_failure_counter: login_failure_counter.pop(username) result = {'status': 0, 'msg': 'ok', 'data': user} # 登录失败 else: if username not in login_failure_counter: # 第一次登录失败,登录失败计数器中不存在该用户,则创建一个该用户的计数器 login_failure_counter[username] = { "cnt": 1, "last_failure_time": datetime.datetime.now() } else: if (datetime.datetime.now() - login_failure_counter[username]["last_failure_time"] ).seconds <= lock_time_threshold: login_failure_counter[username]["cnt"] += 1 else: # 上一次登录失败时间早于5分钟前,则重新计数。以达到超过5分钟自动解锁的目的。 login_failure_counter[username]["cnt"] = 1 login_failure_counter[username][ "last_failure_time"] = datetime.datetime.now() result = {'status': 1, 'msg': '用户名或密码错误,请重新输入!', 'data': ''} return result
def _db_priv(user, instance, db_name): """ 检测用户是否拥有指定库权限 :param user: 用户对象 :param instance: 实例对象 :param db_name: 库名 :return: 权限存在则返回对应权限的limit_num,否则返回False TODO 返回统一为 int 类型, 不存在返回0 (虽然其实在python中 0==False) """ # 获取用户库权限 user_privileges = QueryPrivileges.objects.filter(user_name=user.username, instance=instance, db_name=str(db_name), valid_date__gte=datetime.datetime.now(), is_deleted=0, priv_type=1) if user.is_superuser: return int(SysConfig().get('admin_query_limit', 5000)) else: if user_privileges.exists(): return user_privileges.first().limit_num return False
def _tb_priv(user, instance, db_name, tb_name): """ 检测用户是否拥有指定表权限 :param user: 用户对象 :param instance: 实例对象 :param db_name: 库名 :param tb_name: 表名 :return: 权限存在则返回对应权限的limit_num,否则返回False """ # 获取用户表权限 user_privileges = QueryPrivileges.objects.filter(user_name=user.username, instance=instance, db_name=str(db_name), table_name=str(tb_name), valid_date__gte=datetime.datetime.now(), is_deleted=0, priv_type=2) if user.is_superuser: return int(SysConfig().get('admin_query_limit', 5000)) else: if user_privileges.exists(): return user_privileges.first().limit_num return False
def sqladvisor(request): sql_content = request.POST.get('sql_content') instance_name = request.POST.get('instance_name') db_name = request.POST.get('db_name') verbose = request.POST.get('verbose') result = {'status': 0, 'msg': 'ok', 'data': []} # 服务器端参数验证 if sql_content is None or instance_name is None: result['status'] = 1 result['msg'] = '页面提交参数可能为空' return HttpResponse(json.dumps(result), content_type='application/json') sql_content = sql_content.strip() try: user_instances(request.user, 'all').get(instance_name=instance_name) except Exception: result['status'] = 1 result['msg'] = '你所在组未关联该实例!' return HttpResponse(json.dumps(result), content_type='application/json') if verbose is None or verbose == '': verbose = 1 # 取出实例的连接信息 instance_info = Instance.objects.get(instance_name=instance_name) # 提交给sqladvisor获取审核结果 sqladvisor_path = SysConfig().get('sqladvisor') sql_content = sql_content.strip().replace('"', '\\"').replace('`', '').replace('\n', ' ') try: p = subprocess.Popen(sqladvisor_path + ' -h "%s" -P "%s" -u "%s" -p "%s\" -d "%s" -v %s -q "%s"' % ( str(instance_info.host), str(instance_info.port), str(instance_info.user), str(instance_info.raw_password), str(db_name), verbose, sql_content), stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.STDOUT, shell=True, universal_newlines=True) stdout, stderr = p.communicate() result['data'] = stdout except Exception: logger.error(traceback.format_exc()) result['data'] = 'sqladvisor运行报错,请检查日志' return HttpResponse(json.dumps(result), content_type='application/json')
def test_replace_configs(self): archer_config = SysConfig() new_config = json.dumps( [{'key': 'numconfig', 'value': 1}, {'key': 'strconfig', 'value': 'strconfig'}, {'key': 'boolconfig', 'value': 'false'}]) archer_config.replace(new_config) archer_config.get_all_config() expected_config = { 'numconfig': '1', 'strconfig': 'strconfig', 'boolconfig': False } self.assertEqual(archer_config.sys_config, expected_config)
def sign_up(request): username = request.POST.get('username') password = request.POST.get('password') password2 = request.POST.get('password2') display = request.POST.get('display') email = request.POST.get('email') result = {'status': 0, 'msg': 'ok', 'data': None} if not (username and password): result['status'] = 1 result['msg'] = '用户名和密码不能为空' elif len(Users.objects.filter(username=username)) > 0: result['status'] = 1 result['msg'] = '用户名已存在' elif password != password2: result['status'] = 1 result['msg'] = '两次输入密码不一致' else: # 验证密码 try: validate_password(password) except ValidationError as msg: result['status'] = 1 result['msg'] = str(msg) else: # 添加用户并且添加到默认权限组 Users.objects.create_user(username=username, password=password, display=display, email=email, is_active=1, is_staff=1) default_auth_group = SysConfig().sys_config.get( 'default_auth_group', '') try: user = Users.objects.get(username=username) group = Group.objects.get(name=default_auth_group) user.groups.add(group) except Exception: logger.error('无name为{}的权限组,无法默认关联,请到系统设置进行配置'.format( default_auth_group)) return HttpResponse(json.dumps(result), content_type='application/json')
def analyze(request): """ 利用soar分析SQL :param request: :return: """ text = request.POST.get('text') instance_name = request.POST.get('instance_name') db_name = request.POST.get('db_name') if not text: result = {"total": 0, "rows": []} else: soar = Soar() if instance_name != '' and db_name != '': try: instance_info = user_instances(request.user, db_type=['mysql']).get(instance_name=instance_name) except Instance.DoesNotExist: return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例!', 'data': []}) soar_test_dsn = SysConfig().get('soar_test_dsn') # 获取实例连接信息 online_dsn = "{user}:{pwd}@{host}:{port}/{db}".format(user=instance_info.user, pwd=instance_info.password, host=instance_info.host, port=instance_info.port, db=db_name) else: online_dsn = '' soar_test_dsn = '' args = {"report-type": "markdown", "query": '', "online-dsn": online_dsn, "test-dsn": soar_test_dsn, "allow-online-as-test": "false"} rows = generate_sql(text) for row in rows: args['query'] = row['sql'].replace('\n', ' ') cmd_args = soar.generate_args2cmd(args=args, shell=True) stdout, stderr = soar.execute_cmd(cmd_args, shell=True).communicate() row['report'] = stdout if stdout else stderr result = {"total": len(rows), "rows": rows} return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True), content_type='application/json')
def kill_session(request): instance_name = request.POST.get('instance_name') request_params = request.POST.get('request_params') result = {'status': 0, 'msg': 'ok', 'data': []} # 判断是RDS还是其他实例 if len(AliyunRdsConfig.objects.filter(instance_name=instance_name)) > 0: if SysConfig().sys_config.get('aliyun_rds_manage'): result = aliyun_kill_session(request) else: raise Exception('未开启rds管理,无法查看rds数据!') else: kill_sql = request_params Dao(instance_name=instance_name).mysql_execute('information_schema', kill_sql) # 返回查询结果 return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True), content_type='application/json')
def query_check(self, db_name=None, sql=''): # 查询语句的检查、注释去除、切分 result = {'msg': '', 'bad_query': False, 'filtered_sql': sql, 'has_star': False} banned_keywords = ["ascii", "char", "charindex", "concat", "concat_ws", "difference", "format", "len", "nchar", "patindex", "quotename", "replace", "replicate", "reverse", "right", "soundex", "space", "str", "string_agg", "string_escape", "string_split", "stuff", "substring", "trim", "unicode"] keyword_warning = '' star_patter = r"(^|,|\s)\*(\s|\(|$)" sql_whitelist = ['select', SysConfig().get("mssql_cmd_white_list", "").strip().split(',')] # 根据白名单list拼接pattern语句 whitelist_pattern = "^" + "|^".join(sql_whitelist) # 删除注释语句,进行语法判断,执行第一条有效sql try: sql = sql.format(sql, strip_comments=True) sql = sqlparse.split(sql)[0] result['filtered_sql'] = sql.strip() sql_lower = sql.lower() except IndexError: result['bad_query'] = True result['msg'] = '没有有效的SQL语句' return result if re.match(whitelist_pattern, sql_lower) is None: result['bad_query'] = True result['msg'] = '仅支持{}语法!'.format(','.join(sql_whitelist)) return result if re.search(star_patter, sql_lower) is not None: keyword_warning += '禁止使用 * 关键词\n' result['has_star'] = True if '+' in sql_lower: keyword_warning += '禁止使用 + 关键词\n' result['bad_query'] = True for keyword in banned_keywords: pattern = r"(^|,| |=){}( |\(|$)".format(keyword) if re.search(pattern, sql_lower) is not None: keyword_warning += '禁止使用 {} 关键词\n'.format(keyword) result['bad_query'] = True if result.get('bad_query') or result.get('has_star'): result['msg'] = keyword_warning return result
def testStarOptionOn(self, _priv_check): c = Client() c.force_login(self.u2) some_limit = 100 sql_with_star = 'select * from some_table' some_db = 'some_db' _priv_check.return_value = {'status': 0, 'data': {'limit_num': 100, 'priv_check': 1}} archer_config = SysConfig() archer_config.set('disable_star', True) r = c.post('/query/', data={'instance_name': self.slave1.instance_name, 'sql_content': sql_with_star, 'db_name': some_db, 'limit_num': some_limit}) archer_config.set('disable_star', False) r_json = r.json() self.assertEqual(1, r_json['status'])
class TestSchemaSync(TestCase): """ 测试SchemaSync """ def setUp(self): self.superuser = User(username='******', is_superuser=True) self.superuser.save() # 使用 travis.ci 时实例和测试service保持一致 self.master = Instance( instance_name='test_instance', type='master', db_type='mysql', host=settings.DATABASES['default']['HOST'], port=settings.DATABASES['default']['PORT'], user=settings.DATABASES['default']['USER'], password=settings.DATABASES['default']['PASSWORD']) self.master.save() self.sys_config = SysConfig() self.client = Client() self.client.force_login(self.superuser) def tearDown(self): self.superuser.delete() self.master.delete() self.sys_config.replace(json.dumps({})) def test_schema_sync(self): """ 测试SchemaSync :return: """ data = { "instance_name": "test_instance", "db_name": "*", "target_instance_name": "test_instance", "target_db_name": "*", "sync_auto_inc": True, "sync_comments": False } r = self.client.post(path='/instance/schemasync/', data=data) self.assertEqual(json.loads(r.content)['status'], 1) self.assertEqual(json.loads(r.content)['msg'], '请配置SchemaSync路径!') self.sys_config.set('schemasync', '/opt/venv4schemasync/bin/schemasync') self.sys_config.get_all_config() r = self.client.post(path='/instance/schemasync/', data=data) self.assertEqual(json.loads(r.content)['status'], 0)
def sign_up(request): sign_up_enabled = SysConfig().get('sign_up_enabled', False) if not sign_up_enabled: result = {'status': 1, 'msg': '注册未启用,请联系管理员开启', 'data': None} return HttpResponse(json.dumps(result), content_type='application/json') username = request.POST.get('username') password = request.POST.get('password') password2 = request.POST.get('password2') display = request.POST.get('display') email = request.POST.get('email') result = {'status': 0, 'msg': 'ok', 'data': None} if not (username and password): result['status'] = 1 result['msg'] = '用户名和密码不能为空' elif len(Users.objects.filter(username=username)) > 0: result['status'] = 1 result['msg'] = '用户名已存在' elif password != password2: result['status'] = 1 result['msg'] = '两次输入密码不一致' elif not display: result['status'] = 1 result['msg'] = '请填写中文名' else: # 验证密码 try: validate_password(password) Users.objects.create_user(username=username, password=password, display=display, email=email, is_active=1, is_staff=True) except ValidationError as msg: result['status'] = 1 result['msg'] = str(msg) return HttpResponse(json.dumps(result), content_type='application/json')
def sqlcronnewquery(request): """SQL在线查询页面""" user = request.user # 获取组信息 group_list = user_groups(user) # 获取所有有效用户,通知对象 active_user = Users.objects.filter(is_active=1) # 获取系统配置 archer_config = SysConfig() # 主动创建标签 InstanceTag.objects.get_or_create(tag_code='can_read', defaults={ 'tag_name': '支持查询', 'active': True }) context = {'active_user': active_user, 'group_list': group_list} # 主动创建标签 return render(request, 'sqlcron/newquery.html', context)
def __init__(self, **kwargs): if kwargs: self.MAIL_REVIEW_SMTP_SERVER = kwargs.get('server') self.MAIL_REVIEW_SMTP_PORT = kwargs.get('port', 0) self.MAIL_REVIEW_FROM_ADDR = kwargs.get('user') self.MAIL_REVIEW_FROM_PASSWORD = kwargs.get('password') self.MAIL_SSL = kwargs.get('ssl') else: sys_config = SysConfig() self.MAIL_REVIEW_SMTP_SERVER = sys_config.get('mail_smtp_server') self.MAIL_REVIEW_SMTP_PORT = sys_config.get('mail_smtp_port', 0) self.MAIL_SSL = sys_config.get('mail_ssl') self.MAIL_REVIEW_FROM_ADDR = sys_config.get('mail_smtp_user') self.MAIL_REVIEW_FROM_PASSWORD = sys_config.get('mail_smtp_password') if self.MAIL_REVIEW_SMTP_PORT: self.MAIL_REVIEW_SMTP_PORT = int(self.MAIL_REVIEW_SMTP_PORT) elif self.MAIL_SSL: self.MAIL_REVIEW_SMTP_PORT = 465 else: self.MAIL_REVIEW_SMTP_PORT = 25
def authenticate_entry(request): """接收http请求,然后把请求中的用户名密码传给loginAuthenticate去验证""" username = request.POST.get('username') password = request.POST.get('password') result = login_authenticate(username, password) if result['status'] == 0: # 开启LDAP的认证通过后更新用户密码 if settings.ENABLE_LDAP: try: Users.objects.get(username=username) except Exception: insert_info = Users() insert_info.password = make_password(password) insert_info.save() else: replace_info = Users.objects.get(username=username) replace_info.password = make_password(password) replace_info.save() # 添加到默认组 default_auth_group = SysConfig().sys_config.get( 'default_auth_group', '') try: user = Users.objects.get(username=username) group = Group.objects.get(name=default_auth_group) user.groups.add(group) except Exception: logger.error( '无name为{}的权限组,无法默认关联,请到系统设置进行配置'.format(default_auth_group)) # 调用了django内置登录方法,防止管理后台二次登录 user = authenticate(username=username, password=password) if user: login(request, user) result = {'status': 0, 'msg': 'ok', 'data': None} return HttpResponse(json.dumps(result), content_type='application/json')
def execute_workflow(self, workflow): """执行上线单,返回Review set""" # 判断实例是否只读 read_only = self.query(sql='select @@read_only;').rows[0][0] if read_only: result = ReviewSet( full_sql=workflow.sqlworkflowcontent.sql_content, rows=[ReviewResult(id=1, errlevel=2, stagestatus='Execute Failed', errormessage='实例read_only=1,禁止执行变更语句!', sql=workflow.sqlworkflowcontent.sql_content)]) result.error = '实例read_only=1,禁止执行变更语句!', return result # 原生执行 if workflow.is_manual == 1: return self.execute(db_name=workflow.db_name, sql=workflow.sqlworkflowcontent.sql_content) # inception执行 elif not SysConfig().get('inception'): inception_engine = GoInceptionEngine() return inception_engine.execute(workflow) else: inception_engine = InceptionEngine() return inception_engine.execute(workflow)
def process(request): instance_name = request.POST.get('instance_name') command_type = request.POST.get('command_type') base_sql = "select id, user, host, db, command, time, state, ifnull(info,'') as info from information_schema.processlist" # 判断是RDS还是其他实例 if len( AliyunRdsConfig.objects.filter(instance_name=instance_name, is_enable=1)) > 0: if SysConfig().sys_config.get('aliyun_rds_manage'): result = aliyun_process_status(request) else: raise Exception('未开启rds管理,无法查看rds数据!') else: if command_type == 'All': sql = base_sql + ";" elif command_type == 'Not Sleep': sql = "{} where command<>'Sleep';".format(base_sql) else: sql = "{} where command= '{}';".format(base_sql, command_type) processlist = Dao(instance_name=instance_name).mysql_query( 'information_schema', sql) column_list = processlist['column_list'] rows = [] for row in processlist['rows']: row_info = {} for row_index, row_item in enumerate(row): row_info[column_list[row_index]] = row_item rows.append(row_info) result = {'status': 0, 'msg': 'ok', 'data': rows} # 返回查询结果 return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True), content_type='application/json')
def __send(msg_title, msg_content, msg_to, msg_cc=None, **kwargs): """ 按照通知配置发送通知消息 :param msg_title: 通知标题 :param msg_content: 通知内容 :param msg_to: 通知人user list :return: """ sys_config = SysConfig() msg_sender = MsgSender() msg_cc = msg_cc if msg_cc else [] webhook_url = kwargs.get('webhook_url') msg_to_email = [user.email for user in msg_to if user.email] msg_cc_email = [user.email for user in msg_cc if user.email] msg_to_ding_user = [user.ding_user_id for user in chain(msg_to, msg_cc) if user.ding_user_id] msg_to_wx_user = [user.wx_user_id if user.wx_user_id else user.username for user in chain(msg_to, msg_cc)] if sys_config.get('mail'): msg_sender.send_email(msg_title, msg_content, msg_to_email, list_cc_addr=msg_cc_email) if sys_config.get('ding') and webhook_url: msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content) if sys_config.get('ding_to_person'): msg_sender.send_ding2user(msg_to_ding_user, msg_title + '\n' + msg_content) if sys_config.get('wx'): msg_sender.send_wx2user(msg_title + '\n' + msg_content, msg_to_wx_user)
def init_user(user): """ 给用户关联默认资源组和权限组 :param user: :return: """ # 添加到默认权限组 default_auth_group = SysConfig().get('default_auth_group', '') if default_auth_group: default_auth_group = default_auth_group.split(',') [ user.groups.add(group) for group in Group.objects.filter(name__in=default_auth_group) ] # 添加到默认资源组 default_resource_group = SysConfig().get('default_resource_group', '') if default_resource_group: default_resource_group = default_resource_group.split(',') [ user.resource_group.add(group) for group in ResourceGroup.objects.filter( group_name__in=default_resource_group) ]
def query(request): """ 获取SQL查询结果 :param request: :return: """ instance_name = request.POST.get('instance_name') sql_content = request.POST.get('sql_content') db_name = request.POST.get('db_name') limit_num = int(request.POST.get('limit_num', 0)) schema_name = request.POST.get('schema_name', None) user = request.user result = {'status': 0, 'msg': 'ok', 'data': {}} try: instance = Instance.objects.get(instance_name=instance_name) except Instance.DoesNotExist: result['status'] = 1 result['msg'] = '实例不存在' return HttpResponse(json.dumps(result), content_type='application/json') # 服务器端参数验证 if None in [sql_content, db_name, instance_name, limit_num]: result['status'] = 1 result['msg'] = '页面提交参数可能为空' return HttpResponse(json.dumps(result), content_type='application/json') try: config = SysConfig() # 查询前的检查,禁用语句检查,语句切分 query_engine = get_engine(instance=instance) query_check_info = query_engine.query_check(db_name=db_name, sql=sql_content) if query_check_info.get('bad_query'): # 引擎内部判断为 bad_query result['status'] = 1 result['msg'] = query_check_info.get('msg') return HttpResponse(json.dumps(result), content_type='application/json') if query_check_info.get( 'has_star') and config.get('disable_star') is True: # 引擎内部判断为有 * 且禁止 * 选项打开 result['status'] = 1 result['msg'] = query_check_info.get('msg') return HttpResponse(json.dumps(result), content_type='application/json') sql_content = query_check_info['filtered_sql'] # 查询权限校验,并且获取limit_num priv_check_info = query_priv_check(user, instance, db_name, sql_content, limit_num) if priv_check_info['status'] == 0: limit_num = priv_check_info['data']['limit_num'] priv_check = priv_check_info['data']['priv_check'] else: result['status'] = 1 result['msg'] = priv_check_info['msg'] return HttpResponse(json.dumps(result), content_type='application/json') # explain的limit_num设置为0 limit_num = 0 if re.match(r"^explain", sql_content.lower()) else limit_num # 对查询sql增加limit限制或者改写语句 sql_content = query_engine.filter_sql(sql=sql_content, limit_num=limit_num) # 先获取查询连接,用于后面查询复用连接以及终止会话 query_engine.get_connection(db_name=db_name) thread_id = query_engine.thread_id max_execution_time = int(config.get('max_execution_time', 60)) # 执行查询语句,并增加一个定时终止语句的schedule,timeout=max_execution_time if thread_id: schedule_name = f'query-{time.time()}' run_date = (datetime.datetime.now() + datetime.timedelta(seconds=max_execution_time)) add_kill_conn_schedule(schedule_name, run_date, instance.id, thread_id) with FuncTimer() as t: # 获取主从延迟信息 seconds_behind_master = query_engine.seconds_behind_master if instance.db_type == 'pgsql': # TODO 此处判断待优化,请在 修改传参方式后去除 query_result = query_engine.query(db_name, sql_content, limit_num, schema_name=schema_name) else: query_result = query_engine.query(db_name, sql_content, limit_num) query_result.query_time = t.cost # 返回查询结果后删除schedule if thread_id: del_schedule(schedule_name) # 查询异常 if query_result.error: result['status'] = 1 result['msg'] = query_result.error # 数据脱敏,仅对查询无错误的结果集进行脱敏,并且按照query_check配置是否返回 elif config.get('data_masking'): try: with FuncTimer() as t: masking_result = query_engine.query_masking( db_name, sql_content, query_result) masking_result.mask_time = t.cost # 脱敏出错 if masking_result.error: # 开启query_check,直接返回异常,禁止执行 if config.get('query_check'): result['status'] = 1 result['msg'] = f'数据脱敏异常:{masking_result.error}' # 关闭query_check,忽略错误信息,返回未脱敏数据,权限校验标记为跳过 else: logger.warning( f'数据脱敏异常,按照配置放行,查询语句:{sql_content},错误信息:{masking_result.error}' ) query_result.error = None result['data'] = query_result.__dict__ # 正常脱敏 else: result['data'] = masking_result.__dict__ except Exception as msg: # 抛出未定义异常,并且开启query_check,直接返回异常,禁止执行 if config.get('query_check'): result['status'] = 1 result['msg'] = f'数据脱敏异常,请联系管理员,错误信息:{msg}' # 关闭query_check,忽略错误信息,返回未脱敏数据,权限校验标记为跳过 else: logger.warning( f'数据脱敏异常,按照配置放行,查询语句:{sql_content},错误信息:{msg}') query_result.error = None result['data'] = query_result.__dict__ # 无需脱敏的语句 else: result['data'] = query_result.__dict__ # 仅将成功的查询语句记录存入数据库 if not query_result.error: result['data']['seconds_behind_master'] = seconds_behind_master if int(limit_num) == 0: limit_num = int(query_result.affected_rows) else: limit_num = min(int(limit_num), int(query_result.affected_rows)) query_log = QueryLog(username=user.username, user_display=user.display, db_name=db_name, instance_name=instance.instance_name, sqllog=sql_content, effect_row=limit_num, cost_time=query_result.query_time, priv_check=priv_check, hit_rule=query_result.mask_rule_hit, masking=query_result.is_masked) # 防止查询超时 try: query_log.save() except OperationalError: connection.close() query_log.save() except Exception as e: logger.error( f'查询异常报错,查询语句:{sql_content}\n,错误信息:{traceback.format_exc()}') result['status'] = 1 result['msg'] = f'查询异常报错,错误信息:{e}' return HttpResponse(json.dumps(result), content_type='application/json') # 返回查询结果 try: return HttpResponse(json.dumps(result, cls=ExtendJSONEncoderFTime, bigint_as_string=True), content_type='application/json') # 虽然能正常返回,但是依然会乱码 except UnicodeDecodeError: return HttpResponse(json.dumps(result, default=str, bigint_as_string=True, encoding='latin1'), content_type='application/json')
def test_set_other_data(self): archer_config = SysConfig() archer_config.set('other_config', 'testvalue3') self.assertEqual(archer_config.sys_config['other_config'], 'testvalue3')
def setUp(self): archer_config = SysConfig() self.smtp_server = 'test_smtp_server' self.smtp_user = '******' self.smtp_password = '******' self.smtp_port = 1234 self.smtp_ssl = True archer_config.set('mail_smtp_server', self.smtp_server) archer_config.set('mail_smtp_user', self.smtp_user) archer_config.set('mail_smtp_password', self.smtp_password) archer_config.set('mail_smtp_port', self.smtp_port) archer_config.set('mail_ssl', self.smtp_ssl)
def test_set_bool_transform(self): archer_config = SysConfig() archer_config.set('boolconfig3', False) self.assertEqual(archer_config.sys_config['boolconfig3'], False)
def test_get_bool_transform(self): bool_config = json.dumps([{'key': 'boolconfig2', 'value': 'false'}]) archer_config = SysConfig() archer_config.replace(bool_config) self.assertEqual(archer_config.sys_config['boolconfig2'], False)
def tearDown(self): archer_config = SysConfig() archer_config.set('mail_smtp_server', '') archer_config.set('mail_smtp_user', '') archer_config.set('mail_smtp_password', '') archer_config.set('mail_smtp_port', '') archer_config.set('mail_ssl', '')
def execute(request): workflow_id = request.POST['workflow_id'] if workflow_id == '' or workflow_id is None: context = {'errMsg': 'workflow_id参数为空.'} return render(request, 'error.html', context) workflow_id = int(workflow_id) workflow_detail = SqlWorkflow.objects.get(id=workflow_id) instance_name = workflow_detail.instance_name db_name = workflow_detail.db_name url = get_detail_url(request, workflow_id) if can_execute(request.user, workflow_id) is False: context = {'errMsg': '你无权操作当前工单!'} return render(request, 'error.html', context) # 判断是否高危SQL,禁止执行 if SysConfig().sys_config.get('critical_ddl_regex', '') != '': if InceptionDao().critical_ddl(workflow_detail.sql_content): context = {'errMsg': '高危语句,禁止执行!'} return render(request, 'error.html', context) # 将流程状态修改为执行中,并更新reviewok_time字段 workflow_detail.status = Const.workflowStatus['executing'] workflow_detail.reviewok_time = timezone.now() workflow_detail.save() # 判断是通过inception执行还是直接执行,is_manual=0则通过inception执行,is_manual=1代表inception审核不通过,需要直接执行 if workflow_detail.is_manual == 0: # 执行之前重新split并check一遍,更新SHA1缓存;因为如果在执行中,其他进程去做这一步操作的话,会导致inception core dump挂掉 try: split_review_result = InceptionDao( instance_name=instance_name).sqlauto_review( workflow_detail.sql_content, db_name, is_split='yes') except Exception as msg: logger.error(traceback.format_exc()) context = {'errMsg': msg} return render(request, 'error.html', context) workflow_detail.review_content = json.dumps(split_review_result) try: workflow_detail.save() except Exception: # 关闭后重新获取连接,防止超时 connection.close() workflow_detail.save() # 采取异步回调的方式执行语句,防止出现持续执行中的异常 t = Thread(target=execute_call_back, args=(workflow_id, instance_name, url)) t.start() else: # 采取异步回调的方式执行语句,防止出现持续执行中的异常 t = Thread(target=execute_skipinc_call_back, args=(workflow_id, instance_name, db_name, workflow_detail.sql_content, url)) t.start() # 删除定时执行job if workflow_detail.status == Const.workflowStatus['timingtask']: job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id) del_sqlcronjob(job_id) # 增加工单日志 # 获取audit_id audit_id = Workflow.audit_info_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id workflowOb.add_workflow_log(audit_id=audit_id, operation_type=5, operation_type_desc='执行工单', operation_info="人工操作执行", operator=request.user.username, operator_display=request.user.display) return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def autoreview(request): workflow_id = request.POST.get('workflow_id') sql_content = request.POST['sql_content'] workflow_title = request.POST['workflow_name'] group_name = request.POST['group_name'] group_id = SqlGroup.objects.get(group_name=group_name).group_id instance_name = request.POST['instance_name'] db_name = request.POST.get('db_name') is_backup = request.POST['is_backup'] notify_users = request.POST.getlist('notify_users') # 服务器端参数验证 if sql_content is None or workflow_title is None or instance_name is None or db_name is None or is_backup is None: context = {'errMsg': '页面提交参数可能为空'} return render(request, 'error.html', context) # 验证组权限(用户是否在该组、该组是否有指定实例) try: user_instances(request.user, 'master').get(instance_name=instance_name) except Exception: context = {'errMsg': '你所在组未关联该实例!'} return render(request, 'error.html', context) # # 删除注释语句 # sql_content = ''.join( # map(lambda x: re.compile(r'(^--.*|^/\*.*\*/;\s*$)').sub('', x, count=1), # sql_content.splitlines(1))).strip() # # 去除空行 # sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content) sql_content = sql_content.strip() if sql_content[-1] != ";": context = {'errMsg': "SQL语句结尾没有以;结尾,请后退重新修改并提交!"} return render(request, 'error.html', context) # 交给inception进行自动审核 try: inception_result = InceptionDao( instance_name=instance_name).sqlauto_review(sql_content, db_name) except Exception as msg: context = {'errMsg': msg} return render(request, 'error.html', context) if inception_result is None or len(inception_result) == 0: context = {'errMsg': 'inception返回的结果集为空!可能是SQL语句有语法错误'} return render(request, 'error.html', context) # 要把result转成JSON存进数据库里,方便SQL单子详细信息展示 json_result = json.dumps(inception_result) # 遍历result,看是否有任何自动审核不通过的地方,并且按配置确定是标记审核不通过还是放行,放行的可以在工单内跳过inception直接执行 sys_config = SysConfig().sys_config is_manual = 0 workflow_status = Const.workflowStatus['manreviewing'] for row in inception_result: # 1表示警告,不影响执行 if row[2] == 1 and sys_config.get('auto_review_wrong', '') == '1': workflow_status = Const.workflowStatus['autoreviewwrong'] break # 2表示严重错误,或者inception不支持的语法,标记手工执行,可以跳过inception直接执行 elif row[2] == 2: is_manual = 1 if sys_config.get('auto_review_wrong', '') in ('', '1', '2'): workflow_status = Const.workflowStatus['autoreviewwrong'] break elif re.match(r"\w*comments\w*", row[4]): is_manual = 1 if sys_config.get('auto_review_wrong', '') in ('', '1', '2'): workflow_status = Const.workflowStatus['autoreviewwrong'] break # 判断SQL是否包含DDL语句,SQL语法 1、DDL,2、DML sql_syntax = 2 for row in sql_content.strip(';').split(';'): if re.match(r"^alter|^create|^drop|^truncate|^rename", row.strip().lower()): sql_syntax = 1 break # 调用工作流生成工单 # 使用事务保持数据一致性 try: with transaction.atomic(): # 存进数据库里 engineer = request.user.username if not workflow_id: sql_workflow = SqlWorkflow() sql_workflow.create_time = timezone.now() else: sql_workflow = SqlWorkflow.objects.get(id=int(workflow_id)) sql_workflow.workflow_name = workflow_title sql_workflow.group_id = group_id sql_workflow.group_name = group_name sql_workflow.engineer = engineer sql_workflow.engineer_display = request.user.display sql_workflow.audit_auth_groups = Workflow.audit_settings( group_id, WorkflowDict.workflow_type['sqlreview']) sql_workflow.status = workflow_status sql_workflow.is_backup = is_backup sql_workflow.review_content = json_result sql_workflow.instance_name = instance_name sql_workflow.db_name = db_name sql_workflow.sql_content = sql_content sql_workflow.execute_result = '' sql_workflow.is_manual = is_manual sql_workflow.audit_remark = '' sql_workflow.sql_syntax = sql_syntax sql_workflow.save() workflow_id = sql_workflow.id # 自动审核通过了,才调用工作流 if workflow_status == Const.workflowStatus['manreviewing']: # 调用工作流插入审核信息, 查询权限申请workflow_type=2 # 抄送通知人 list_cc_addr = [ email['email'] for email in Users.objects.filter( username__in=notify_users).values('email') ] workflowOb.addworkflowaudit( request, WorkflowDict.workflow_type['sqlreview'], workflow_id, list_cc_addr=list_cc_addr) except Exception as msg: logger.error(traceback.format_exc()) context = {'errMsg': msg} return render(request, 'error.html', context) return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))