def execute(request): """ 执行SQL :param request: :return: """ # 校验多个权限 if not (request.user.has_perm('sql.sql_execute') or request.user.has_perm('sql.sql_execute_for_resource_group')): raise PermissionDenied workflow_id = int(request.POST.get('workflow_id', 0)) if workflow_id == 0: context = {'errMsg': 'workflow_id参数为空.'} return render(request, 'error.html', context) if can_execute(request.user, workflow_id) is False: context = {'errMsg': '你无权操作当前工单!'} return render(request, 'error.html', context) if on_correct_time_period(workflow_id) is False: context = {'errMsg': '不在可执行时间范围内,如果需要修改执行时间请重新提交工单!'} return render(request, 'error.html', context) # 根据执行模式进行对应修改 mode = request.POST.get('mode') # 交由系统执行 if mode == "auto": # 删除定时执行任务 schedule_name = f"sqlreview-timing-{workflow_id}" del_schedule(schedule_name) # 加入执行队列 async_task('sql.utils.execute_sql.execute', workflow_id, request.user, hook='sql.utils.execute_sql.execute_callback', timeout=-1, task_name=f'sqlreview-execute-{workflow_id}') # 线下手工执行 elif mode == "manual": # 将流程状态修改为执行结束 SqlWorkflow(id=workflow_id, status="workflow_finish", finish_time=datetime.datetime.now() ).save(update_fields=['status', 'finish_time']) # 增加工单日志 audit_id = Audit.detail_by_workflow_id(workflow_id=workflow_id, workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id Audit.add_log(audit_id=audit_id, operation_type=6, operation_type_desc='手工工单', operation_info='确认手工执行结束', operator=request.user.username, operator_display=request.user.display) return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))
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 cancel(request): """ 终止流程 :param request: :return: """ workflow_id = int(request.POST.get('workflow_id', 0)) if workflow_id == 0: context = {'errMsg': 'workflow_id参数为空.'} return render(request, 'error.html', context) workflow_detail = SqlWorkflow.objects.get(id=workflow_id) audit_remark = request.POST.get('cancel_remark') if audit_remark is None: context = {'errMsg': '终止原因不能为空'} return render(request, 'error.html', context) user = request.user if can_cancel(request.user, workflow_id) is False: context = {'errMsg': '你无权操作当前工单!'} return render(request, 'error.html', context) # 使用事务保持数据一致性 try: with transaction.atomic(): # 调用工作流接口取消或者驳回 audit_id = Audit.detail_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id # 仅待审核的需要调用工作流,审核通过的不需要 if workflow_detail.status != 'workflow_manreviewing': # 增加工单日志 if user.username == workflow_detail.engineer: Audit.add_log( audit_id=audit_id, operation_type=3, operation_type_desc='取消执行', operation_info="取消原因:{}".format(audit_remark), operator=request.user.username, operator_display=request.user.display) else: Audit.add_log( audit_id=audit_id, operation_type=2, operation_type_desc='审批不通过', operation_info="审批备注:{}".format(audit_remark), operator=request.user.username, operator_display=request.user.display) else: if user.username == workflow_detail.engineer: Audit.audit(audit_id, WorkflowDict.workflow_status['audit_abort'], user.username, audit_remark) # 非提交人需要校验审核权限 elif user.has_perm('sql.sql_review'): Audit.audit(audit_id, WorkflowDict.workflow_status['audit_reject'], user.username, audit_remark) else: raise PermissionDenied # 删除定时执行task if workflow_detail.status == 'workflow_timingtask': schedule_name = f"sqlreview-timing-{workflow_id}" del_schedule(schedule_name) # 将流程状态修改为人工终止流程 workflow_detail.status = 'workflow_abort' workflow_detail.save() except Exception as msg: logger.error(f"取消工单报错,错误信息:{traceback.format_exc()}") context = {'errMsg': msg} return render(request, 'error.html', context) else: # 发送取消、驳回通知 audit_detail = Audit.detail_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict.workflow_type['sqlreview']) if audit_detail.current_status in ( WorkflowDict.workflow_status['audit_abort'], WorkflowDict.workflow_status['audit_reject']): async_task(notify_for_audit, audit_id=audit_detail.audit_id, audit_remark=audit_remark, timeout=60, task_name=f'sqlreview-cancel-{workflow_id}') return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def post(self, request): # 参数验证 serializer = ExecuteWorkflowSerializer(data=request.data) if not serializer.is_valid(): return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST) workflow_type = request.data['workflow_type'] workflow_id = request.data['workflow_id'] # 执行SQL上线工单 if workflow_type == 2: mode = request.data['mode'] engineer = request.data['engineer'] user = Users.objects.get(username=engineer) # 校验多个权限 if not (user.has_perm('sql.sql_execute') or user.has_perm('sql.sql_execute_for_resource_group')): raise serializers.ValidationError({"errors": "你无权执行当前工单!"}) if can_execute(user, workflow_id) is False: raise serializers.ValidationError({"errors": "你无权执行当前工单!"}) if on_correct_time_period(workflow_id) is False: raise serializers.ValidationError( {"errors": "不在可执行时间范围内,如果需要修改执行时间请重新提交工单!"}) # 获取审核信息 audit_id = Audit.detail_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id # 交由系统执行 if mode == "auto": # 修改工单状态为排队中 SqlWorkflow( id=workflow_id, status="workflow_queuing").save(update_fields=['status']) # 删除定时执行任务 schedule_name = f"sqlreview-timing-{workflow_id}" del_schedule(schedule_name) # 加入执行队列 async_task('sql.utils.execute_sql.execute', workflow_id, user, hook='sql.utils.execute_sql.execute_callback', timeout=-1, task_name=f'sqlreview-execute-{workflow_id}') # 增加工单日志 Audit.add_log(audit_id=audit_id, operation_type=5, operation_type_desc='执行工单', operation_info='工单执行排队中', operator=user.username, operator_display=user.display) # 线下手工执行 elif mode == "manual": # 将流程状态修改为执行结束 SqlWorkflow(id=workflow_id, status="workflow_finish", finish_time=datetime.datetime.now()).save( update_fields=['status', 'finish_time']) # 增加工单日志 Audit.add_log(audit_id=audit_id, operation_type=6, operation_type_desc='手工工单', operation_info='确认手工执行结束', operator=user.username, operator_display=user.display) # 开启了Execute阶段通知参数才发送消息通知 sys_config = SysConfig() is_notified = 'Execute' in sys_config.get('notify_phase_control').split(',') \ if sys_config.get('notify_phase_control') else True if is_notified: notify_for_execute(SqlWorkflow.objects.get(id=workflow_id)) # 执行数据归档工单 elif workflow_type == 3: async_task('sql.archiver.archive', workflow_id, timeout=-1, task_name=f'archive-{workflow_id}') return Response({'msg': '开始执行,执行结果请到工单详情页查看'})
def post(self, request): # 参数验证 serializer = AuditWorkflowSerializer(data=request.data) if not serializer.is_valid(): return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST) audit_type = request.data['audit_type'] workflow_type = request.data['workflow_type'] workflow_id = request.data['workflow_id'] audit_remark = request.data['audit_remark'] engineer = request.data['engineer'] user = Users.objects.get(username=engineer) # 审核查询权限申请 if workflow_type == 1: audit_status = 1 if audit_type == 'pass' else 2 if audit_remark is None: audit_remark = '' if Audit.can_review(user, workflow_id, workflow_type) is False: raise serializers.ValidationError({"errors": "你无权操作当前工单!"}) # 使用事务保持数据一致性 try: with transaction.atomic(): audit_id = Audit.detail_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict.workflow_type['query'] ).audit_id # 调用工作流接口审核 audit_result = Audit.audit(audit_id, audit_status, user.username, audit_remark) # 按照审核结果更新业务表审核状态 audit_detail = Audit.detail(audit_id) if audit_detail.workflow_type == WorkflowDict.workflow_type[ 'query']: # 更新业务表审核状态,插入权限信息 _query_apply_audit_call_back( audit_detail.workflow_id, audit_result['data']['workflow_status']) except Exception as msg: logger.error(traceback.format_exc()) raise serializers.ValidationError({'errors': msg}) else: # 消息通知 async_task(notify_for_audit, audit_id=audit_id, audit_remark=audit_remark, timeout=60, task_name=f'query-priv-audit-{workflow_id}') return Response({ 'msg': 'passed' }) if audit_type == 'pass' else Response({'msg': 'canceled'}) # 审核SQL上线申请 elif workflow_type == 2: # SQL上线申请通过 if audit_type == 'pass': # 权限验证 if Audit.can_review(user, workflow_id, workflow_type) is False: raise serializers.ValidationError({"errors": "你无权操作当前工单!"}) # 使用事务保持数据一致性 try: with transaction.atomic(): # 调用工作流接口审核 audit_id = Audit.detail_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict. workflow_type['sqlreview']).audit_id audit_result = Audit.audit( audit_id, WorkflowDict.workflow_status['audit_success'], user.username, audit_remark) # 按照审核结果更新业务表审核状态 if audit_result['data'][ 'workflow_status'] == WorkflowDict.workflow_status[ 'audit_success']: # 将流程状态修改为审核通过 SqlWorkflow(id=workflow_id, status='workflow_review_pass').save( update_fields=['status']) except Exception as msg: logger.error(traceback.format_exc()) raise serializers.ValidationError({'errors': msg}) else: # 开启了Pass阶段通知参数才发送消息通知 sys_config = SysConfig() is_notified = 'Pass' in sys_config.get('notify_phase_control').split(',') \ if sys_config.get('notify_phase_control') else True if is_notified: async_task(notify_for_audit, audit_id=audit_id, audit_remark=audit_remark, timeout=60, task_name=f'sqlreview-pass-{workflow_id}') return Response({'msg': 'passed'}) # SQL上线申请驳回/取消 elif audit_type == 'cancel': workflow_detail = SqlWorkflow.objects.get(id=workflow_id) if audit_remark is None: raise serializers.ValidationError({"errors": "终止原因不能为空"}) if can_cancel(user, workflow_id) is False: raise serializers.ValidationError({"errors": "你无权操作当前工单!"}) # 使用事务保持数据一致性 try: with transaction.atomic(): # 调用工作流接口取消或者驳回 audit_id = Audit.detail_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict. workflow_type['sqlreview']).audit_id # 仅待审核的需要调用工作流,审核通过的不需要 if workflow_detail.status != 'workflow_manreviewing': # 增加工单日志 if user.username == workflow_detail.engineer: Audit.add_log(audit_id=audit_id, operation_type=3, operation_type_desc='取消执行', operation_info="取消原因:{}".format( audit_remark), operator=user.username, operator_display=user.display) else: Audit.add_log(audit_id=audit_id, operation_type=2, operation_type_desc='审批不通过', operation_info="审批备注:{}".format( audit_remark), operator=user.username, operator_display=user.display) else: if user.username == workflow_detail.engineer: Audit.audit( audit_id, WorkflowDict. workflow_status['audit_abort'], user.username, audit_remark) # 非提交人需要校验审核权限 elif user.has_perm('sql.sql_review'): Audit.audit( audit_id, WorkflowDict. workflow_status['audit_reject'], user.username, audit_remark) else: raise serializers.ValidationError( {"errors": "Permission Denied"}) # 删除定时执行task if workflow_detail.status == 'workflow_timingtask': schedule_name = f"sqlreview-timing-{workflow_id}" del_schedule(schedule_name) # 将流程状态修改为人工终止流程 workflow_detail.status = 'workflow_abort' workflow_detail.save() except Exception as msg: logger.error(f"取消工单报错,错误信息:{traceback.format_exc()}") raise serializers.ValidationError({'errors': msg}) else: # 发送取消、驳回通知,开启了Cancel阶段通知参数才发送消息通知 sys_config = SysConfig() is_notified = 'Cancel' in sys_config.get('notify_phase_control').split(',') \ if sys_config.get('notify_phase_control') else True if is_notified: audit_detail = Audit.detail_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict. workflow_type['sqlreview']) if audit_detail.current_status in ( WorkflowDict.workflow_status['audit_abort'], WorkflowDict.workflow_status['audit_reject']): async_task( notify_for_audit, audit_id=audit_detail.audit_id, audit_remark=audit_remark, timeout=60, task_name=f'sqlreview-cancel-{workflow_id}') return Response({'msg': 'canceled'}) # 审核数据归档申请 elif workflow_type == 3: audit_status = 1 if audit_type == 'pass' else 2 if audit_remark is None: audit_remark = '' if Audit.can_review(user, workflow_id, workflow_type) is False: raise serializers.ValidationError({"errors": "你无权操作当前工单!"}) # 使用事务保持数据一致性 try: with transaction.atomic(): audit_id = Audit.detail_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict.workflow_type['archive'] ).audit_id # 调用工作流插入审核信息,更新业务表审核状态 audit_status = Audit.audit( audit_id, audit_status, user.username, audit_remark)['data']['workflow_status'] ArchiveConfig( id=workflow_id, status=audit_status, state=True if audit_status == WorkflowDict.workflow_status['audit_success'] else False).save(update_fields=['status', 'state']) except Exception as msg: logger.error(traceback.format_exc()) raise serializers.ValidationError({'errors': msg}) else: # 消息通知 async_task(notify_for_audit, audit_id=audit_id, audit_remark=audit_remark, timeout=60, task_name=f'archive-audit-{workflow_id}') return Response({ 'msg': 'passed' }) if audit_type == 'pass' else Response({'msg': 'canceled'})