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'})
