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 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 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 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 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')
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 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 query_priv_apply(request):
"""
申请查询权限
:param request:
:return:
"""
title = request.POST['title']
instance_name = request.POST.get('instance_name')
group_name = request.POST.get('group_name')
group_id = ResourceGroup.objects.get(group_name=group_name).group_id
priv_type = request.POST.get('priv_type')
db_name = request.POST.get('db_name')
db_list = request.POST.getlist('db_list[]')
table_list = request.POST.getlist('table_list[]')
valid_date = request.POST.get('valid_date')
limit_num = request.POST.get('limit_num')
# 获取用户信息
user = request.user
# 服务端参数校验
result = {'status': 0, 'msg': 'ok', 'data': []}
if int(priv_type) == 1:
if not (title and instance_name and db_list and valid_date
and limit_num):
result['status'] = 1
result['msg'] = '请填写完整'
return HttpResponse(json.dumps(result),
content_type='application/json')
elif int(priv_type) == 2:
if not (title and instance_name and db_name and valid_date
and table_list and limit_num):
result['status'] = 1
result['msg'] = '请填写完整'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
user_instances(request.user, type='slave',
db_type='all').get(instance_name=instance_name)
except Instance.DoesNotExist:
context = {'errMsg': '你所在组未关联该实例！'}
return render(request, 'error.html', context)
# 库权限
ins = Instance.objects.get(instance_name=instance_name)
if int(priv_type) == 1:
# 检查申请账号是否已拥库查询权限
for db_name in db_list:
if _db_priv(user, ins, db_name):
result['status'] = 1
result['msg'] = f'你已拥有{instance_name}实例{db_name}库权限，不能重复申请'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 表权限
elif int(priv_type) == 2:
# 先检查是否拥有库权限
if _db_priv(user, ins, db_name):
result['status'] = 1
result['msg'] = f'你已拥有{instance_name}实例{db_name}库的全部权限，不能重复申请'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 检查申请账号是否已拥有该表的查询权限
for tb_name in table_list:
if _tb_priv(user, ins, db_name, tb_name):
result['status'] = 1
result[
'msg'] = f'你已拥有{instance_name}实例{db_name}.{tb_name}表的查询权限，不能重复申请'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 保存申请信息到数据库
applyinfo = QueryPrivilegesApply(
title=title,
group_id=group_id,
group_name=group_name,
audit_auth_groups=Audit.settings(
group_id, WorkflowDict.workflow_type['query']),
user_name=user.username,
user_display=user.display,
instance=ins,
priv_type=int(priv_type),
valid_date=valid_date,
status=WorkflowDict.workflow_status['audit_wait'],
limit_num=limit_num)
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.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_apply_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
# 消息通知
audit_id = Audit.detail_by_workflow_id(
workflow_id=apply_id,
workflow_type=WorkflowDict.workflow_type['query']).audit_id
async_task(notify_for_audit, audit_id=audit_id, timeout=60)
return HttpResponse(json.dumps(result), content_type='application/json')
def autoreview(request):
"""正式提交SQL, 此处生成工单"""
workflow_id = request.POST.get('workflow_id')
sql_content = request.POST['sql_content']
workflow_title = request.POST['workflow_name']
group_name = request.POST['group_name']
group_id = 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 = request.POST['is_backup']
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 sql_content is None or workflow_title is None or instance_name is None or db_name is None or is_backup is None:
context = {'errMsg': '页面提交参数可能为空'}
return render(request, 'error.html', context)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(request.user, 'master').get(instance_name=instance_name)
except Exception:
context = {'errMsg': '你所在组未关联该实例！'}
return render(request, 'error.html', context)
sql_content = sql_content.strip()
# 审核
try:
check_engine = get_engine(instance)
check_result = check_engine.execute_check(db_name=db_name,
sql=sql_content)
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
if not check_result:
context = {'errMsg': 'inception返回的结果集为空！可能是SQL语句有语法错误'}
return render(request, 'error.html', context)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
# 遍历result，看是否有任何自动审核不通过的地方，并且按配置确定是标记审核不通过还是放行，放行的可以在工单内跳过inception直接执行
sys_config = SysConfig()
is_manual = 0
workflow_status = 'workflow_manreviewing'
for row in check_result.rows:
# 1表示警告，不影响执行
if row.errlevel == 1 and sys_config.get('auto_review_wrong',
'') == '1':
workflow_status = 'workflow_autoreviewwrong'
break
# 2表示严重错误，或者inception不支持的语法，标记手工执行，可以跳过inception直接执行
elif row.errlevel == 2:
is_manual = 1
if sys_config.get('auto_review_wrong', '') in ('', '1', '2'):
workflow_status = 'workflow_autoreviewwrong'
break
elif re.match(r"\w*comments\w*", row.errormessage):
is_manual = 1
if sys_config.get('auto_review_wrong', '') in ('', '1', '2'):
workflow_status = 'workflow_autoreviewwrong'
break
# 判断SQL是否包含DDL语句，SQL语法 1、DDL，2、DML
sql_syntax = 2
for stmt in sqlparse.split(sql_content):
statement = sqlparse.parse(stmt)[0]
syntax_type = statement.token_first(skip_cm=True).ttype.__str__()
if syntax_type == 'Token.Keyword.DDL':
sql_syntax = 1
break
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
engineer = request.user.username
if not workflow_id:
sql_workflow = SqlWorkflow()
sql_workflow.create_time = timezone.now()
else:
sql_workflow = SqlWorkflow.objects.get(id=int(workflow_id))
sql_workflow.workflow_name = workflow_title
sql_workflow.group_id = group_id
sql_workflow.group_name = group_name
sql_workflow.engineer = engineer
sql_workflow.engineer_display = request.user.display
sql_workflow.audit_auth_groups = Audit.settings(
group_id, WorkflowDict.workflow_type['sqlreview'])
sql_workflow.status = workflow_status
sql_workflow.is_backup = is_backup
sql_workflow.review_content = check_result.json()
sql_workflow.instance_name = instance_name
sql_workflow.db_name = db_name
sql_workflow.sql_content = sql_content
sql_workflow.execute_result = ''
sql_workflow.is_manual = is_manual
sql_workflow.audit_remark = ''
sql_workflow.sql_syntax = sql_syntax
sql_workflow.save()
workflow_id = sql_workflow.id
# 自动审核通过了，才调用工作流
if workflow_status == 'workflow_manreviewing':
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
Audit.add(WorkflowDict.workflow_type['sqlreview'], workflow_id)
except Exception as msg:
logger.error(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 create(self, validated_data):
"""使用原工单submit流程创建工单"""
workflow_data = validated_data.pop('workflow')
instance = workflow_data['instance']
sql_content = validated_data['sql_content'].strip()
user = Users.objects.get(username=workflow_data['engineer'])
group = ResourceGroup.objects.get(pk=workflow_data['group_id'])
active_user = Users.objects.filter(is_active=1)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(user, tag_codes=['can_write']).get(id=instance.id)
except instance.DoesNotExist:
raise serializers.ValidationError({'errors': '你所在组未关联该实例！'})
# 再次交给engine进行检测，防止绕过
try:
check_engine = get_engine(instance=instance)
check_result = check_engine.execute_check(db_name=workflow_data['db_name'],
sql=sql_content)
except Exception as e:
raise serializers.ValidationError({'errors': str(e)})
# 未开启备份选项，并且engine支持备份，强制设置备份
is_backup = workflow_data['is_backup'] if 'is_backup' in workflow_data.keys() else False
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'
workflow_data.update(status=workflow_status,
is_backup=is_backup,
is_manual=0,
syntax_type=check_result.syntax_type,
engineer_display=user.display,
group_name=group.group_name,
audit_auth_groups=Audit.settings(workflow_data['group_id'],
WorkflowDict.workflow_type['sqlreview']))
try:
with transaction.atomic():
workflow = SqlWorkflow.objects.create(**workflow_data)
validated_data['review_content'] = check_result.json()
workflow_content = SqlWorkflowContent.objects.create(workflow=workflow, **validated_data)
# 自动审核通过了，才调用工作流
if workflow_status == 'workflow_manreviewing':
# 调用工作流插入审核信息, SQL上线权限申请workflow_type=2
Audit.add(WorkflowDict.workflow_type['sqlreview'], workflow.id)
except Exception as e:
logger.error(f"提交工单报错，错误信息：{traceback.format_exc()}")
raise serializers.ValidationError({'errors': str(e)})
else:
# 自动审核通过且开启了Apply阶段通知参数才发送消息通知
is_notified = 'Apply' in sys_config.get('notify_phase_control').split(',') \
if sys_config.get('notify_phase_control') else True
if workflow_status == 'workflow_manreviewing' and is_notified:
# 获取审核信息
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=active_user, timeout=60,
task_name=f'sqlreview-submit-{workflow.id}')
return workflow_content
def submit(request):
"""正式提交SQL, 此处生成工单"""
sql_content = request.POST['sql_content']
workflow_title = request.POST['workflow_name']
# 检查用户是否有权限涉及到资源组等， 比较复杂， 可以把检查权限改成一个独立的方法
# 工单表中可以考虑不存储资源组相关信息
# 工单和实例关联， 实例和资源组关联， 资源组和用户关联。
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 = request.POST['is_backup']
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 sql_content is None or workflow_title is None or instance_name is None or db_name is None or is_backup is None:
context = {'errMsg': '页面提交参数可能为空'}
return render(request, 'error.html', context)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(request.user, type='master',
db_type='mysql').get(instance_name=instance_name)
except Exception:
context = {'errMsg': '你所在组未关联该实例！'}
return render(request, 'error.html', context)
sql_content = sql_content.strip()
# 审核
try:
check_engine = get_engine(instance)
check_result = check_engine.execute_check(db_name=db_name,
sql=sql_content)
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
if not check_result:
context = {'errMsg': 'inception返回的结果集为空！可能是SQL语句有语法错误'}
return render(request, 'error.html', context)
# 遍历result，看是否有任何自动审核不通过的地方，并且按配置确定是标记审核不通过还是放行，放行的可以在工单内原生执行
sys_config = SysConfig()
is_manual = 0
workflow_status = 'workflow_manreviewing'
for row in check_result.rows:
# 1表示警告，不影响执行
if row.errlevel == 1 and sys_config.get('auto_review_wrong',
'') == '1':
workflow_status = 'workflow_autoreviewwrong'
break
# 2表示严重错误，或者inception不支持的语法，标记手工执行，可以跳过inception直接执行
elif row.errlevel == 2:
is_manual = 1
if sys_config.get('auto_review_wrong', '') in ('', '1', '2'):
workflow_status = 'workflow_autoreviewwrong'
break
elif re.match(r"\w*comments\w*", row.errormessage):
is_manual = 1
if sys_config.get('auto_review_wrong', '') in ('', '1', '2'):
workflow_status = 'workflow_autoreviewwrong'
break
# 判断SQL是否包含DDL语句，SQL语法 1、DDL，2、DML
syntax_type = 2
for stmt in sqlparse.split(sql_content):
if get_syntax_type(stmt) == 'DDL':
syntax_type = 1
break
# 调用工作流生成工单
# 使用事务保持数据一致性
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=is_manual,
syntax_type=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(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, )))
