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)
# 将流程状态修改为执行中
SqlWorkflow(id=workflow_id,
status='workflow_executing').save(update_fields=['status'])
# 增加工单日志
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=5,
operation_type_desc='执行工单',
operation_info="人工操作执行",
operator=request.user.username,
operator_display=request.user.display)
# 加入执行队列
async_task('sql.utils.execute_sql.execute',
workflow_id,
hook='sql.utils.execute_sql.execute_callback',
timeout=-1)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def queryprivaudit(request):
# 获取用户信息
user = request.user
apply_id = int(request.POST['apply_id'])
audit_status = int(request.POST['audit_status'])
audit_remark = request.POST.get('audit_remark')
if audit_remark is None:
audit_remark = ''
if Audit.can_review(request.user, apply_id, 1) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
audit_id = Audit.detail_by_workflow_id(
workflow_id=apply_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_audit_call_back(audit_detail.workflow_id,
audit_result['data']['workflow_status'])
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(
reverse('sql:queryapplydetail', args=(apply_id, )))
def workflowsdetail(request, audit_id):
"""待办详情"""
# 按照不同的workflow_type返回不同的详情
audit_detail = Audit.detail(audit_id)
if audit_detail.workflow_type == WorkflowDict.workflow_type['query']:
return HttpResponseRedirect(
reverse('sql:queryapplydetail', args=(audit_detail.workflow_id, )))
elif audit_detail.workflow_type == WorkflowDict.workflow_type['sqlreview']:
return HttpResponseRedirect(
reverse('sql:detail', args=(audit_detail.workflow_id, )))
elif audit_detail.workflow_type == WorkflowDict.workflow_type['archive']:
return HttpResponseRedirect(
reverse('sql:archive_detail', args=(audit_detail.workflow_id, )))
def changeauditors(request):
auth_groups = request.POST.get('audit_auth_groups')
group_name = request.POST.get('group_name')
workflow_type = request.POST.get('workflow_type')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 调用工作流修改审核配置
group_id = ResourceGroup.objects.get(group_name=group_name).group_id
audit_auth_groups = [
str(Group.objects.get(name=auth_group).id)
for auth_group in auth_groups.split(',')
]
try:
Audit.change_settings(group_id, workflow_type,
','.join(audit_auth_groups))
except Exception as msg:
logger.error(traceback.format_exc())
result['msg'] = str(msg)
result['status'] = 1
# 返回结果
return HttpResponse(json.dumps(result), content_type='application/json')
def queryapplydetail(request, apply_id):
workflow_detail = QueryPrivilegesApply.objects.get(apply_id=apply_id)
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Audit.review_info(apply_id, 1)
# 是否可审核
is_can_review = Audit.can_review(request.user, apply_id, 1)
# 获取审核日志
if workflow_detail.status == 2:
try:
audit_id = Audit.detail_by_workflow_id(workflow_id=apply_id, workflow_type=1).audit_id
last_operation_info = Audit.logs(audit_id=audit_id).latest('id').operation_info
except Exception as e:
logger.debug(f'无审核日志记录，错误信息{e}')
last_operation_info = ''
else:
last_operation_info = ''
context = {'workflow_detail': workflow_detail, 'audit_auth_group': audit_auth_group,
'last_operation_info': last_operation_info, 'current_audit_auth_group': current_audit_auth_group,
'is_can_review': is_can_review}
return render(request, 'queryapplydetail.html', context)
def passed(request):
workflow_id = request.POST.get('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)
audit_remark = request.POST.get('audit_remark', '')
user = request.user
if Audit.can_review(request.user, workflow_id, 2) 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
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']:
# 将流程状态修改为审核通过，并更新reviewok_time字段
workflow_detail.status = 'workflow_review_pass'
workflow_detail.reviewok_time = timezone.now()
workflow_detail.audit_remark = audit_remark
workflow_detail.save()
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
else:
# 消息通知
async_task(notify_for_audit, audit_id=audit_id, audit_remark=audit_remark, timeout=60)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))
def passed(request):
"""
审核通过，不执行
:param request:
:return:
"""
workflow_id = int(request.POST.get('workflow_id', 0))
audit_remark = request.POST.get('audit_remark', '')
if workflow_id == 0:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
user = request.user
if Audit.can_review(user, workflow_id, 2) 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
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(f"审核工单报错，错误信息：{traceback.format_exc()}")
context = {'errMsg': msg}
return render(request, 'error.html', context)
else:
# 消息通知
async_task(notify_for_audit, audit_id=audit_id, audit_remark=audit_remark, timeout=60)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))
def execute_callback(task):
"""异步任务的回调, 将结果填入数据库等等
使用django-q的hook, 传入参数为整个task
task.result 是真正的结果
"""
workflow_id = task.args[0]
workflow = SqlWorkflow.objects.get(id=workflow_id)
workflow.finish_time = task.stopped
if not task.success:
# 不成功会返回字符串
workflow.status = 'workflow_exception'
elif task.result.warning or task.result.error:
workflow.status = 'workflow_exception'
execute_result = task.result
else:
workflow.status = 'workflow_finish'
execute_result = task.result
# resultset 的内部方法 json()
workflow.execute_result = execute_result.json()
workflow.audit_remark = ''
workflow.save()
# 增加工单日志
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='执行结果：{}'.format(
workflow.get_status_display()),
operator='',
operator_display='系统')
# 发送消息
notify_for_execute(workflow)
def execute(workflow_id):
"""为延时或异步任务准备的execute, 传入工单ID即可"""
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
# 只有执行中和定时执行的数据才可以继续执行
if workflow_detail.status not in [
'workflow_executing', 'workflow_timingtask'
]:
raise Exception('工单状态不正确，禁止执行！')
# 给定时执行的工单增加执行日志
if workflow_detail.status == 'workflow_timingtask':
# 将工单状态修改为执行中
SqlWorkflow(id=workflow_id,
status='workflow_executing').save(update_fields=['status'])
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=5,
operation_type_desc='执行工单',
operation_info='系统定时执行',
operator='',
operator_display='系统')
execute_engine = get_engine(instance=workflow_detail.instance)
return execute_engine.execute_workflow(workflow=workflow_detail)
def can_cancel(user, workflow_id):
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
result = False
# 审核中的工单，审核人和提交人可终止
if workflow_detail.status == 'workflow_manreviewing':
from sql.utils.workflow_audit import Audit
if Audit.can_review(user, workflow_id,
2) or user.username == workflow_detail.engineer:
result = True
# 审核通过但未执行的工单，执行人可以打回
if workflow_detail.status in [
'workflow_review_pass', 'workflow_timingtask'
]:
result = True if can_execute(user, workflow_id) else False
return result
def global_info(request):
"""存放用户，菜单信息等."""
user = request.user
if user and user.is_authenticated:
# 获取待办数量
try:
todo = Audit.todo(user)
except Exception:
todo = 0
else:
todo = 0
return {
'todo': todo,
}
def workflowsdetail(request, audit_id):
"""待办详情"""
# 按照不同的workflow_type返回不同的详情
audit_detail = Audit.detail(audit_id)
if audit_detail.workflow_type == WorkflowDict.workflow_type['query']:
return HttpResponseRedirect(
reverse('sql:queryapplydetail', args=(audit_detail.workflow_id, )))
elif audit_detail.workflow_type == WorkflowDict.workflow_type['sqlreview']:
workflow = SqlWorkflow.objects.get(id=audit_detail.workflow_id)
if workflow.order_type == 'sqlcron_order':
return HttpResponseRedirect(
reverse('sql:sqlcrondetail',
args=(audit_detail.workflow_id, )))
return HttpResponseRedirect(
reverse('sql:detail', args=(audit_detail.workflow_id, )))
def global_info(request):
"""存放用户，菜单信息等."""
user = request.user
if user and user.is_authenticated:
# 获取待办数量
try:
todo = Audit.todo(user)
except Exception:
todo = 0
else:
todo = 0
return {
'todo': todo,
'sign_up_enabled': SysConfig().get('sign_up_enabled')
}
def global_info(request):
"""存放用户，菜单信息等."""
user = request.user
if user and user.is_authenticated:
# 获取待办数量
try:
todo = Audit.todo(user)
except Exception:
todo = 0
else:
todo = 0
watermark_enabled = SysConfig().get('watermark_enabled', False)
return {
'todo': todo,
'archery_version': display_version,
'watermark_enabled': watermark_enabled
}
def auditors(request):
"""获取资源组的审批流程"""
group_name = request.POST.get('group_name')
workflow_type = request.POST['workflow_type']
result = {
'status': 0,
'msg': 'ok',
'data': {
'auditors': '',
'auditors_display': ''
}
}
if group_name:
group_id = ResourceGroup.objects.get(group_name=group_name).group_id
audit_auth_groups = Audit.settings(group_id=group_id,
workflow_type=workflow_type)
else:
result['status'] = 1
result['msg'] = '参数错误'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 获取权限组名称
if audit_auth_groups:
# 校验配置
for auth_group_id in audit_auth_groups.split(','):
try:
Group.objects.get(id=auth_group_id)
except Exception:
result['status'] = 1
result['msg'] = '审批流程权限组不存在，请重新配置！'
return HttpResponse(json.dumps(result),
content_type='application/json')
audit_auth_groups_name = '->'.join([
Group.objects.get(id=auth_group_id).name
for auth_group_id in audit_auth_groups.split(',')
])
result['data']['auditors'] = audit_auth_groups
result['data']['auditors_display'] = audit_auth_groups_name
return HttpResponse(json.dumps(result), content_type='application/json')
def submit(request):
"""正式提交SQL, 此处生成工单"""
sql_content = request.POST['sql_content'].strip()
workflow_title = request.POST['workflow_name']
# 检查用户是否有权限涉及到资源组等， 比较复杂， 可以把检查权限改成一个独立的方法
# 工单表中可以考虑不存储资源组相关信息
# 工单和实例关联， 实例和资源组关联， 资源组和用户关联。（reply 一个实例可以被多个资源组关联，无法去除）
group_name = request.POST['group_name']
group_id = ResourceGroup.objects.get(group_name=group_name).group_id
instance_name = request.POST['instance_name']
instance = Instance.objects.get(instance_name=instance_name)
db_name = request.POST.get('db_name')
is_backup = True if request.POST['is_backup'] == 'True' else False
notify_users = request.POST.getlist('notify_users')
list_cc_addr = [
email['email'] for email in Users.objects.filter(
username__in=notify_users).values('email')
]
# 服务器端参数验证
if None in [sql_content, db_name, instance_name, db_name, is_backup]:
context = {'errMsg': '页面提交参数可能为空'}
return render(request, 'error.html', context)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(request.user, type='master',
db_type='all').get(instance_name=instance_name)
except instance.DoesNotExist:
context = {'errMsg': '你所在组未关联该实例！'}
return render(request, 'error.html', context)
# 再次交给engine进行检测，防止绕过
try:
check_engine = get_engine(instance=instance)
check_result = check_engine.execute_check(db_name=db_name,
sql=sql_content.strip())
except Exception as e:
context = {'errMsg': str(e)}
return render(request, 'error.html', context)
# 按照系统配置确定是自动驳回还是放行
sys_config = SysConfig()
auto_review_wrong = sys_config.get('auto_review_wrong',
'') # 1表示出现警告就驳回，2和空表示出现错误才驳回
workflow_status = 'workflow_manreviewing'
if check_result.warning_count > 0 and auto_review_wrong == '1':
workflow_status = 'workflow_autoreviewwrong'
elif check_result.error_count > 0 and auto_review_wrong in ('', '1', '2'):
workflow_status = 'workflow_autoreviewwrong'
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
sql_workflow = SqlWorkflow.objects.create(
workflow_name=workflow_title,
group_id=group_id,
group_name=group_name,
engineer=request.user.username,
engineer_display=request.user.display,
audit_auth_groups=Audit.settings(
group_id, WorkflowDict.workflow_type['sqlreview']),
status=workflow_status,
is_backup=is_backup,
instance=instance,
db_name=db_name,
is_manual=0,
syntax_type=check_result.syntax_type,
create_time=timezone.now())
SqlWorkflowContent.objects.create(
workflow=sql_workflow,
sql_content=sql_content,
review_content=check_result.json(),
execute_result='')
workflow_id = sql_workflow.id
# 自动审核通过了，才调用工作流
if workflow_status == 'workflow_manreviewing':
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
Audit.add(WorkflowDict.workflow_type['sqlreview'], workflow_id)
except Exception as msg:
logger.error(f"提交工单报错，错误信息：{traceback.format_exc()}")
context = {'errMsg': msg}
return render(request, 'error.html', context)
else:
# 自动审核通过才进行消息通知
if workflow_status == 'workflow_manreviewing':
# 获取审核信息
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
async_task(notify_for_audit,
audit_id=audit_id,
email_cc=list_cc_addr,
timeout=60)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
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)
# 获取审核信息
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
# 根据执行模式进行对应修改
mode = request.POST.get('mode')
# 交由系统执行
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,
request.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=request.user.username,
operator_display=request.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=request.user.username,
operator_display=request.user.display)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def submit(request):
"""正式提交SQL, 此处生成工单"""
sql_content = request.POST.get('sql_content').strip()
workflow_title = request.POST.get('workflow_name')
demand_url = request.POST.get('demand_url', '')
# 检查用户是否有权限涉及到资源组等， 比较复杂， 可以把检查权限改成一个独立的方法
group_name = request.POST.get('group_name')
group_id = ResourceGroup.objects.get(group_name=group_name).group_id
instance_name = request.POST.get('instance_name')
instance = Instance.objects.get(instance_name=instance_name)
db_name = request.POST.get('db_name')
is_backup = True if request.POST.get('is_backup') == 'True' else False
cc_users = request.POST.getlist('cc_users')
run_date_start = request.POST.get('run_date_start')
run_date_end = request.POST.get('run_date_end')
# 服务器端参数验证
if None in [
sql_content, db_name, instance_name, db_name, is_backup, demand_url
]:
context = {'errMsg': '页面提交参数可能为空'}
return render(request, 'error.html', context)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(request.user,
tag_codes=['can_write'
]).get(instance_name=instance_name)
except instance.DoesNotExist:
context = {'errMsg': '你所在组未关联该实例！'}
return render(request, 'error.html', context)
# 再次交给engine进行检测，防止绕过
try:
check_engine = get_engine(instance=instance)
check_result = check_engine.execute_check(db_name=db_name,
sql=sql_content.strip())
except Exception as e:
context = {'errMsg': str(e)}
return render(request, 'error.html', context)
# 未开启备份选项，并且engine支持备份，强制设置备份
sys_config = SysConfig()
if not sys_config.get('enable_backup_switch') and check_engine.auto_backup:
is_backup = True
# 按照系统配置确定是自动驳回还是放行
auto_review_wrong = sys_config.get('auto_review_wrong',
'') # 1表示出现警告就驳回，2和空表示出现错误才驳回
workflow_status = 'workflow_manreviewing'
if check_result.warning_count > 0 and auto_review_wrong == '1':
workflow_status = 'workflow_autoreviewwrong'
elif check_result.error_count > 0 and auto_review_wrong in ('', '1', '2'):
workflow_status = 'workflow_autoreviewwrong'
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
sql_workflow = SqlWorkflow.objects.create(
workflow_name=workflow_title,
demand_url=demand_url,
group_id=group_id,
group_name=group_name,
engineer=request.user.username,
engineer_display=request.user.display,
audit_auth_groups=Audit.settings(
group_id, WorkflowDict.workflow_type['sqlreview']),
status=workflow_status,
is_backup=is_backup,
instance=instance,
db_name=db_name,
is_manual=0,
syntax_type=check_result.syntax_type,
create_time=timezone.now(),
run_date_start=run_date_start or None,
run_date_end=run_date_end or None)
SqlWorkflowContent.objects.create(
workflow=sql_workflow,
sql_content=sql_content,
review_content=check_result.json(),
execute_result='')
workflow_id = sql_workflow.id
# 自动审核通过了，才调用工作流
if workflow_status == 'workflow_manreviewing':
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
Audit.add(WorkflowDict.workflow_type['sqlreview'], workflow_id)
except Exception as msg:
logger.error(f"提交工单报错，错误信息：{traceback.format_exc()}")
context = {'errMsg': msg}
logger.error(traceback.format_exc())
return render(request, 'error.html', context)
else:
# 自动审核通过才进行消息通知
if workflow_status == 'workflow_manreviewing':
# 获取审核信息
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
async_task(notify_for_audit,
audit_id=audit_id,
cc_users=cc_users,
timeout=60,
task_name=f'sqlreview-submit-{workflow_id}')
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def notify_for_audit(audit_id, **kwargs):
"""
工作流消息通知，不包含工单执行结束的通知
:param audit_id:
:param kwargs:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
if not __notify_cnf_status():
return None
sys_config = SysConfig()
# 获取审核信息
audit_detail = Audit.detail(audit_id=audit_id)
audit_id = audit_detail.audit_id
workflow_audit_remark = kwargs.get('audit_remark', '')
base_url = sys_config.get('archery_base_url', 'http://127.0.0.1:8000').rstrip('/')
workflow_url = "{base_url}/workflow/{audit_id}".format(base_url=base_url, audit_id=audit_detail.audit_id)
workflow_id = audit_detail.workflow_id
workflow_type = audit_detail.workflow_type
status = audit_detail.current_status
workflow_title = audit_detail.workflow_title
workflow_from = audit_detail.create_user_display
group_name = audit_detail.group_name
dingding_webhook = ResourceGroup.objects.get(group_id=audit_detail.group_id).ding_webhook
feishu_webhook = ResourceGroup.objects.get(group_id=audit_detail.group_id).feishu_webhook
qywx_webhook = ResourceGroup.objects.get(group_id=audit_detail.group_id).qywx_webhook
# 获取当前审批和审批流程
workflow_auditors, current_workflow_auditors = Audit.review_info(audit_detail.workflow_id,
audit_detail.workflow_type)
# 准备消息内容
if workflow_type == WorkflowDict.workflow_type['query']:
workflow_type_display = WorkflowDict.workflow_type['query_display']
workflow_detail = QueryPrivilegesApply.objects.get(apply_id=workflow_id)
instance = workflow_detail.instance.instance_name
db_name = ' '
if workflow_detail.priv_type == 1:
workflow_content = '''数据库清单：{}\n授权截止时间：{}\n结果集：{}\n'''.format(
workflow_detail.db_list,
datetime.datetime.strftime(workflow_detail.valid_date, '%Y-%m-%d %H:%M:%S'),
workflow_detail.limit_num)
elif workflow_detail.priv_type == 2:
db_name = workflow_detail.db_list
workflow_content = '''数据库：{}\n表清单：{}\n授权截止时间：{}\n结果集：{}\n'''.format(
workflow_detail.db_list,
workflow_detail.table_list,
datetime.datetime.strftime(workflow_detail.valid_date, '%Y-%m-%d %H:%M:%S'),
workflow_detail.limit_num)
else:
workflow_content = ''
elif workflow_type == WorkflowDict.workflow_type['sqlreview']:
workflow_type_display = WorkflowDict.workflow_type['sqlreview_display']
workflow_detail = SqlWorkflow.objects.get(pk=workflow_id)
instance = workflow_detail.instance.instance_name
db_name = workflow_detail.db_name
workflow_content = re.sub('[\r\n\f]{2,}', '\n',
workflow_detail.sqlworkflowcontent.sql_content[0:500].replace('\r', ''))
elif workflow_type == WorkflowDict.workflow_type['archive']:
workflow_type_display = WorkflowDict.workflow_type['archive_display']
workflow_detail = ArchiveConfig.objects.get(pk=workflow_id)
instance = workflow_detail.src_instance.instance_name
db_name = workflow_detail.src_db_name
workflow_content = '''归档表：{}\n归档模式：{}\n归档条件：{}\n'''.format(
workflow_detail.src_table_name,
workflow_detail.mode,
workflow_detail.condition)
else:
raise Exception('工单类型不正确')
# 准备消息格式
if status == WorkflowDict.workflow_status['audit_wait']: # 申请阶段
msg_title = "[{}]新的工单申请#{}".format(workflow_type_display, audit_id)
# 接收人，发送给该资源组内对应权限组所有的用户
auth_group_names = Group.objects.get(id=audit_detail.current_audit).name
msg_to = auth_group_users([auth_group_names], audit_detail.group_id)
msg_cc = Users.objects.filter(username__in=kwargs.get('cc_users', []))
# 消息内容
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n审批流程：{}\n当前审批：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from,
group_name,
instance,
db_name,
workflow_auditors,
current_workflow_auditors,
workflow_title,
workflow_url,
workflow_content)
elif status == WorkflowDict.workflow_status['audit_success']: # 审核通过
msg_title = "[{}]工单审核通过#{}".format(workflow_type_display, audit_id)
# 接收人，仅发送给申请人
msg_to = [Users.objects.get(username=audit_detail.create_user)]
msg_cc = Users.objects.filter(username__in=kwargs.get('cc_users', []))
# 消息内容
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from,
group_name,
instance,
db_name,
workflow_auditors,
workflow_title,
workflow_url,
workflow_content)
elif status == WorkflowDict.workflow_status['audit_reject']: # 审核驳回
msg_title = "[{}]工单被驳回#{}".format(workflow_type_display, audit_id)
# 接收人，仅发送给申请人
msg_to = [Users.objects.get(username=audit_detail.create_user)]
msg_cc = Users.objects.filter(username__in=kwargs.get('cc_users', []))
# 消息内容
msg_content = '''发起时间：{}\n目标实例：{}\n数据库：{}\n工单名称：{}\n工单地址：{}\n驳回原因：{}\n提醒：此工单被审核不通过，请按照驳回原因进行修改！'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
instance,
db_name,
workflow_title,
workflow_url,
re.sub('[\r\n\f]{2,}', '\n', workflow_audit_remark))
elif status == WorkflowDict.workflow_status['audit_abort']: # 审核取消，通知所有审核人
msg_title = "[{}]提交人主动终止工单#{}".format(workflow_type_display, audit_id)
# 接收人，发送给该资源组内对应权限组所有的用户
auth_group_names = [Group.objects.get(id=auth_group_id).name for auth_group_id in
audit_detail.audit_auth_groups.split(',')]
msg_to = auth_group_users(auth_group_names, audit_detail.group_id)
msg_cc = Users.objects.filter(username__in=kwargs.get('cc_users', []))
# 消息内容
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n工单名称：{}\n工单地址：{}\n终止原因：{}'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from,
group_name,
instance,
db_name,
workflow_title,
workflow_url,
re.sub('[\r\n\f]{2,}', '\n', workflow_audit_remark))
else:
raise Exception('工单状态不正确')
logger.info(f"通知Debug{msg_to}{msg_cc}")
# 发送通知
__send(msg_title, msg_content, msg_to, msg_cc, feishu_webhook=feishu_webhook, dingding_webhook=dingding_webhook,
qywx_webhook=qywx_webhook)
def notify_for_audit(audit_id, **kwargs):
"""
工作流消息通知，不包含工单执行结束的通知
:param audit_id:
:param kwargs:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
sys_config = SysConfig()
if not sys_config.get('mail') and not sys_config.get('ding'):
logger.info('未开启消息通知，可在系统设置中开启')
return None
# 获取审核信息
audit_detail = Audit.detail(audit_id=audit_id)
audit_id = audit_detail.audit_id
workflow_audit_remark = kwargs.get('audit_remark', '')
base_url = sys_config.get('archery_base_url',
'http://127.0.0.1:8000').rstrip('/')
workflow_url = "{base_url}/workflow/{audit_id}".format(
base_url=base_url, audit_id=audit_detail.audit_id)
msg_cc_email = kwargs.get('email_cc', [])
workflow_id = audit_detail.workflow_id
workflow_type = audit_detail.workflow_type
status = audit_detail.current_status
workflow_title = audit_detail.workflow_title
workflow_from = audit_detail.create_user_display
group_name = audit_detail.group_name
webhook_url = ResourceGroup.objects.get(
group_id=audit_detail.group_id).ding_webhook
# 获取当前审批和审批流程
workflow_auditors, current_workflow_auditors = Audit.review_info(
audit_detail.workflow_id, audit_detail.workflow_type)
# 准备消息内容
if workflow_type == WorkflowDict.workflow_type['query']:
workflow_type_display = WorkflowDict.workflow_type['query_display']
workflow_detail = QueryPrivilegesApply.objects.get(
apply_id=workflow_id)
instance = workflow_detail.instance.instance_name
db_name = ''
if workflow_detail.priv_type == 1:
workflow_content = '''数据库清单：{}\n授权截止时间：{}\n结果集：{}\n'''.format(
workflow_detail.db_list,
datetime.datetime.strftime(workflow_detail.valid_date,
'%Y-%m-%d %H:%M:%S'),
workflow_detail.limit_num)
elif workflow_detail.priv_type == 2:
db_name = workflow_detail.db_list
workflow_content = '''数据库：{}\n表清单：{}\n授权截止时间：{}\n结果集：{}\n'''.format(
workflow_detail.db_list, workflow_detail.table_list,
datetime.datetime.strftime(workflow_detail.valid_date,
'%Y-%m-%d %H:%M:%S'),
workflow_detail.limit_num)
else:
workflow_content = ''
elif workflow_type == WorkflowDict.workflow_type['sqlreview']:
workflow_type_display = WorkflowDict.workflow_type['sqlreview_display']
workflow_detail = SqlWorkflow.objects.get(pk=workflow_id)
instance = workflow_detail.instance.instance_name
db_name = workflow_detail.db_name
workflow_content = re.sub(
'[\r\n\f]{2,}', '\n',
workflow_detail.sqlworkflowcontent.sql_content[0:500].replace(
'\r', ''))
else:
raise Exception('工单类型不正确')
# 准备消息格式
if status == WorkflowDict.workflow_status['audit_wait']: # 申请阶段
msg_title = "[{}]新的工单申请#{}".format(workflow_type_display, audit_id)
# 接收人，发送给该资源组内对应权限组所有的用户
auth_group_names = Group.objects.get(
id=audit_detail.current_audit).name
msg_to = auth_group_users([auth_group_names], audit_detail.group_id)
# 消息内容
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n审批流程：{}\n当前审批：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from, group_name, instance, db_name, workflow_auditors,
current_workflow_auditors, workflow_title, workflow_url,
workflow_content)
elif status == WorkflowDict.workflow_status['audit_success']: # 审核通过
msg_title = "[{}]工单审核通过#{}".format(workflow_type_display, audit_id)
# 接收人，仅发送给申请人
msg_to = [Users.objects.get(username=audit_detail.create_user)]
# 消息内容
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from, group_name, instance, db_name, workflow_auditors,
workflow_title, workflow_url, workflow_content)
elif status == WorkflowDict.workflow_status['audit_reject']: # 审核驳回
msg_title = "[{}]工单被驳回#{}".format(workflow_type_display, audit_id)
# 接收人，仅发送给申请人
msg_to = [Users.objects.get(username=audit_detail.create_user)]
# 消息内容
msg_content = '''发起时间：{}\n目标实例：{}\n数据库：{}\n工单名称：{}\n工单地址：{}\n驳回原因：{}\n提醒：此工单被审核不通过，请按照驳回原因进行修改！'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
instance, db_name, workflow_title, workflow_url,
workflow_audit_remark)
elif status == WorkflowDict.workflow_status['audit_abort']: # 审核取消，通知所有审核人
msg_title = "[{}]提交人主动终止工单#{}".format(workflow_type_display, audit_id)
# 接收人，发送给该资源组内对应权限组所有的用户
auth_group_names = [
Group.objects.get(id=auth_group_id).name
for auth_group_id in audit_detail.audit_auth_groups.split(',')
]
msg_to = auth_group_users(auth_group_names, audit_detail.group_id)
# 消息内容
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n工单名称：{}\n工单地址：{}\n终止原因：{}'''.format(
workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from, group_name, instance, db_name, workflow_title,
workflow_url, workflow_audit_remark)
else:
raise Exception('工单状态不正确')
# 处理接收人信息
msg_to_email = [user.email for user in msg_to if user.email]
# 发送通知
msg_sender = MsgSender()
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'):
if webhook_url:
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
def notify_for_execute(workflow):
"""
工单执行结束的通知
:param workflow:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
sys_config = SysConfig()
if not sys_config.get('mail') and not sys_config.get('ding'):
logger.info('未开启消息通知，可在系统设置中开启')
return None
# 获取当前审批和审批流程
base_url = sys_config.get('archery_base_url',
'http://127.0.0.1:8000').rstrip('/')
audit_auth_group, current_audit_auth_group = Audit.review_info(
workflow.id, 2)
audit_id = Audit.detail_by_workflow_id(workflow.id, 2).audit_id
url = "{base_url}/workflow/{audit_id}".format(base_url=base_url,
audit_id=audit_id)
msg_title = "[{}]工单{}#{}".format(
WorkflowDict.workflow_type['sqlreview_display'],
workflow.get_status_display(), audit_id)
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow.engineer_display, workflow.group_name, audit_auth_group,
workflow.workflow_name, url,
re.sub(
'[\r\n\f]{2,}', '\n',
workflow.sqlworkflowcontent.sql_content[0:500].replace('\r', '')))
# 邮件通知申请人，抄送DBA
msg_to = Users.objects.filter(username=workflow.engineer)
msg_cc = auth_group_users(auth_group_names=['DBA'],
group_id=workflow.group_id)
# 处理接收人信息
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_sender = MsgSender()
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'):
# 钉钉通知申请人，审核人，抄送DBA
webhook_url = ResourceGroup.objects.get(
group_id=workflow.group_id).ding_webhook
if webhook_url:
MsgSender.send_ding(webhook_url, msg_title + '\n' + msg_content)
# DDL通知
if sys_config.get('mail') and sys_config.get(
'ddl_notify_auth_group') and workflow.status == 'workflow_finish':
# 判断上线语句是否存在DDL，存在则通知相关人员
if workflow.syntax_type == 1:
# 消息内容通知
msg_title = '[Archery]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflow.engineer).display,
workflow.group_name, workflow.instance.instance_name,
workflow.db_name, workflow.workflow_name, url,
workflow.sqlworkflowcontent.sql_content[0:500])
# 获取通知成员ddl_notify_auth_group
msg_to = Users.objects.filter(
groups__name=sys_config.get('ddl_notify_auth_group'))
# 处理接收人信息
msg_to_email = [user.email for user in msg_to]
# 发送
msg_sender.send_email(msg_title, msg_content, msg_to_email)
def newexec(request):
"""正式提交SQL, 此处生成工单"""
workflow_name = request.POST.get('workflow_name')
demand_url = request.POST.get('demand_url')
group_name = request.POST.get('group_name')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
first_run_time = request.POST.get('first_run_time')
schedule_type = request.POST.get('period')
minutes = request.POST.get('minutes')
receivers = request.POST.getlist('receivers')
cc_list = request.POST.getlist('cc_list')
is_backup = True if request.POST.get('is_backup') == 'True' else False
sql_content = request.POST.get('sql_content').strip()
# 服务器端参数验证
if not all([
workflow_name, group_name, instance_name, db_name, first_run_time,
schedule_type, sql_content
]):
context = {'errMsg': '请检查提交工单填写内容是否完整'}
return render(request, 'error.html', context)
group_id = ResourceGroup.objects.get(group_name=group_name).group_id
instance = Instance.objects.get(instance_name=instance_name)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(request.user,
tag_codes=['can_write'
]).get(instance_name=instance_name)
except instance.DoesNotExist:
context = {'errMsg': '你所在组未关联该实例！'}
return render(request, 'error.html', context)
# 再次交给engine进行检测，防止绕过
try:
check_engine = get_engine(instance=instance)
check_result = check_engine.execute_check(db_name=db_name,
sql=sql_content.strip())
except Exception as e:
context = {'errMsg': str(e)}
return render(request, 'error.html', context)
# 未开启备份选项，并且engine支持备份，强制设置备份
sys_config = SysConfig()
if not sys_config.get('enable_backup_switch') and check_engine.auto_backup:
is_backup = True
# 按照系统配置确定是自动驳回还是放行
auto_review_wrong = sys_config.get('auto_review_wrong',
'') # 1表示出现警告就驳回，2和空表示出现错误才驳回
workflow_status = 'workflow_manreviewing'
if check_result.warning_count > 0 and auto_review_wrong == '1':
workflow_status = 'workflow_autoreviewwrong'
elif check_result.error_count > 0 and auto_review_wrong in ('', '1', '2'):
workflow_status = 'workflow_autoreviewwrong'
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
sql_workflow = SqlWorkflow.objects.create(
order_type="sqlcron_order",
workflow_name=workflow_name,
demand_url=demand_url,
group_id=group_id,
group_name=group_name,
engineer=request.user.username,
engineer_display=request.user.display,
audit_auth_groups=Audit.settings(
group_id, WorkflowDict.workflow_type['sqlreview']),
status=workflow_status,
is_backup=is_backup,
instance=instance,
db_name=db_name,
is_manual=0,
syntax_type=check_result.syntax_type,
create_time=timezone.now(),
run_date_start=None,
run_date_end=None,
cc_list=cc_list,
)
sql_workflow.receivers.set(
Users.objects.filter(username__in=receivers))
sql_workflow_content = SqlWorkflowContent.objects.create(
workflow=sql_workflow,
sql_content=sql_content,
review_content=check_result.json(),
execute_result='')
sched = schedule(
'sql.utils.execute_sql.execute',
sql_workflow.id,
hook='sql.utils.execute_sql.execute_callback',
name=f'sqlcron-{sql_workflow.id}',
schedule_type=schedule_type,
minutes=minutes,
next_run=first_run_time,
repeats=0, # 在审批结束前创建但不启用
timeout=-1,
)
sql_workflow.schedule = sched
sql_workflow.save()
sql_workflow_content.save()
# 自动审核通过了，才调用工作流
if workflow_status == 'workflow_manreviewing':
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
Audit.add(WorkflowDict.workflow_type['sqlreview'],
sql_workflow.id)
except Exception as msg:
logger.error(f"提交工单报错，错误信息：{traceback.format_exc()}")
context = {'errMsg': msg}
logger.error(traceback.format_exc())
return render(request, 'error.html', context)
else:
# 自动审核通过才进行消息通知
if workflow_status == 'workflow_manreviewing':
# 获取审核信息
audit_id = Audit.detail_by_workflow_id(
workflow_id=sql_workflow.id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
async_task(notify_for_audit,
audit_id=audit_id,
cc_users=receivers,
timeout=60,
task_name=f'sqlreview-submit-{sql_workflow.id}')
return redirect(reverse('sql:sqlcrondetail', args=(sql_workflow.id, )))
def detail(request, workflow_id):
"""展示SQL工单详细页面"""
workflow_detail = get_object_or_404(SqlWorkflow, pk=workflow_id)
if not can_view(request.user, workflow_id):
raise PermissionDenied
# 自动审批不通过的不需要获取下列信息
if workflow_detail.status != 'workflow_autoreviewwrong':
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Audit.review_info(workflow_id, 2)
# 是否可审核
is_can_review = Audit.can_review(request.user, workflow_id, 2)
# 是否可执行 TODO 这几个判断方法入参都修改为workflow对象，可减少多次数据库交互
is_can_execute = can_execute(request.user, workflow_id)
# 是否可定时执行
is_can_timingtask = can_timingtask(request.user, workflow_id)
# 是否可取消
is_can_cancel = can_cancel(request.user, workflow_id)
# 是否可查看回滚信息
is_can_rollback = can_rollback(request.user, workflow_id)
# 获取审核日志
try:
audit_detail = Audit.detail_by_workflow_id(workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview'])
audit_id = audit_detail.audit_id
last_operation_info = Audit.logs(audit_id=audit_id).latest('id').operation_info
# 等待审批的展示当前全部审批人
if workflow_detail.status == 'workflow_manreviewing':
auth_group_name = Group.objects.get(id=audit_detail.current_audit).name
current_audit_users = auth_group_users([auth_group_name], audit_detail.group_id)
current_audit_users_display = [user.display for user in current_audit_users]
last_operation_info += '，当前审批人：' + ','.join(current_audit_users_display)
except Exception as e:
logger.debug(f'无审核日志记录，错误信息{e}')
last_operation_info = ''
else:
audit_auth_group = '系统自动驳回'
current_audit_auth_group = '系统自动驳回'
is_can_review = False
is_can_execute = False
is_can_timingtask = False
is_can_cancel = False
is_can_rollback = False
last_operation_info = None
# 获取定时执行任务信息
if workflow_detail.status == 'workflow_timingtask':
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
job = task_info(job_id)
if job:
run_date = job.next_run
else:
run_date = ''
else:
run_date = ''
# 获取是否开启手工执行确认
manual = SysConfig().get('manual')
context = {'workflow_detail': workflow_detail, 'last_operation_info': last_operation_info,
'is_can_review': is_can_review, 'is_can_execute': is_can_execute, 'is_can_timingtask': is_can_timingtask,
'is_can_cancel': is_can_cancel, 'is_can_rollback': is_can_rollback, 'audit_auth_group': audit_auth_group,
'manual': manual, 'current_audit_auth_group': current_audit_auth_group, 'run_date': run_date}
return render(request, 'detail.html', context)
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
# 删除定时执行job
if workflow_detail.status == 'workflow_timingtask':
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(
workflow_id)
del_sqlcronjob(job_id)
# 将流程状态修改为人工终止流程
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 == WorkflowDict.workflow_status[
'audit_abort']:
async_task(notify_for_audit,
audit_id=audit_detail.audit_id,
audit_remark=audit_remark,
timeout=60)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def applyforprivileges(request):
title = request.POST['title']
instance_name = request.POST['instance_name']
group_name = request.POST['group_name']
group_id = ResourceGroup.objects.get(group_name=group_name).group_id
priv_type = request.POST['priv_type']
db_name = request.POST['db_name']
valid_date = request.POST['valid_date']
limit_num = request.POST['limit_num']
# 获取用户信息
user = request.user
# 服务端参数校验
result = {'status': 0, 'msg': 'ok', 'data': []}
if int(priv_type) == 1:
db_list = request.POST['db_list']
if title is None or instance_name is None or db_list is None or valid_date is None or limit_num is None:
result['status'] = 1
result['msg'] = '请填写完整'
return HttpResponse(json.dumps(result),
content_type='application/json')
elif int(priv_type) == 2:
table_list = request.POST['table_list']
if title is None or instance_name is None or db_name is None or valid_date is None or table_list is None or limit_num is None:
result['status'] = 1
result['msg'] = '请填写完整'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
user_instances(request.user, 'slave').get(instance_name=instance_name)
except Exception:
context = {'errMsg': '你所在组未关联该实例！'}
return render(request, 'error.html', context)
# 判断是否需要限制到表级别的权限
# 库权限
if int(priv_type) == 1:
db_list = db_list.split(',')
# 检查申请账号是否已拥整个库的查询权限
own_dbs = QueryPrivileges.objects.filter(
instance_name=instance_name,
user_name=user.username,
db_name__in=db_list,
valid_date__gte=datetime.datetime.now(),
priv_type=1,
is_deleted=0).values('db_name')
own_db_list = [table_info['db_name'] for table_info in own_dbs]
if own_db_list is None:
pass
else:
for db_name in db_list:
if db_name in own_db_list:
result['status'] = 1
result[
'msg'] = '你已拥有' + instance_name + '实例' + db_name + '库的全部查询权限，不能重复申请'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 表权限
elif int(priv_type) == 2:
table_list = table_list.split(',')
# 检查申请账号是否已拥有该表的查询权限
own_tables = QueryPrivileges.objects.filter(
instance_name=instance_name,
user_name=user.username,
db_name=db_name,
table_name__in=table_list,
valid_date__gte=datetime.datetime.now(),
priv_type=2,
is_deleted=0).values('table_name')
own_table_list = [
table_info['table_name'] for table_info in own_tables
]
if own_table_list is None:
pass
else:
for table_name in table_list:
if table_name in own_table_list:
result['status'] = 1
result[
'msg'] = '你已拥有' + instance_name + '实例' + db_name + '.' + table_name + '表的查询权限，不能重复申请'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 保存申请信息到数据库
applyinfo = QueryPrivilegesApply()
applyinfo.title = title
applyinfo.group_id = group_id
applyinfo.group_name = group_name
applyinfo.audit_auth_groups = Audit.settings(
group_id, WorkflowDict.workflow_type['query'])
applyinfo.user_name = user.username
applyinfo.user_display = user.display
applyinfo.instance_name = instance_name
if int(priv_type) == 1:
applyinfo.db_list = ','.join(db_list)
applyinfo.table_list = ''
elif int(priv_type) == 2:
applyinfo.db_list = db_name
applyinfo.table_list = ','.join(table_list)
applyinfo.priv_type = int(priv_type)
applyinfo.valid_date = valid_date
applyinfo.status = WorkflowDict.workflow_status[
'audit_wait'] # 待审核
applyinfo.limit_num = limit_num
applyinfo.create_user = user.username
applyinfo.save()
apply_id = applyinfo.apply_id
# 调用工作流插入审核信息,查询权限申请workflow_type=1
audit_result = Audit.add(WorkflowDict.workflow_type['query'],
apply_id)
if audit_result['status'] == 0:
# 更新业务表审核状态,判断是否插入权限信息
query_audit_call_back(apply_id,
audit_result['data']['workflow_status'])
except Exception as msg:
logger.error(traceback.format_exc())
result['status'] = 1
result['msg'] = str(msg)
else:
result = audit_result
return HttpResponse(json.dumps(result), content_type='application/json')
def newquery(request):
"""正式提交SQL, 此处生成工单"""
workflow_name = request.POST.get('workflow_name')
demand_url = request.POST.get('demand_url', '')
group_name = request.POST.get('group_name')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
first_run_time = request.POST.get('first_run_time')
schedule_type = request.POST.get('period')
minutes = request.POST.get('minutes')
receivers = request.POST.getlist('receivers')
cc_list = request.POST.getlist('cc_list')
is_backup = False
sql_content = request.POST.get('sql_content').strip()
result = {'status': 0, 'msg': 'ok', 'data': {}}
# 服务器端参数验证
if not all([
workflow_name, group_name, instance_name, db_name, first_run_time,
schedule_type, sql_content
]):
result['status'] = 1
result['msg'] = '请检查提交工单填写内容是否完整'
return HttpResponse(json.dumps(result),
content_type='application/json')
user = request.user
group_id = ResourceGroup.objects.get(group_name=group_name).group_id
instance = Instance.objects.get(instance_name=instance_name)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(user,
tag_codes=['can_read']).get(instance_name=instance_name)
except instance.DoesNotExist:
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, 0)
if priv_check_info['status'] != 0:
result['status'] = 1
result['msg'] = priv_check_info['msg']
return HttpResponse(json.dumps(result),
content_type='application/json')
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')
check_result = ReviewSet(full_sql=sql_content)
check_result.rows = [
ReviewResult(
id=1,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=sql_content,
affected_rows=0,
execute_time=0,
)
]
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
sql_workflow = SqlWorkflow.objects.create(
order_type="sqlcron_order",
workflow_name=workflow_name,
demand_url=demand_url,
group_id=group_id,
group_name=group_name,
engineer=request.user.username,
engineer_display=request.user.display,
audit_auth_groups=Audit.settings(
group_id, WorkflowDict.workflow_type['sqlreview']),
status='workflow_manreviewing',
is_backup=is_backup,
instance=instance,
db_name=db_name,
is_manual=0,
syntax_type=3, #0、未知，1、DDL，2、DML 3、QUERY'
create_time=timezone.now(),
run_date_start=None,
run_date_end=None,
cc_list=cc_list,
)
sql_workflow.receivers.set(
Users.objects.filter(username__in=receivers))
sql_workflow_content = SqlWorkflowContent.objects.create(
workflow=sql_workflow,
sql_content=sql_content,
review_content=check_result.json(),
execute_result='')
sched = schedule(
'sql.utils.query_sql.query',
sql_workflow.id,
hook='sql.utils.query_sql.query_callback',
name=f'sqlcron-{sql_workflow.id}',
schedule_type=schedule_type,
minutes=minutes,
next_run=first_run_time,
repeats=0, # 在审批结束前创建但不启用
timeout=-1,
)
sql_workflow.schedule = sched
sql_workflow.save()
sql_workflow_content.save()
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
Audit.add(WorkflowDict.workflow_type['sqlreview'], sql_workflow.id)
except Exception as e:
logger.error(f"提交工单报错，错误信息：{traceback.format_exc()}")
result['status'] = 1
result['msg'] = f'提交工单报错，错误信息：{e}'
return HttpResponse(json.dumps(result),
content_type='application/json')
else:
# 进行消息通知
audit_id = Audit.detail_by_workflow_id(
workflow_id=sql_workflow.id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
async_task(notify_for_audit,
audit_id=audit_id,
cc_users=receivers,
timeout=60,
task_name=f'sqlreview-submit-{sql_workflow.id}')
result['status'] = 0
result['data'] = {
'redirect': reverse('sql:sqlcrondetail', args=(sql_workflow.id, ))
}
return HttpResponse(json.dumps(result), content_type='application/json')
from django.http import HttpResponse, HttpResponseRedirect
from django.shortcuts import render
from django.urls import reverse
from common.config import SysConfig
from common.utils.const import WorkflowDict
from common.utils.extend_json_encoder import ExtendJSONEncoder
from sql.utils.data_masking import Masking
from sql.utils.resource_group import user_instances, user_groups
from sql.utils.workflow_audit import Audit
from .models import QueryPrivilegesApply, QueryPrivileges, QueryLog, ResourceGroup, Instance
from sql.engines import get_engine
logger = logging.getLogger('default')
Audit = Audit()
# 查询权限申请用于工作流审核回调
def query_audit_call_back(workflow_id, workflow_status):
# 更新业务表状态
apply_info = QueryPrivilegesApply.objects.get(apply_id=workflow_id)
apply_info.status = workflow_status
apply_info.save()
# 审核通过插入权限信息，批量插入，减少性能消耗
if workflow_status == WorkflowDict.workflow_status['audit_success']:
apply_queryset = QueryPrivilegesApply.objects.get(apply_id=workflow_id)
# 库权限
if apply_queryset.priv_type == 1:
insertlist = [
def archive_apply(request):
"""申请归档实例数据"""
user = request.user
title = request.POST.get('title')
group_name = request.POST.get('group_name')
src_instance_name = request.POST.get('src_instance_name')
src_db_name = request.POST.get('src_db_name')
src_table_name = request.POST.get('src_table_name')
mode = request.POST.get('mode')
dest_instance_name = request.POST.get('dest_instance_name')
dest_db_name = request.POST.get('dest_db_name')
dest_table_name = request.POST.get('dest_table_name')
condition = request.POST.get('condition')
no_delete = True if request.POST.get('no_delete') == 'true' else False
sleep = request.POST.get('sleep', 0)
result = {'status': 0, 'msg': 'ok', 'data': {}}
# 参数校验
if not all([
title, group_name, src_instance_name, src_db_name, src_table_name,
mode, condition
]) or no_delete is None:
return JsonResponse({'status': 1, 'msg': '请填写完整！', 'data': {}})
if mode == 'dest' and not all(
[dest_instance_name, dest_db_name, dest_table_name]):
return JsonResponse({
'status': 1,
'msg': '归档到实例时目标实例信息必选！',
'data': {}
})
# 获取源实例信息
try:
s_ins = user_instances(request.user,
db_type=['mysql'
]).get(instance_name=src_instance_name)
except Instance.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例！', 'data': {}})
# 获取目标实例信息
if mode == 'dest':
try:
d_ins = user_instances(
request.user,
db_type=['mysql']).get(instance_name=dest_instance_name)
except Instance.DoesNotExist:
return JsonResponse({
'status': 1,
'msg': '你所在组未关联该实例！',
'data': {}
})
else:
d_ins = None
# 获取资源组和审批信息
res_group = ResourceGroup.objects.get(group_name=group_name)
audit_auth_groups = Audit.settings(res_group.group_id,
WorkflowDict.workflow_type['archive'])
if not audit_auth_groups:
return JsonResponse({
'status': 1,
'msg': '审批流程不能为空，请先配置审批流程',
'data': {}
})
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 保存申请信息到数据库
archive_info = ArchiveConfig.objects.create(
title=title,
resource_group=res_group,
audit_auth_groups=audit_auth_groups,
src_instance=s_ins,
src_db_name=src_db_name,
src_table_name=src_table_name,
dest_instance=d_ins,
dest_db_name=dest_db_name,
dest_table_name=dest_table_name,
condition=condition,
mode=mode,
no_delete=no_delete,
sleep=sleep,
status=WorkflowDict.workflow_status['audit_wait'],
state=False,
user_name=user.username,
user_display=user.display,
)
archive_id = archive_info.id
# 调用工作流插入审核信息
audit_result = Audit.add(WorkflowDict.workflow_type['archive'],
archive_id)
except Exception as msg:
logger.error(traceback.format_exc())
result['status'] = 1
result['msg'] = str(msg)
else:
result = audit_result
# 消息通知
audit_id = Audit.detail_by_workflow_id(
workflow_id=archive_id,
workflow_type=WorkflowDict.workflow_type['archive']).audit_id
async_task(notify_for_audit,
audit_id=audit_id,
timeout=60,
task_name=f'archive-apply-{archive_id}')
return HttpResponse(json.dumps(result), content_type='application/json')
def detail(request, workflow_id):
workflow_detail = get_object_or_404(SqlWorkflow, pk=workflow_id)
if workflow_detail.status in ['workflow_finish', 'workflow_exception'] \
and workflow_detail.is_manual == 0:
rows = workflow_detail.sqlworkflowcontent.execute_result
else:
rows = workflow_detail.sqlworkflowcontent.review_content
# 自动审批不通过的不需要获取下列信息
if workflow_detail.status != 'workflow_autoreviewwrong':
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Audit.review_info(
workflow_id, 2)
# 是否可审核
is_can_review = Audit.can_review(request.user, workflow_id, 2)
# 是否可执行
is_can_execute = can_execute(request.user, workflow_id)
# 是否可定时执行
is_can_timingtask = can_timingtask(request.user, workflow_id)
# 是否可取消
is_can_cancel = can_cancel(request.user, workflow_id)
# 获取审核日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
last_operation_info = Audit.logs(
audit_id=audit_id).latest('id').operation_info
else:
audit_auth_group = '系统自动驳回'
current_audit_auth_group = '系统自动驳回'
is_can_review = False
is_can_execute = False
is_can_timingtask = False
is_can_cancel = False
last_operation_info = None
# 获取定时执行任务信息
if workflow_detail.status == 'workflow_timingtask':
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
job = job_info(job_id)
if job:
run_date = job.next_run
else:
run_date = ''
else:
run_date = ''
# 兼容旧数据'[[]]'格式，转换为新格式[{}]
if isinstance(json.loads(rows)[0], list):
review_result = ReviewSet()
for r in json.loads(rows):
review_result.rows += [ReviewResult(inception_result=r)]
rows = review_result.json()
context = {
'workflow_detail': workflow_detail,
'rows': rows,
'last_operation_info': last_operation_info,
'is_can_review': is_can_review,
'is_can_execute': is_can_execute,
'is_can_timingtask': is_can_timingtask,
'is_can_cancel': is_can_cancel,
'audit_auth_group': audit_auth_group,
'current_audit_auth_group': current_audit_auth_group,
'run_date': run_date
}
return render(request, 'detail.html', context)
def execute_select(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')
status = "workflow_finish"
operation_type = 6
operation_type_desc = '手工工单'
operation_info = "确认手工执行结束"
finish_time = datetime.datetime.now()
# 将流程状态修改为对应状态
SqlWorkflow(
id=workflow_id, status=status,
finish_time=finish_time).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=operation_type,
operation_type_desc=operation_type_desc,
operation_info=operation_info,
operator=request.user.username,
operator_display=request.user.display)
fileName = datetime.datetime.now().strftime("%Y%m%d%H%M%S") + ".txt"
output_file = "/tmp/" + fileName
def output_data():
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
instance = workflow_detail.instance
sql_content = workflow_detail.sqlworkflowcontent.sql_content.replace(
";", "").replace("'", '"')
sql_content = sql_content + " limit 10000;" if "limit" not in sql_content else sql_content
sql_cmd = f"""/usr/bin/mysql -u{instance.user} -p'{instance.raw_password}' --default-character-set=utf8 -h{instance.host} -P{instance.port} -e 'use {workflow_detail.db_name};{sql_content}' >{output_file}"""
result = os.system(sql_cmd)
return result
if output_data() == 0 and os.path.exists(output_file):
def file_iterator(fileName, chunk_size=512):
with open(fileName, 'r') as f:
while True:
c = f.read(chunk_size)
if c:
yield c
else:
break
response = StreamingHttpResponse(file_iterator(output_file))
response['Content-Type'] = 'application/octet-stream'
response['Content-Disposition'] = 'attachment;filename="{0}"'.format(
fileName)
return response
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
