def query(request):
"""
获取SQL查询结果
:param request:
:return:
"""
instance_name = request.POST.get('instance_name')
sql_content = request.POST.get('sql_content')
db_name = request.POST.get('db_name')
limit_num = int(request.POST.get('limit_num', 0))
user = request.user
result = {'status': 0, 'msg': 'ok', 'data': {}}
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result['status'] = 1
result['msg'] = '实例不存在'
return result
# 服务器端参数验证
if None in [sql_content, db_name, instance_name, limit_num]:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
config = SysConfig()
# 查询前的检查，禁用语句检查，语句切分
query_engine = get_engine(instance=instance)
query_check_info = query_engine.query_check(db_name=db_name,
sql=sql_content)
if query_check_info.get('bad_query'):
# 引擎内部判断为 bad_query
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
if query_check_info.get(
'has_star') and config.get('disable_star') is True:
# 引擎内部判断为有 * 且禁止 * 选项打开
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
sql_content = query_check_info['filtered_sql']
# 查询权限校验，并且获取limit_num
priv_check_info = query_priv_check(user, instance, db_name,
sql_content, limit_num)
if priv_check_info['status'] == 0:
limit_num = priv_check_info['data']['limit_num']
priv_check = priv_check_info['data']['priv_check']
else:
result['status'] = 1
result['msg'] = priv_check_info['msg']
return HttpResponse(json.dumps(result),
content_type='application/json')
# explain的limit_num设置为0
limit_num = 0 if re.match(r"^explain",
sql_content.lower()) else limit_num
# 对查询sql增加limit限制或者改写语句
sql_content = query_engine.filter_sql(sql=sql_content,
limit_num=limit_num)
# 执行查询语句，timeout=max_execution_time
max_execution_time = int(config.get('max_execution_time', 60))
query_task_id = async_task(query_engine.query,
db_name=str(db_name),
sql=sql_content,
limit_num=limit_num,
timeout=max_execution_time,
cached=60)
# 等待执行结果，max_execution_time后还没有返回结果代表将会被终止
query_task = fetch(query_task_id,
wait=max_execution_time * 1000,
cached=True)
# 在max_execution_time内执行结束
if query_task:
if query_task.success:
query_result = query_task.result
query_result.query_time = query_task.time_taken()
else:
query_result = ResultSet(full_sql=sql_content)
query_result.error = query_task.result
# 等待超时，async_task主动关闭连接
else:
query_result = ResultSet(full_sql=sql_content)
query_result.error = f'查询时间超过 {max_execution_time} 秒，已被主动终止，请优化语句或者联系管理员。'
# 查询异常
if query_result.error:
result['status'] = 1
result['msg'] = query_result.error
# 数据脱敏，仅对查询无错误的结果集进行脱敏，并且按照query_check配置是否返回
elif config.get('data_masking'):
query_masking_task_id = async_task(query_engine.query_masking,
db_name=db_name,
sql=sql_content,
resultset=query_result,
cached=60)
query_masking_task = fetch(query_masking_task_id,
wait=60 * 1000,
cached=True)
if query_masking_task.success:
masking_result = query_masking_task.result
masking_result.mask_time = query_masking_task.time_taken()
# 脱敏出错
if masking_result.error:
# 开启query_check，直接返回异常，禁止执行
if config.get('query_check'):
result['status'] = 1
result['msg'] = masking_result.error
# 关闭query_check，忽略错误信息，返回未脱敏数据，权限校验标记为跳过
else:
query_result.error = None
priv_check = False
result['data'] = query_result.__dict__
# 正常脱敏
else:
result['data'] = masking_result.__dict__
else:
logger.error(
f'数据脱敏异常，查询语句：{sql_content}\n，错误信息：{traceback.format_exc()}'
)
# 抛出未定义异常，并且开启query_check，直接返回异常，禁止执行
if config.get('query_check'):
result['status'] = 1
result[
'msg'] = f'数据脱敏异常，请联系管理员，错误信息：{query_masking_task.result}'
# 关闭query_check，忽略错误信息，返回未脱敏数据，权限校验标记为跳过
else:
query_result.error = None
priv_check = False
result['data'] = query_result.__dict__
# 无需脱敏的语句
else:
result['data'] = query_result.__dict__
# 仅将成功的查询语句记录存入数据库
if not query_result.error:
if int(limit_num) == 0:
limit_num = int(query_result.affected_rows)
else:
limit_num = min(int(limit_num),
int(query_result.affected_rows))
query_log = QueryLog(username=user.username,
user_display=user.display,
db_name=db_name,
instance_name=instance.instance_name,
sqllog=sql_content,
effect_row=limit_num,
cost_time=query_result.query_time,
priv_check=priv_check,
hit_rule=query_result.mask_rule_hit,
masking=query_result.is_masked)
# 防止查询超时
try:
query_log.save()
except OperationalError:
connection.close()
query_log.save()
except Exception as e:
logger.error(
f'查询异常报错，查询语句：{sql_content}\n，错误信息：{traceback.format_exc()}')
result['status'] = 1
result['msg'] = f'查询异常报错，错误信息：{e}'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 返回查询结果
try:
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
# 虽然能正常返回，但是依然会乱码
except UnicodeDecodeError:
return HttpResponse(json.dumps(result,
default=str,
bigint_as_string=True,
encoding='latin1'),
content_type='application/json')
def execute_check(self, db_name=None, sql=''):
"""上线单执行前的检查, 返回Review set"""
config = SysConfig()
# 进行Inception检查，获取检测结果
if not config.get('inception'):
try:
inception_engine = GoInceptionEngine()
inc_check_result = inception_engine.execute_check(
instance=self.instance, db_name=db_name, sql=sql)
except Exception as e:
logger.debug(f"goInception检测语句报错：错误信息{traceback.format_exc()}")
raise RuntimeError(
f"goInception检测语句报错，请注意检查系统配置中goInception配置，错误信息：\n{e}")
else:
try:
inception_engine = InceptionEngine()
inc_check_result = inception_engine.execute_check(
instance=self.instance, db_name=db_name, sql=sql)
except Exception as e:
logger.debug(f"Inception检测语句报错：错误信息{traceback.format_exc()}")
raise RuntimeError(
f"Inception检测语句报错，请注意检查系统配置中Inception配置，错误信息：\n{e}")
# 判断Inception检测结果
if inc_check_result.error:
logger.debug(f"Inception检测语句报错：错误信息{inc_check_result.error}")
raise RuntimeError(
f"Inception检测语句报错，错误信息：\n{inc_check_result.error}")
# 禁用/高危语句检查
check_critical_result = ReviewSet(full_sql=sql)
line = 1
critical_ddl_regex = config.get('critical_ddl_regex', '')
p = re.compile(critical_ddl_regex)
check_critical_result.syntax_type = 2 # TODO 工单类型 0、其他 1、DDL，2、DML
for row in inc_check_result.rows:
statement = row.sql
# 去除注释
statement = remove_comments(statement, db_type='mysql')
# 禁用语句
if re.match(r"^select", statement.lower()):
check_critical_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='驳回不支持语句',
errormessage='仅支持DML和DDL语句，查询语句请使用SQL查询功能！',
sql=statement)
# 高危语句
elif critical_ddl_regex and p.match(statement.strip().lower()):
check_critical_result.is_critical = True
result = ReviewResult(id=line,
errlevel=2,
stagestatus='驳回高危SQL',
errormessage='禁止提交匹配' +
critical_ddl_regex + '条件的语句！',
sql=statement)
# 正常语句
else:
result = ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=statement,
affected_rows=0,
execute_time=0,
)
# 没有找出DDL语句的才继续执行此判断
if check_critical_result.syntax_type == 2:
if get_syntax_type(statement, parser=False,
db_type='mysql') == 'DDL':
check_critical_result.syntax_type = 1
check_critical_result.rows += [result]
# 遇到禁用和高危语句直接返回
if check_critical_result.is_critical:
check_critical_result.error_count += 1
return check_critical_result
line += 1
return inc_check_result
def notify_for_audit(audit_id, msg_type=0, **kwargs):
"""
工作流消息通知，不包含工单执行结束的通知
:param audit_id:
:param msg_type: 0.all,1.email,2.dingding
: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_email_cc = 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)
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:
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)
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_email_reciver = [
user.email for user in auth_group_users([auth_group_names],
audit_detail.group_id)
]
# 消息内容
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n当前审批：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_from, group_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_email_reciver = [
Users.objects.get(username=audit_detail.create_user).email
]
# 消息内容
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_from, group_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_email_reciver = [
Users.objects.get(username=audit_detail.create_user).email
]
# 消息内容
msg_content = '''工单名称：{}\n工单地址：{}\n驳回原因：{}\n提醒：此工单被审核不通过，请按照驳回原因进行修改！'''.format(
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_email_reciver = [
user.email for user in auth_group_users(auth_group_names,
audit_detail.group_id)
]
# 消息内容
msg_content = '''发起人：{}\n组：{}\n工单名称：{}\n工单地址：{}\n提醒：提交人主动终止流程'''.format(
workflow_from, group_name, workflow_title, workflow_url)
else:
raise Exception('工单状态不正确')
# 判断是发送钉钉还是发送邮件
msg_sender = MsgSender()
logger.info('发送消息通知，消息audit_id={}'.format(audit_id))
logger.info('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(msg_title,
msg_email_reciver,
msg_content))
if msg_type == 0:
if sys_config.get('mail'):
msg_sender.send_email(msg_title,
msg_content,
msg_email_reciver,
list_cc_addr=msg_email_cc)
if sys_config.get('ding'):
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
elif msg_type == 1:
msg_sender.send_email(msg_title,
msg_content,
msg_email_reciver,
list_cc_addr=msg_email_cc)
elif msg_type == 2:
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
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 execute_callback(task):
"""异步任务的回调, 将结果填入数据库等等
使用django-q的hook, 传入参数为整个task
task.result 是真正的结果
"""
# https://stackoverflow.com/questions/7835272/django-operationalerror-2006-mysql-server-has-gone-away
if connection.connection and not connection.is_usable():
close_old_connections()
workflow_id = task.args[0]
# 判断工单状态，如果不是执行中的，不允许更新信息，直接抛错记录日志
with transaction.atomic():
workflow = SqlWorkflow.objects.get(id=workflow_id)
if workflow.status != 'workflow_executing':
raise Exception(f'工单{workflow.id}状态不正确，禁止重复更新执行结果！')
workflow.finish_time = task.stopped
if not task.success:
# 不成功会返回错误堆栈信息，构造一个错误信息
workflow.status = 'workflow_exception'
execute_result = ReviewSet(
full_sql=workflow.sqlworkflowcontent.sql_content)
execute_result.rows = [
ReviewResult(stage='Execute failed',
errlevel=2,
stagestatus='异常终止',
errormessage=task.result,
sql=workflow.sqlworkflowcontent.sql_content)
]
elif task.result.warning or task.result.error:
execute_result = task.result
workflow.status = 'workflow_exception'
else:
execute_result = task.result
workflow.status = 'workflow_finish'
try:
# 保存执行结果
workflow.sqlworkflowcontent.execute_result = execute_result.json()
workflow.sqlworkflowcontent.save()
workflow.save()
except Exception as e:
logger.error(f'SQL工单回调异常: {workflow_id} {traceback.format_exc()}')
SqlWorkflow.objects.filter(id=workflow_id).update(
finish_time=task.stopped,
status='workflow_exception',
)
workflow.sqlworkflowcontent.execute_result = {f'{e}'}
workflow.sqlworkflowcontent.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='系统')
# DDL工单结束后清空实例资源缓存
if workflow.syntax_type == 1:
r = get_redis_connection("default")
for key in r.scan_iter(match='*insRes*', count=2000):
r.delete(key)
# 开启了Execute阶段通知参数才发送消息通知
sys_config = SysConfig()
is_notified = 'Execute' in sys_config.get('notify_phase_control').split(',') \
if sys_config.get('notify_phase_control') else True
if is_notified:
notify_for_execute(workflow)
def query(request):
"""
获取SQL查询结果
:param request:
:return:
"""
instance_name = request.POST.get('instance_name')
sql_content = request.POST.get('sql_content')
db_name = request.POST.get('db_name')
limit_num = int(request.POST.get('limit_num', 0))
user = request.user
result = {'status': 0, 'msg': 'ok', 'data': {}}
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result['status'] = 1
result['msg'] = '实例不存在'
return result
# 服务器端参数验证
if None in [sql_content, db_name, instance_name, limit_num]:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
config = SysConfig()
# 查询前的检查，禁用语句检查，语句切分
query_engine = get_engine(instance=instance)
query_check_info = query_engine.query_check(db_name=db_name,
sql=sql_content)
if query_check_info.get('bad_query'):
# 引擎内部判断为 bad_query
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
if query_check_info.get(
'has_star') and config.get('disable_star') is True:
# 引擎内部判断为有 * 且禁止 * 选项打开
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
sql_content = query_check_info['filtered_sql']
# 查询权限校验，并且获取limit_num
priv_check_info = query_priv_check(user, instance, db_name,
sql_content, limit_num)
if priv_check_info['status'] == 0:
limit_num = priv_check_info['data']['limit_num']
priv_check = priv_check_info['data']['priv_check']
else:
result['status'] = 1
result['msg'] = priv_check_info['msg']
return HttpResponse(json.dumps(result),
content_type='application/json')
# explain的limit_num设置为0
limit_num = 0 if re.match(r"^explain",
sql_content.lower()) else limit_num
# 对查询sql增加limit限制或者改写语句
sql_content = query_engine.filter_sql(sql=sql_content,
limit_num=limit_num)
# 先获取查询连接，用于后面查询复用连接以及终止会话
query_engine.get_connection(db_name=db_name)
thread_id = query_engine.thread_id
max_execution_time = int(config.get('max_execution_time', 60))
# 执行查询语句，并增加一个定时终止语句的schedule，timeout=max_execution_time
if thread_id:
schedule_name = f'query-{time.time()}'
run_date = (datetime.datetime.now() +
datetime.timedelta(seconds=max_execution_time))
add_kill_conn_schedule(schedule_name, run_date, instance.id,
thread_id)
with FuncTimer() as t:
query_result = query_engine.query(db_name, sql_content, limit_num)
query_result.query_time = t.cost
# 返回查询结果后删除schedule
if thread_id:
del_schedule(schedule_name)
# 查询异常
if query_result.error:
result['status'] = 1
result['msg'] = query_result.error
# 数据脱敏，仅对查询无错误的结果集进行脱敏，并且按照query_check配置是否返回
elif config.get('data_masking'):
try:
with FuncTimer() as t:
masking_result = query_engine.query_masking(
db_name, sql_content, query_result)
masking_result.mask_time = t.cost
# 脱敏出错
if masking_result.error:
# 开启query_check，直接返回异常，禁止执行
if config.get('query_check'):
result['status'] = 1
result['msg'] = f'数据脱敏异常：{masking_result.error}'
# 关闭query_check，忽略错误信息，返回未脱敏数据，权限校验标记为跳过
else:
query_result.error = None
priv_check = False
result['data'] = query_result.__dict__
logger.error(
f'数据脱敏异常，查询语句：{sql_content}\n，错误信息：{masking_result.error}'
)
# 正常脱敏
else:
result['data'] = masking_result.__dict__
except Exception as msg:
logger.error(f'数据脱敏异常，查询语句：{sql_content}\n，错误信息：{msg}')
# 抛出未定义异常，并且开启query_check，直接返回异常，禁止执行
if config.get('query_check'):
result['status'] = 1
result['msg'] = f'数据脱敏异常，请联系管理员，错误信息：{msg}'
# 关闭query_check，忽略错误信息，返回未脱敏数据，权限校验标记为跳过
else:
query_result.error = None
priv_check = False
result['data'] = query_result.__dict__
# 无需脱敏的语句
else:
result['data'] = query_result.__dict__
# 仅将成功的查询语句记录存入数据库
if not query_result.error:
if hasattr(query_engine, 'seconds_behind_master'):
result['data'][
'seconds_behind_master'] = query_engine.seconds_behind_master
if int(limit_num) == 0:
limit_num = int(query_result.affected_rows)
else:
limit_num = min(int(limit_num),
int(query_result.affected_rows))
query_log = QueryLog(username=user.username,
user_display=user.display,
db_name=db_name,
instance_name=instance.instance_name,
sqllog=sql_content,
effect_row=limit_num,
cost_time=query_result.query_time,
priv_check=priv_check,
hit_rule=query_result.mask_rule_hit,
masking=query_result.is_masked)
# 防止查询超时
try:
query_log.save()
except OperationalError:
connection.close()
query_log.save()
except Exception as e:
logger.error(
f'查询异常报错，查询语句：{sql_content}\n，错误信息：{traceback.format_exc()}')
result['status'] = 1
result['msg'] = f'查询异常报错，错误信息：{e}'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 返回查询结果
try:
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
# 虽然能正常返回，但是依然会乱码
except UnicodeDecodeError:
return HttpResponse(json.dumps(result,
default=str,
bigint_as_string=True,
encoding='latin1'),
content_type='application/json')
def submit(request):
"""正式提交SQL, 此处生成工单"""
sql_content = request.POST.get('sql_content').strip()
workflow_title = request.POST.get('workflow_name')
# 检查用户是否有权限涉及到资源组等， 比较复杂， 可以把检查权限改成一个独立的方法
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
notify_users = request.POST.getlist('notify_users')
list_cc_addr = [email['email'] for email in Users.objects.filter(username__in=notify_users).values('email')]
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]:
context = {'errMsg': '页面提交参数可能为空'}
return render(request, 'error.html', context)
# 未开启备份选项，强制设置备份
sys_config = SysConfig()
if not sys_config.get('enable_backup_switch'):
is_backup = True
# 验证组权限（用户是否在该组、该组是否有指定实例）
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)
# 按照系统配置确定是自动驳回还是放行
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(),
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}
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,)))
class InceptionDao(object):
def __init__(self, instance_name=None):
self.sys_config = SysConfig().sys_config
self.inception_host = self.sys_config.get('inception_host')
self.inception_port = int(self.sys_config.get('inception_port')) if self.sys_config.get(
'inception_port') else 6669
self.inception_remote_backup_host = self.sys_config.get('inception_remote_backup_host')
self.inception_remote_backup_port = int(
self.sys_config.get('inception_remote_backup_port')) if self.sys_config.get(
'inception_remote_backup_port') else 3306
self.inception_remote_backup_user = self.sys_config.get('inception_remote_backup_user')
self.inception_remote_backup_password = self.sys_config.get('inception_remote_backup_password')
if instance_name:
try:
instance_info = Instance.objects.get(instance_name=instance_name)
self.host = instance_info.host
self.port = int(instance_info.port)
self.user = instance_info.user
self.password = Prpcrypt().decrypt(instance_info.password)
except Exception:
raise Exception('找不到对应的实例配置信息，请配置')
def criticalDDL(self, sql_content):
'''
识别DROP DATABASE, DROP TABLE, TRUNCATE PARTITION, TRUNCATE TABLE等高危DDL操作，因为对于这些操作，inception在备份时只能备份METADATA，而不会备份数据！
如果识别到包含高危操作，则返回“审核不通过”
'''
resultList = []
criticalSqlFound = 0
critical_ddl_regex = self.sys_config.get('critical_ddl_regex')
p = re.compile(critical_ddl_regex)
# 删除注释语句
sql_content = ''.join(
map(lambda x: re.compile(r'(^--\s+.*|^/\*.*\*/;\s*$)').sub('', x, count=1),
sql_content.splitlines(1))).strip()
for row in sql_content.rstrip(';').split(';'):
if p.match(row.strip().lower()):
result = (
'', '', 2, '驳回高危SQL', '禁止提交匹配' + critical_ddl_regex + '条件的语句！',
row,
'', '', '', '')
criticalSqlFound = 1
else:
result = ('', '', 0, '', 'None', row, '', '', '', '')
resultList.append(result)
if criticalSqlFound == 1:
return resultList
else:
return None
def preCheck(self, sql_content):
'''
在提交给inception之前，预先识别一些Inception不能正确审核的SQL,比如"alter table t1;"或"alter table test.t1;" 以免导致inception core dump
'''
resultList = []
syntaxErrorSqlFound = 0
for row in sql_content.rstrip(';').split(';'):
if re.match(r"(\s*)alter(\s+)table(\s+)(\S+)(\s*);|(\s*)alter(\s+)table(\s+)(\S+)\.(\S+)(\s*);",
row.lower() + ";"):
result = ('', '', 2, 'SQL语法错误', 'ALTER TABLE 必须带有选项', row, '', '', '', '')
syntaxErrorSqlFound = 1
else:
result = ('', '', 0, '', 'None', row, '', '', '', '')
resultList.append(result)
if syntaxErrorSqlFound == 1:
return resultList
else:
return None
def sqlautoReview(self, sql_content, db_name, isSplit="no"):
'''
将sql交给inception进行自动审核，并返回审核结果。
'''
# 高危SQL检查
if self.sys_config.get('critical_ddl_regex', '') != '':
criticalDDL_check = self.criticalDDL(sql_content)
else:
criticalDDL_check = None
if criticalDDL_check is not None:
result = criticalDDL_check
else:
preCheckResult = self.preCheck(sql_content)
if preCheckResult is not None:
result = preCheckResult
else:
if isSplit == "yes":
# 这种场景只给osc进度功能使用
# 如果一个工单中同时包含DML和DDL，那么执行时被split后的SQL与提交的SQL会不一样（会在每条语句前面加use database;)，导致osc进度更新取不到正确的SHA1值。
# 请参考inception文档中--enable-split参数的说明
sqlSplit = "/*--user=%s; --password=%s; --host=%s; --enable-execute;--port=%d; --enable-ignore-warnings;--enable-split;*/\
inception_magic_start;\
use %s;\
%s\
inception_magic_commit;" % (
self.user,
self.password,
self.host,
self.port,
db_name,
sql_content)
splitResult = self._fetchall(sqlSplit, self.inception_host, self.inception_port, '', '', '')
tmpList = []
for splitRow in splitResult:
sqlTmp = splitRow[1]
sql = "/*--user=%s;--password=%s;--host=%s;--enable-check;--port=%d; --enable-ignore-warnings;*/\
inception_magic_start;\
%s\
inception_magic_commit;" % (
self.user,
self.password,
self.host,
self.port,
sqlTmp)
reviewResult = self._fetchall(sql, self.inception_host, self.inception_port, '', '', '')
tmpList.append(reviewResult)
# 二次加工一下
finalList = []
for splitRow in tmpList:
for sqlRow in splitRow:
finalList.append(list(sqlRow))
result = finalList
else:
# 工单审核使用
sql = "/*--user=%s;--password=%s;--host=%s;--enable-check=1;--port=%d;*/\
inception_magic_start;\
use %s;\
%s\
inception_magic_commit;" % (
self.user,
self.password,
self.host,
self.port,
db_name,
sql_content)
result = self._fetchall(sql, self.inception_host, self.inception_port, '', '', '')
return result
def executeFinal(self, workflow_detail):
'''
将sql交给inception进行最终执行，并返回执行结果。
'''
if workflow_detail.is_backup == '是':
strBackup = "--enable-remote-backup;"
else:
strBackup = "--disable-remote-backup;"
# 根据inception的要求，执行之前最好先split一下
sqlSplit = "/*--user=%s; --password=%s; --host=%s; --enable-execute;--port=%d; --enable-ignore-warnings;--enable-split;*/\
inception_magic_start;\
use %s;\
%s\
inception_magic_commit;" % (
self.user,
self.password,
self.host,
self.port,
workflow_detail.db_name, workflow_detail.sql_content)
splitResult = self._fetchall(sqlSplit, self.inception_host, self.inception_port, '', '', '')
tmpList = []
# 对于split好的结果，再次交给inception执行.这里无需保持在长连接里执行，短连接即可.
for splitRow in splitResult:
sqlTmp = splitRow[1]
sqlExecute = "/*--user=%s;--password=%s;--host=%s;--enable-execute;--port=%d; --enable-ignore-warnings;%s*/\
inception_magic_start;\
%s\
inception_magic_commit;" % (
self.user,
self.password,
self.host,
self.port,
strBackup,
sqlTmp)
executeResult = self._fetchall(sqlExecute, self.inception_host, self.inception_port, '', '', '')
for sqlRow in executeResult:
tmpList.append(sqlRow)
# 每执行一次，就将执行结果更新到工单的execute_result，便于获取osc进度时对比
workflow_detail.execute_result = json.dumps(tmpList)
try:
workflow_detail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflow_detail.save()
# 二次加工一下，目的是为了和sqlautoReview()函数的return保持格式一致，便于在detail页面渲染.
finalStatus = "已正常结束"
finalList = []
for sqlRow in tmpList:
# 如果发现任何一个行执行结果里有errLevel为1或2，并且stagestatus列没有包含Execute Successfully字样，则判断最终执行结果为有异常.
if (sqlRow[2] == 1 or sqlRow[2] == 2) and re.match(r"\w*Execute Successfully\w*", sqlRow[3]) is None:
finalStatus = "执行有异常"
finalList.append(list(sqlRow))
return (finalStatus, finalList)
def getRollbackSqlList(self, workflow_id):
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
listExecuteResult = json.loads(workflow_detail.execute_result)
# 回滚数据倒序展示
listExecuteResult.reverse()
listBackupSql = []
# 创建连接
conn = MySQLdb.connect(host=self.inception_remote_backup_host,
user=self.inception_remote_backup_user,
passwd=self.inception_remote_backup_password,
port=self.inception_remote_backup_port,
charset='utf8')
cur = conn.cursor()
for row in listExecuteResult:
try:
# 获取backup_dbname
if row[8] == 'None':
continue
backupDbName = row[8]
sequence = row[7]
sql = row[5]
opidTime = sequence.replace("'", "")
sqlTable = "select tablename from %s.$_$Inception_backup_information$_$ where opid_time='%s';" % (
backupDbName, opidTime)
cur.execute(sqlTable)
listTables = cur.fetchall()
if listTables:
tableName = listTables[0][0]
sqlBack = "select rollback_statement from %s.%s where opid_time='%s'" % (
backupDbName, tableName, opidTime)
cur.execute(sqlBack)
listBackup = cur.fetchall()
block_rollback_sql_list = [sql]
block_rollback_sql = '\n'.join([back_info[0] for back_info in listBackup])
block_rollback_sql_list.append(block_rollback_sql)
listBackupSql.append(block_rollback_sql_list)
except Exception as e:
logger.error(traceback.format_exc())
raise Exception(e)
return listBackupSql
def _fetchall(self, sql, paramHost, paramPort, paramUser, paramPasswd, paramDb):
'''
封装mysql连接和获取结果集方法
'''
try:
conn = MySQLdb.connect(host=paramHost, user=paramUser, passwd=paramPasswd, db=paramDb, port=paramPort,
charset='utf8')
cur = conn.cursor()
ret = cur.execute(sql)
result = cur.fetchall()
except Exception as e:
logger.error(traceback.format_exc())
raise Exception(e)
else:
cur.close()
conn.close()
return result
def getOscPercent(self, sqlSHA1):
"""已知SHA1值，去inception里查看OSC进度"""
sqlStr = "inception get osc_percent '%s'" % sqlSHA1
result = self._fetchall(sqlStr, self.inception_host, self.inception_port, '', '', '')
if len(result) > 0:
percent = result[0][3]
timeRemained = result[0][4]
pctResult = {"status": 0, "msg": "ok", "data": {"percent": percent, "timeRemained": timeRemained}}
else:
pctResult = {"status": 1, "msg": "没找到该SQL的进度信息，是否已经执行完毕？", "data": {"percent": -100, "timeRemained": -100}}
return pctResult
def stopOscProgress(self, sqlSHA1):
"""已知SHA1值，调用inception命令停止OSC进程，涉及的Inception命令和注意事项，请参考http://mysql-inception.github.io/inception-document/osc/"""
sqlStr = "inception stop alter '%s'" % sqlSHA1
result = self._fetchall(sqlStr, self.inception_host, self.inception_port, '', '', '')
if result is not None:
optResult = {"status": 0, "msg": "已成功停止OSC进程，请注意清理触发器和临时表，先清理触发器再删除临时表", "data": ""}
else:
optResult = {"status": 1, "msg": "ERROR 2624 (HY000):未找到OSC执行进程，可能已经执行完成", "data": ""}
return optResult
def query_print(self, sql_content, db_name):
'''
将sql交给inception打印语法树。
'''
# 工单审核使用
sql = "/*--user=%s;--password=%s;--host=%s;--port=%d;--enable-query-print;*/\
inception_magic_start;\
use %s;\
%s\
inception_magic_commit;" % (
self.user,
self.password,
self.host,
self.port,
db_name,
sql_content)
result = self._fetchall(sql, self.inception_host, self.inception_port, '', '', '')
return result
# inception执行情况统计
def statistic(self):
sql = '''
select
sum(deleting) deleting,
sum(inserting) inserting,
sum(updating) updating,
sum(selecting) selecting,
sum(altertable) altertable,
sum(renaming) renaming,
sum(createindex) createindex,
sum(dropindex) dropindex,
sum(addcolumn) addcolumn,
sum(dropcolumn) dropcolumn,
sum(changecolumn) changecolumn,
sum(alteroption) alteroption,
sum(alterconvert) alterconvert,
sum(createtable) createtable,
sum(droptable) droptable,
sum(createdb) createdb,
sum(truncating) truncating
from statistic;'''
try:
return Dao(host=self.inception_remote_backup_host,
user=self.inception_remote_backup_user,
port=self.inception_remote_backup_port,
password=self.inception_remote_backup_password).mysql_query('inception', sql)
except Exception:
logger.error(traceback.format_exc())
return {'column_list': [], 'rows': [], 'effect_row': 0}
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 = ''.join(
# map(lambda x: re.compile(r'(^--.*|^/\*.*\*/;\s*$)').sub('', x, count=1),
# sql_content.splitlines(1))).strip()
# # 去除空行
# sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content)
sql_content = sql_content.strip()
# 审核
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':
sys_config = SysConfig()
if sys_config.get('mail') or sys_config.get('ding'):
# 再次获取审核信息
audit_detail = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview'])
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)
async_task(notify,
audit_info=audit_detail,
workflow_url=workflow_url,
email_cc=list_cc_addr,
timeout=60)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def execute_check(self, db_name=None, sql=''):
"""上线单执行前的检查, 返回Review set"""
config = SysConfig()
check_result = ReviewSet(full_sql=sql)
# 禁用/高危语句检查
line = 1
critical_ddl_regex = config.get('critical_ddl_regex', '')
p = re.compile(critical_ddl_regex)
check_result.syntax_type = 2 # TODO 工单类型 0、其他 1、DDL，2、DML
# 把所有SQL转换成SqlItem List。 如有多行(内部有多个;)执行块，约定以delimiter $$作为开始, 以$$结束
# 需要在函数里实现单条SQL做sqlparse.format(sql, strip_comments=True)
sqlitemList = get_full_sqlitem_list(sql, db_name)
for sqlitem in sqlitemList:
# 禁用语句
if re.match(r"^\s*select", sqlitem.statement.lower(), re.I):
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='驳回不支持语句',
errormessage='仅支持DML和DDL语句，查询语句请使用SQL查询功能！',
sql=sqlitem.statement)
# 高危语句
elif critical_ddl_regex and p.match(
sqlitem.statement.strip().lower()):
check_result.is_critical = True
result = ReviewResult(id=line,
errlevel=2,
stagestatus='驳回高危SQL',
errormessage='禁止提交匹配' +
critical_ddl_regex + '条件的语句！',
sql=sqlitem.statement)
# 正常语句
else:
result = ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=0,
execute_time=0,
)
# 判断工单类型
if get_syntax_type(sqlitem.statement) == 'DDL':
check_result.syntax_type = 1
check_result.rows += [result]
# 遇到禁用和高危语句直接返回，提高效率
if check_result.is_critical:
check_result.error_count += 1
return check_result
line += 1
return check_result
def execute_check(self, db_name=None, sql=''):
"""上线单执行前的检查, 返回Review set"""
config = SysConfig()
check_result = ReviewSet(full_sql=sql)
# 禁用/高危语句检查
line = 1
critical_ddl_regex = config.get('critical_ddl_regex', '')
p = re.compile(critical_ddl_regex)
check_result.syntax_type = 2 # TODO 工单类型 0、其他 1、DDL，2、DML
for statement in sqlparse.split(sql):
statement = sqlparse.format(statement, strip_comments=True)
# 禁用语句
if re.match(r"^select", statement.lower()):
check_result.is_critical = False
result = ReviewResult(id=line,
errlevel=0,
stagestatus='Select statements',
errormessage='None',
sql=statement)
check_result.rows += [result]
check_result.syntax_type = 0
# check_result.error_count += 1
# 高危语句
elif critical_ddl_regex and p.match(statement.strip().lower()):
check_result.is_critical = True
result = ReviewResult(id=line,
errlevel=2,
stagestatus='驳回高危SQL',
errormessage='禁止提交匹配' +
critical_ddl_regex + '条件的语句！',
sql=statement)
# 正常语句
else:
result = ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=statement,
affected_rows=0,
execute_time=0,
)
# 判断工单类型
# 没有找出DDL语句的才继续执行此判断
if check_result.syntax_type == 2:
if get_syntax_type(statement) == 'DDL':
check_result.syntax_type = 1
check_result.rows += [result]
# 遇到禁用和高危语句直接返回，提高效率
if check_result.is_critical:
check_result.error_count += 1
return check_result
line += 1
# 通过检测的再进行inception检查
if config.get('go_inception'):
try:
inception_engine = GoInceptionEngine()
check_result = inception_engine.execute_check(
instance=self.instance, db_name=db_name, sql=sql)
except Exception as e:
logger.debug(f"Inception检测语句报错：错误信息{traceback.format_exc()}")
raise RuntimeError(
f"Inception检测语句报错，请注意检查系统配置中Inception配置，错误信息：\n{e}")
else:
try:
inception_engine = InceptionEngine()
check_result = inception_engine.execute_check(
instance=self.instance, db_name=db_name, sql=sql)
except Exception as e:
logger.debug(f"Inception检测语句报错：错误信息{traceback.format_exc()}")
raise RuntimeError(
f"Inception检测语句报错，请注意检查系统配置中Inception配置，错误信息：\n{e}")
return check_result
def addworkflowaudit(self, request, workflow_type, workflow_id, **kwargs):
result = {'status': 0, 'msg': '', 'data': []}
# 检查是否已存在待审核数据
workflowInfo = WorkflowAudit.objects.filter(workflow_type=workflow_type, workflow_id=workflow_id,
current_status=WorkflowDict.workflow_status['audit_wait'])
if len(workflowInfo) >= 1:
result['msg'] = '该工单当前状态为待审核，请勿重复提交'
raise Exception(result['msg'])
# 获取工单信息
if workflow_type == WorkflowDict.workflow_type['query']:
workflow_detail = QueryPrivilegesApply.objects.get(apply_id=workflow_id)
workflow_title = workflow_detail.title
group_id = workflow_detail.group_id
group_name = workflow_detail.group_name
create_user = workflow_detail.user_name
audit_auth_groups = workflow_detail.audit_auth_groups
workflow_remark = ''
elif workflow_type == WorkflowDict.workflow_type['sqlreview']:
workflow_detail = SqlWorkflow.objects.get(pk=workflow_id)
workflow_title = workflow_detail.workflow_name
group_id = workflow_detail.group_id
group_name = workflow_detail.group_name
create_user = workflow_detail.engineer
audit_auth_groups = workflow_detail.audit_auth_groups
workflow_remark = ''
else:
result['msg'] = '工单类型不存在'
raise Exception(result['msg'])
# 校验是否配置审批流程
if audit_auth_groups is None:
result['msg'] = '审批流程不能为空，请先配置审批流程'
raise Exception(result['msg'])
else:
audit_auth_groups_list = audit_auth_groups.split(',')
# 判断是否无需审核,并且修改审批人为空
if SysConfig().sys_config.get('auto_review', False):
if workflow_type == WorkflowDict.workflow_type['sqlreview']:
if is_autoreview(workflow_id):
Workflow = SqlWorkflow.objects.get(id=int(workflow_id))
Workflow.audit_auth_groups = '无需审批'
Workflow.status = '审核通过'
Workflow.save()
audit_auth_groups_list = None
# 无审核配置则无需审核，直接通过
if audit_auth_groups_list is None:
# 向审核主表插入审核通过的数据
audit_detail = WorkflowAudit()
audit_detail.group_id = group_id
audit_detail.group_name = group_name
audit_detail.workflow_id = workflow_id
audit_detail.workflow_type = workflow_type
audit_detail.workflow_title = workflow_title
audit_detail.workflow_remark = workflow_remark
audit_detail.audit_auth_groups = ''
audit_detail.current_audit = '-1'
audit_detail.next_audit = '-1'
audit_detail.current_status = WorkflowDict.workflow_status['audit_success'] # 审核通过
audit_detail.create_user = create_user
audit_detail.create_user_display = request.user.display
audit_detail.save()
result['data'] = {'workflow_status': WorkflowDict.workflow_status['audit_success']}
result['msg'] = '无审核配置，直接审核通过'
# 增加工单日志
self.add_workflow_log(audit_id=audit_detail.audit_id,
operation_type=0,
operation_type_desc='提交',
operation_info='无需审批，系统直接审核通过',
operator=audit_detail.create_user,
operator_display=audit_detail.create_user_display
)
else:
# 向审核主表插入待审核数据
audit_detail = WorkflowAudit()
audit_detail.group_id = group_id
audit_detail.group_name = group_name
audit_detail.workflow_id = workflow_id
audit_detail.workflow_type = workflow_type
audit_detail.workflow_title = workflow_title
audit_detail.workflow_remark = workflow_remark
audit_detail.audit_auth_groups = ','.join(audit_auth_groups_list)
audit_detail.current_audit = audit_auth_groups_list[0]
# 判断有无下级审核
if len(audit_auth_groups_list) == 1:
audit_detail.next_audit = '-1'
else:
audit_detail.next_audit = audit_auth_groups_list[1]
audit_detail.current_status = WorkflowDict.workflow_status['audit_wait']
audit_detail.create_user = create_user
audit_detail.create_user_display = request.user.display
audit_detail.save()
result['data'] = {'workflow_status': WorkflowDict.workflow_status['audit_wait']}
# 增加工单日志
audit_auth_group, current_audit_auth_group = self.review_info(workflow_id, workflow_type)
self.add_workflow_log(audit_id=audit_detail.audit_id,
operation_type=0,
operation_type_desc='提交',
operation_info='等待审批，审批流程：{}'.format(audit_auth_group),
operator=audit_detail.create_user,
operator_display=audit_detail.create_user_display
)
# 消息通知
sys_config = SysConfig().sys_config
if sys_config.get('mail') or sys_config.get('ding'):
# 再次获取审核信息
audit_info = WorkflowAudit.objects.get(audit_id=audit_detail.audit_id)
workflow_url = "{}://{}/workflow/{}".format(request.scheme, request.get_host(), audit_detail.audit_id)
email_cc = kwargs.get('list_cc_addr', [])
send_msg(audit_info=audit_info, workflow_url=workflow_url, email_cc=email_cc)
# 返回添加结果
return result
def auditworkflow(self, request, audit_id, audit_status, audit_user, audit_remark):
result = {'status': 0, 'msg': 'ok', 'data': 0}
audit_detail = WorkflowAudit.objects.get(audit_id=audit_id)
# 不同审核状态
if audit_status == WorkflowDict.workflow_status['audit_success']:
# 判断当前工单是否为待审核状态
if audit_detail.current_status != WorkflowDict.workflow_status['audit_wait']:
result['msg'] = '工单不是待审核状态，请返回刷新'
raise Exception(result['msg'])
# 判断是否还有下一级审核
if audit_detail.next_audit == '-1':
# 更新主表审核状态为审核通过
audit_result = WorkflowAudit()
audit_result.audit_id = audit_id
audit_result.current_audit = '-1'
audit_result.current_status = WorkflowDict.workflow_status['audit_success']
audit_result.save(update_fields=['current_audit', 'current_status'])
else:
# 更新主表审核下级审核组和当前审核组
audit_result = WorkflowAudit()
audit_result.audit_id = audit_id
audit_result.current_status = WorkflowDict.workflow_status['audit_wait']
audit_result.current_audit = audit_detail.next_audit
# 判断后续是否还有下下一级审核组
audit_auth_groups_list = audit_detail.audit_auth_groups.split(',')
for index, auth_group in enumerate(audit_auth_groups_list):
if auth_group == audit_detail.next_audit:
# 无下下级审核组
if index == len(audit_auth_groups_list) - 1:
audit_result.next_audit = '-1'
break
# 存在下下级审核组
else:
audit_result.next_audit = audit_auth_groups_list[index + 1]
audit_result.save(update_fields=['current_audit', 'next_audit', 'current_status'])
# 插入审核明细数据
audit_detail_result = WorkflowAuditDetail()
audit_detail_result.audit_id = audit_id
audit_detail_result.audit_user = audit_user
audit_detail_result.audit_status = WorkflowDict.workflow_status['audit_success']
audit_detail_result.audit_time = timezone.now()
audit_detail_result.remark = audit_remark
audit_detail_result.save()
# 增加工单日志
audit_auth_group, current_audit_auth_group = self.review_info(audit_detail.workflow_id,
audit_detail.workflow_type)
self.add_workflow_log(audit_id=audit_id,
operation_type=1,
operation_type_desc='审批通过',
operation_info="审批备注：{}，下级审批：{}".format(audit_remark, current_audit_auth_group),
operator=request.user.username,
operator_display=request.user.display
)
elif audit_status == WorkflowDict.workflow_status['audit_reject']:
# 判断当前工单是否为待审核状态
if audit_detail.current_status != WorkflowDict.workflow_status['audit_wait']:
result['msg'] = '工单不是待审核状态，请返回刷新'
raise Exception(result['msg'])
# 更新主表审核状态
audit_result = WorkflowAudit()
audit_result.audit_id = audit_id
audit_result.current_audit = '-1'
audit_result.next_audit = '-1'
audit_result.current_status = WorkflowDict.workflow_status['audit_reject']
audit_result.save(update_fields=['current_audit', 'next_audit', 'current_status'])
# 插入审核明细数据
audit_detail_result = WorkflowAuditDetail()
audit_detail_result.audit_id = audit_id
audit_detail_result.audit_user = audit_user
audit_detail_result.audit_status = WorkflowDict.workflow_status['audit_reject']
audit_detail_result.audit_time = timezone.now()
audit_detail_result.remark = audit_remark
audit_detail_result.save()
# 增加工单日志
self.add_workflow_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
)
elif audit_status == WorkflowDict.workflow_status['audit_abort']:
# 判断当前工单是否为待审核/审核通过状态
if audit_detail.current_status != WorkflowDict.workflow_status['audit_wait'] and \
audit_detail.current_status != WorkflowDict.workflow_status['audit_success']:
result['msg'] = '工单不是待审核态/审核通过状态，请返回刷新'
raise Exception(result['msg'])
# 更新主表审核状态
audit_result = WorkflowAudit()
audit_result.audit_id = audit_id
audit_result.current_status = WorkflowDict.workflow_status['audit_abort']
audit_result.save(update_fields=['current_status'])
# 插入审核明细数据
audit_detail_result = WorkflowAuditDetail()
audit_detail_result.audit_id = audit_id
audit_detail_result.audit_user = audit_user
audit_detail_result.audit_status = WorkflowDict.workflow_status['audit_abort']
audit_detail_result.audit_time = timezone.now()
audit_detail_result.remark = audit_remark
audit_detail_result.save()
# 增加工单日志
self.add_workflow_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:
result['msg'] = '审核异常'
raise Exception(result['msg'])
# 消息通知
sys_config = SysConfig().sys_config
if sys_config.get('mail') or sys_config.get('ding'):
# 再次获取审核信息
audit_info = WorkflowAudit.objects.get(audit_id=audit_id)
workflow_url = "{}://{}/workflow/{}".format(request.scheme, request.get_host(), audit_detail.audit_id)
send_msg(audit_info=audit_info, workflow_url=workflow_url, audit_remark=audit_remark)
# 返回审核结果
result['data'] = {'workflow_status': audit_result.current_status}
return result
class MysqlEngine(EngineBase):
def __init__(self, instance=None):
super().__init__(instance=instance)
self.config = SysConfig()
self.inc_engine = InceptionEngine() if self.config.get(
'inception') else GoInceptionEngine()
def get_connection(self, db_name=None):
# https://stackoverflow.com/questions/19256155/python-mysqldb-returning-x01-for-bit-values
conversions = MySQLdb.converters.conversions
conversions[FIELD_TYPE.BIT] = lambda data: data == b'\x01'
if self.conn:
self.thread_id = self.conn.thread_id()
return self.conn
if db_name:
self.conn = MySQLdb.connect(host=self.host,
port=self.port,
user=self.user,
passwd=self.password,
db=db_name,
charset=self.instance.charset
or 'utf8mb4',
conv=conversions,
connect_timeout=10)
else:
self.conn = MySQLdb.connect(host=self.host,
port=self.port,
user=self.user,
passwd=self.password,
charset=self.instance.charset
or 'utf8mb4',
conv=conversions,
connect_timeout=10)
self.thread_id = self.conn.thread_id()
return self.conn
@property
def name(self):
return 'MySQL'
@property
def info(self):
return 'MySQL engine'
@property
def auto_backup(self):
"""是否支持备份"""
return True
@property
def seconds_behind_master(self):
slave_status = self.query(sql='show slave status',
close_conn=False,
cursorclass=MySQLdb.cursors.DictCursor)
return slave_status.rows[0].get(
'Seconds_Behind_Master') if slave_status.rows else None
@property
def server_version(self):
def numeric_part(s):
"""Returns the leading numeric part of a string.
"""
re_numeric_part = re.compile(r"^(\d+)")
m = re_numeric_part.match(s)
if m:
return int(m.group(1))
return None
self.get_connection()
version = self.conn.get_server_info()
return tuple([numeric_part(n) for n in version.split('.')[:3]])
@property
def schema_object(self):
"""获取实例对象信息"""
url = build_database_url(host=self.host,
username=self.user,
password=self.password,
port=self.port)
return schemaobject.SchemaObject(url,
charset=self.instance.charset
or 'utf8mb4')
def kill_connection(self, thread_id):
"""终止数据库连接"""
self.query(sql=f'kill {thread_id}')
def get_all_databases(self):
"""获取数据库列表, 返回一个ResultSet"""
sql = "show databases"
result = self.query(sql=sql)
db_list = [
row[0] for row in result.rows
if row[0] not in ('information_schema', 'performance_schema',
'mysql', 'test', 'sys')
]
result.rows = db_list
return result
def get_all_tables(self, db_name, **kwargs):
"""获取table 列表, 返回一个ResultSet"""
sql = "show tables"
result = self.query(db_name=db_name, sql=sql)
tb_list = [row[0] for row in result.rows if row[0] not in ['test']]
result.rows = tb_list
return result
def get_all_columns_by_tb(self, db_name, tb_name, **kwargs):
"""获取所有字段, 返回一个ResultSet"""
sql = f"""SELECT
COLUMN_NAME,
COLUMN_TYPE,
CHARACTER_SET_NAME,
IS_NULLABLE,
COLUMN_KEY,
EXTRA,
COLUMN_COMMENT
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = '{db_name}'
AND TABLE_NAME = '{tb_name}'
ORDER BY ORDINAL_POSITION;"""
result = self.query(db_name=db_name, sql=sql)
column_list = [row[0] for row in result.rows]
result.rows = column_list
return result
def describe_table(self, db_name, tb_name, **kwargs):
"""return ResultSet 类似查询"""
sql = f"show create table `{tb_name}`;"
result = self.query(db_name=db_name, sql=sql)
return result
def query(self,
db_name=None,
sql='',
limit_num=0,
close_conn=True,
**kwargs):
"""返回 ResultSet """
result_set = ResultSet(full_sql=sql)
max_execution_time = kwargs.get('max_execution_time', 0)
cursorclass = kwargs.get('cursorclass') or MySQLdb.cursors.Cursor
try:
conn = self.get_connection(db_name=db_name)
conn.autocommit(True)
cursor = conn.cursor(cursorclass)
try:
cursor.execute(
f"set session max_execution_time={max_execution_time};")
except MySQLdb.OperationalError:
pass
effect_row = cursor.execute(sql)
if int(limit_num) > 0:
rows = cursor.fetchmany(size=int(limit_num))
else:
rows = cursor.fetchall()
fields = cursor.description
result_set.column_list = [i[0] for i in fields] if fields else []
result_set.rows = rows
result_set.affected_rows = effect_row
except Exception as e:
logger.warning(
f"MySQL语句执行报错，语句：{sql}，错误信息{traceback.format_exc()}")
result_set.error = str(e)
finally:
if close_conn:
self.close()
return result_set
def query_check(self, db_name=None, sql=''):
# 查询语句的检查、注释去除、切分
result = {
'msg': '',
'bad_query': False,
'filtered_sql': sql,
'has_star': False
}
# 删除注释语句，进行语法判断，执行第一条有效sql
try:
sql = sqlparse.format(sql, strip_comments=True)
sql = sqlparse.split(sql)[0]
result['filtered_sql'] = sql.strip()
except IndexError:
result['bad_query'] = True
result['msg'] = '没有有效的SQL语句'
if re.match(r"^select|^show|^explain", sql, re.I) is None:
result['bad_query'] = True
result['msg'] = '不支持的查询语法类型!'
if '*' in sql:
result['has_star'] = True
result['msg'] = 'SQL语句中含有 * '
# select语句先使用Explain判断语法是否正确
if re.match(r"^select", sql, re.I):
explain_result = self.query(db_name=db_name, sql=f"explain {sql}")
if explain_result.error:
result['bad_query'] = True
result['msg'] = explain_result.error
return result
def filter_sql(self, sql='', limit_num=0):
# 对查询sql增加limit限制,limit n 或 limit n,n 或 limit n offset n统一改写成limit n
sql = sql.rstrip(';').strip()
if re.match(r"^select", sql, re.I):
# LIMIT N
limit_n = re.compile(r'limit([\s]*\d+[\s]*)$', re.I)
# LIMIT N, N 或LIMIT N OFFSET N
limit_offset = re.compile(
r'limit([\s]*\d+[\s]*)(,|offset)([\s]*\d+[\s]*)$', re.I)
if limit_n.search(sql):
sql_limit = limit_n.search(sql).group(1)
limit_num = min(int(limit_num), int(sql_limit))
sql = limit_n.sub(f'limit {limit_num};', sql)
elif limit_offset.search(sql):
sql_limit = limit_offset.search(sql).group(3)
limit_num = min(int(limit_num), int(sql_limit))
sql = limit_offset.sub(f'limit {limit_num};', sql)
else:
sql = f'{sql} limit {limit_num};'
else:
sql = f'{sql};'
return sql
def query_masking(self, db_name=None, sql='', resultset=None):
"""传入 sql语句, db名, 结果集,
返回一个脱敏后的结果集"""
# 仅对select语句脱敏
if re.match(r"^select", sql, re.I):
mask_result = data_masking(self.instance, db_name, sql, resultset)
else:
mask_result = resultset
return mask_result
def execute_check(self, db_name=None, sql=''):
"""上线单执行前的检查, 返回Review set"""
# 进行Inception检查，获取检测结果
try:
inc_check_result = self.inc_engine.execute_check(
instance=self.instance, db_name=db_name, sql=sql)
except Exception as e:
logger.debug(
f"{self.inc_engine.name}检测语句报错：错误信息{traceback.format_exc()}")
raise RuntimeError(
f"{self.inc_engine.name}检测语句报错，请注意检查系统配置中{self.inc_engine.name}配置，错误信息：\n{e}"
)
# 判断Inception检测结果
if inc_check_result.error:
logger.debug(
f"{self.inc_engine.name}检测语句报错：错误信息{inc_check_result.error}")
raise RuntimeError(
f"{self.inc_engine.name}检测语句报错，错误信息：\n{inc_check_result.error}"
)
# 禁用/高危语句检查
check_critical_result = ReviewSet(full_sql=sql)
line = 1
critical_ddl_regex = self.config.get('critical_ddl_regex', '')
p = re.compile(critical_ddl_regex)
check_critical_result.syntax_type = 2 # TODO 工单类型 0、其他 1、DDL，2、DML
for row in inc_check_result.rows:
statement = row.sql
# 去除注释
statement = remove_comments(statement, db_type='mysql')
# 禁用语句
if re.match(r"^select", statement.lower()):
check_critical_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='驳回不支持语句',
errormessage='仅支持DML和DDL语句，查询语句请使用SQL查询功能！',
sql=statement)
# 高危语句
elif critical_ddl_regex and p.match(statement.strip().lower()):
check_critical_result.is_critical = True
result = ReviewResult(id=line,
errlevel=2,
stagestatus='驳回高危SQL',
errormessage='禁止提交匹配' +
critical_ddl_regex + '条件的语句！',
sql=statement)
# 正常语句
else:
result = ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=statement,
affected_rows=0,
execute_time=0,
)
# 没有找出DDL语句的才继续执行此判断
if check_critical_result.syntax_type == 2:
if get_syntax_type(statement, parser=False,
db_type='mysql') == 'DDL':
check_critical_result.syntax_type = 1
check_critical_result.rows += [result]
# 遇到禁用和高危语句直接返回
if check_critical_result.is_critical:
check_critical_result.error_count += 1
return check_critical_result
line += 1
return inc_check_result
def execute_workflow(self, workflow):
"""执行上线单，返回Review set"""
# 判断实例是否只读
read_only = self.query(sql='SELECT @@global.read_only;').rows[0][0]
if read_only in (1, 'ON'):
result = ReviewSet(
full_sql=workflow.sqlworkflowcontent.sql_content,
rows=[
ReviewResult(id=1,
errlevel=2,
stagestatus='Execute Failed',
errormessage='实例read_only=1，禁止执行变更语句!',
sql=workflow.sqlworkflowcontent.sql_content)
])
result.error = '实例read_only=1，禁止执行变更语句!',
return result
# TODO 原生执行
# if workflow.is_manual == 1:
# return self.execute(db_name=workflow.db_name, sql=workflow.sqlworkflowcontent.sql_content)
# inception执行
return self.inc_engine.execute(workflow)
def execute(self, db_name=None, sql='', close_conn=True):
"""原生执行语句"""
result = ResultSet(full_sql=sql)
conn = self.get_connection(db_name=db_name)
try:
cursor = conn.cursor()
for statement in sqlparse.split(sql):
cursor.execute(statement)
conn.commit()
cursor.close()
except Exception as e:
logger.warning(
f"MySQL语句执行报错，语句：{sql}，错误信息{traceback.format_exc()}")
result.error = str(e)
if close_conn:
self.close()
return result
def get_rollback(self, workflow):
"""通过inception获取回滚语句列表"""
inception_engine = InceptionEngine()
return inception_engine.get_rollback(workflow)
def get_variables(self, variables=None):
"""获取实例参数"""
if variables:
variables = "','".join(variables) if isinstance(
variables, list) else "','".join(list(variables))
db = 'performance_schema' if self.server_version > (
5, 7) else 'information_schema'
sql = f"""select * from {db}.global_variables where variable_name in ('{variables}');"""
else:
sql = "show global variables;"
return self.query(sql=sql)
def set_variable(self, variable_name, variable_value):
"""修改实例参数值"""
sql = f"""set global {variable_name}={variable_value};"""
return self.query(sql=sql)
def osc_control(self, **kwargs):
"""控制osc执行，获取进度、终止、暂停、恢复等
get、kill、pause、resume
"""
return self.inc_engine.osc_control(**kwargs)
def close(self):
if self.conn:
self.conn.close()
self.conn = None
class ClickHouseEngine(EngineBase):
def __init__(self, instance=None):
super(ClickHouseEngine, self).__init__(instance=instance)
self.config = SysConfig()
def get_connection(self, db_name=None):
if self.conn:
return self.conn
if db_name:
self.conn = connect(host=self.host,
port=self.port,
user=self.user,
password=self.password,
database=db_name,
connect_timeout=10)
else:
self.conn = connect(host=self.host,
port=self.port,
user=self.user,
password=self.password,
connect_timeout=10)
return self.conn
@property
def name(self):
return 'ClickHouse'
@property
def info(self):
return 'ClickHouse engine'
@property
def auto_backup(self):
"""是否支持备份"""
return False
@property
def server_version(self):
sql = "select value from system.build_options where name = 'VERSION_FULL';"
result = self.query(sql=sql)
version = result.rows[0][0].split(' ')[1]
return tuple([int(n) for n in version.split('.')[:3]])
def get_table_engine(self, tb_name):
"""获取某个table的engine type"""
sql = f"""select engine
from system.tables
where database='{tb_name.split('.')[0]}'
and name='{tb_name.split('.')[1]}'"""
query_result = self.query(sql=sql)
if query_result.rows:
result = {'status': 1, 'engine': query_result.rows[0][0]}
else:
result = {'status': 0, 'engine': 'None'}
return result
def get_all_databases(self):
"""获取数据库列表, 返回一个ResultSet"""
sql = "show databases"
result = self.query(sql=sql)
db_list = [
row[0] for row in result.rows
if row[0] not in ('system', 'INFORMATION_SCHEMA',
'information_schema', 'datasets')
]
result.rows = db_list
return result
def get_all_tables(self, db_name, **kwargs):
"""获取table 列表, 返回一个ResultSet"""
sql = "show tables"
result = self.query(db_name=db_name, sql=sql)
tb_list = [row[0] for row in result.rows]
result.rows = tb_list
return result
def get_all_columns_by_tb(self, db_name, tb_name, **kwargs):
"""获取所有字段, 返回一个ResultSet"""
sql = f"""select
name,
type,
comment
from
system.columns
where
database = '{db_name}'
and table = '{tb_name}';"""
result = self.query(db_name=db_name, sql=sql)
column_list = [row[0] for row in result.rows]
result.rows = column_list
return result
def describe_table(self, db_name, tb_name, **kwargs):
"""return ResultSet 类似查询"""
sql = f"show create table `{tb_name}`;"
result = self.query(db_name=db_name, sql=sql)
result.rows[0] = (tb_name, ) + (result.rows[0][0].replace(
'(', '(\n ').replace(',', ',\n '), )
return result
def query(self,
db_name=None,
sql='',
limit_num=0,
close_conn=True,
**kwargs):
"""返回 ResultSet """
result_set = ResultSet(full_sql=sql)
try:
conn = self.get_connection(db_name=db_name)
cursor = conn.cursor()
cursor.execute(sql)
if int(limit_num) > 0:
rows = cursor.fetchmany(size=int(limit_num))
else:
rows = cursor.fetchall()
fields = cursor.description
result_set.column_list = [i[0] for i in fields] if fields else []
result_set.rows = rows
result_set.affected_rows = len(rows)
except Exception as e:
logger.warning(f"ClickHouse语句执行报错，语句：{sql}，错误信息{e}")
result_set.error = str(e).split('Stack trace')[0]
finally:
if close_conn:
self.close()
return result_set
def query_check(self, db_name=None, sql=''):
# 查询语句的检查、注释去除、切分
result = {
'msg': '',
'bad_query': False,
'filtered_sql': sql,
'has_star': False
}
# 删除注释语句，进行语法判断，执行第一条有效sql
try:
sql = sqlparse.format(sql, strip_comments=True)
sql = sqlparse.split(sql)[0]
result['filtered_sql'] = sql.strip()
except IndexError:
result['bad_query'] = True
result['msg'] = '没有有效的SQL语句'
if re.match(r"^select|^show|^explain", sql, re.I) is None:
result['bad_query'] = True
result['msg'] = '不支持的查询语法类型!'
if '*' in sql:
result['has_star'] = True
result['msg'] = 'SQL语句中含有 * '
# clickhouse 20.6.3版本开始正式支持explain语法
if re.match(r"^explain", sql,
re.I) and self.server_version < (20, 6, 3):
result['bad_query'] = True
result['msg'] = f"当前ClickHouse实例版本低于20.6.3，不支持explain!"
# select语句先使用Explain判断语法是否正确
if re.match(r"^select", sql,
re.I) and self.server_version >= (20, 6, 3):
explain_result = self.query(db_name=db_name, sql=f"explain {sql}")
if explain_result.error:
result['bad_query'] = True
result['msg'] = explain_result.error
return result
def filter_sql(self, sql='', limit_num=0):
# 对查询sql增加limit限制,limit n 或 limit n,n 或 limit n offset n统一改写成limit n
sql = sql.rstrip(';').strip()
if re.match(r"^select", sql, re.I):
# LIMIT N
limit_n = re.compile(r'limit\s+(\d+)\s*$', re.I)
# LIMIT M OFFSET N
limit_offset = re.compile(r'limit\s+(\d+)\s+offset\s+(\d+)\s*$',
re.I)
# LIMIT M,N
offset_comma_limit = re.compile(r'limit\s+(\d+)\s*,\s*(\d+)\s*$',
re.I)
if limit_n.search(sql):
sql_limit = limit_n.search(sql).group(1)
limit_num = min(int(limit_num), int(sql_limit))
sql = limit_n.sub(f'limit {limit_num};', sql)
elif limit_offset.search(sql):
sql_limit = limit_offset.search(sql).group(1)
sql_offset = limit_offset.search(sql).group(2)
limit_num = min(int(limit_num), int(sql_limit))
sql = limit_offset.sub(
f'limit {limit_num} offset {sql_offset};', sql)
elif offset_comma_limit.search(sql):
sql_offset = offset_comma_limit.search(sql).group(1)
sql_limit = offset_comma_limit.search(sql).group(2)
limit_num = min(int(limit_num), int(sql_limit))
sql = offset_comma_limit.sub(
f'limit {sql_offset},{limit_num};', sql)
else:
sql = f'{sql} limit {limit_num};'
else:
sql = f'{sql};'
return sql
def explain_check(self, check_result, db_name=None, line=0, statement=''):
"""使用explain ast检查sql语法, 返回Review set"""
result = ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=statement,
affected_rows=0,
execute_time=0,
)
# clickhouse版本>=21.1.2 explain ast才支持非select语句检查
if self.server_version >= (21, 1, 2):
explain_result = self.query(db_name=db_name,
sql=f"explain ast {statement}")
if explain_result.error:
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='驳回未通过检查SQL',
errormessage=f'explain语法检查错误：{explain_result.error}',
sql=statement)
return result
def execute_check(self, db_name=None, sql=''):
"""上线单执行前的检查, 返回Review set"""
sql = sqlparse.format(sql, strip_comments=True)
sql_list = sqlparse.split(sql)
# 禁用/高危语句检查
check_result = ReviewSet(full_sql=sql)
line = 1
critical_ddl_regex = self.config.get('critical_ddl_regex', '')
p = re.compile(critical_ddl_regex)
check_result.syntax_type = 2 # TODO 工单类型 0、其他 1、DDL，2、DML
for statement in sql_list:
statement = statement.rstrip(';')
# 禁用语句
if re.match(r"^select|^show", statement.lower()):
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='驳回不支持语句',
errormessage='仅支持DML和DDL语句，查询语句请使用SQL查询功能！',
sql=statement)
# 高危语句
elif critical_ddl_regex and p.match(statement.strip().lower()):
check_result.is_critical = True
result = ReviewResult(id=line,
errlevel=2,
stagestatus='驳回高危SQL',
errormessage='禁止提交匹配' +
critical_ddl_regex + '条件的语句！',
sql=statement)
# alter语句
elif re.match(r"^alter", statement.lower()):
# alter table语句
if re.match(r"^alter\s+table\s+(.+?)\s+", statement.lower()):
table_name = re.match(r"^alter\s+table\s+(.+?)\s+",
statement.lower(), re.M).group(1)
if '.' not in table_name:
table_name = f"{db_name}.{table_name}"
table_engine = self.get_table_engine(table_name)['engine']
table_exist = self.get_table_engine(table_name)['status']
if table_exist == 1:
if not table_engine.endswith(
'MergeTree') and table_engine not in (
'Merge', 'Distributed'):
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='驳回不支持SQL',
errormessage=
'ALTER TABLE仅支持*MergeTree，Merge以及Distributed等引擎表！',
sql=statement)
else:
# delete与update语句，实际是alter语句的变种
if re.match(
r"^alter\s+table\s+(.+?)\s+(delete|update)\s+",
statement.lower()):
if not table_engine.endswith('MergeTree'):
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='驳回不支持SQL',
errormessage=
'DELETE与UPDATE仅支持*MergeTree引擎表！',
sql=statement)
else:
result = self.explain_check(
check_result, db_name, line, statement)
else:
result = self.explain_check(
check_result, db_name, line, statement)
else:
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='表不存在',
errormessage=f'表 {table_name} 不存在！',
sql=statement)
# 其他alter语句
else:
result = self.explain_check(check_result, db_name, line,
statement)
# truncate语句
elif re.match(r"^truncate\s+table\s+(.+?)(\s|$)",
statement.lower()):
table_name = re.match(r"^truncate\s+table\s+(.+?)(\s|$)",
statement.lower(), re.M).group(1)
if '.' not in table_name:
table_name = f"{db_name}.{table_name}"
table_engine = self.get_table_engine(table_name)['engine']
table_exist = self.get_table_engine(table_name)['status']
if table_exist == 1:
if table_engine in ('View', 'File,', 'URL', 'Buffer',
'Null'):
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='驳回不支持SQL',
errormessage=
'TRUNCATE不支持View,File,URL,Buffer和Null表引擎！',
sql=statement)
else:
result = self.explain_check(check_result, db_name,
line, statement)
else:
check_result.is_critical = True
result = ReviewResult(id=line,
errlevel=2,
stagestatus='表不存在',
errormessage=f'表 {table_name} 不存在！',
sql=statement)
# insert语句，explain无法正确判断，暂时只做表存在性检查与简单关键字匹配
elif re.match(r"^insert", statement.lower()):
if re.match(
r"^insert\s+into\s+(.+?)(\s+|\s*\(.+?)(values|format|select)(\s+|\()",
statement.lower()):
table_name = re.match(
r"^insert\s+into\s+(.+?)(\s+|\s*\(.+?)(values|format|select)(\s+|\()",
statement.lower(), re.M).group(1)
if '.' not in table_name:
table_name = f"{db_name}.{table_name}"
table_exist = self.get_table_engine(table_name)['status']
if table_exist == 1:
result = ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=statement,
affected_rows=0,
execute_time=0,
)
else:
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='表不存在',
errormessage=f'表 {table_name} 不存在！',
sql=statement)
else:
check_result.is_critical = True
result = ReviewResult(id=line,
errlevel=2,
stagestatus='驳回不支持SQL',
errormessage='INSERT语法不正确！',
sql=statement)
# 其他语句使用explain ast简单检查
else:
result = self.explain_check(check_result, db_name, line,
statement)
# 没有找出DDL语句的才继续执行此判断
if check_result.syntax_type == 2:
if get_syntax_type(statement, parser=False,
db_type='mysql') == 'DDL':
check_result.syntax_type = 1
check_result.rows += [result]
# 遇到禁用和高危语句直接返回
if check_result.is_critical:
check_result.error_count += 1
return check_result
line += 1
return check_result
def execute_workflow(self, workflow):
"""执行上线单，返回Review set"""
sql = workflow.sqlworkflowcontent.sql_content
execute_result = ReviewSet(full_sql=sql)
sqls = sqlparse.format(sql, strip_comments=True)
sql_list = sqlparse.split(sqls)
line = 1
for statement in sql_list:
with FuncTimer() as t:
result = self.execute(db_name=workflow.db_name,
sql=statement,
close_conn=True)
if not result.error:
execute_result.rows.append(
ReviewResult(
id=line,
errlevel=0,
stagestatus='Execute Successfully',
errormessage='None',
sql=statement,
affected_rows=0,
execute_time=t.cost,
))
line += 1
else:
# 追加当前报错语句信息到执行结果中
execute_result.error = result.error
execute_result.rows.append(
ReviewResult(
id=line,
errlevel=2,
stagestatus='Execute Failed',
errormessage=f'异常信息：{result.error}',
sql=statement,
affected_rows=0,
execute_time=0,
))
line += 1
# 报错语句后面的语句标记为审核通过、未执行，追加到执行结果中
for statement in sql_list[line - 1:]:
execute_result.rows.append(
ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage=f'前序语句失败, 未执行',
sql=statement,
affected_rows=0,
execute_time=0,
))
line += 1
break
return execute_result
def execute(self, db_name=None, sql='', close_conn=True):
"""原生执行语句"""
result = ResultSet(full_sql=sql)
conn = self.get_connection(db_name=db_name)
try:
cursor = conn.cursor()
for statement in sqlparse.split(sql):
cursor.execute(statement)
cursor.close()
except Exception as e:
logger.warning(f"ClickHouse语句执行报错，语句：{sql}，错误信息{e}")
result.error = str(e).split('Stack trace')[0]
if close_conn:
self.close()
return result
def close(self):
if self.conn:
self.conn.close()
self.conn = None
def cancel(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('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.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:
# 消息通知
sys_config = SysConfig()
if sys_config.get('mail') or sys_config.get('ding'):
# 再次获取审核信息
audit_detail = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview'])
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)
async_task(notify,
audit_info=audit_detail,
workflow_url=workflow_url,
audit_remark=audit_remark,
timeout=60)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def query(request):
instance_name = request.POST.get('instance_name')
sql_content = request.POST.get('sql_content')
db_name = request.POST.get('db_name')
limit_num = request.POST.get('limit_num')
result = {'status': 0, 'msg': 'ok', 'data': {}}
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result['status'] = 1
result['msg'] = '实例不存在'
return result
# 服务器端参数验证
if sql_content is None or db_name is None or instance_name is None or limit_num is None:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
sql_content = sql_content.strip()
archer_config = SysConfig()
if archer_config.get('disable_star'):
if '*' in sql_content:
result['status'] = 1
result['msg'] = '不允许 * 标记, 请指定具体字段名.'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 获取用户信息
user = request.user
# 过滤注释语句和非查询的语句
sql_content = ''.join(
map(
lambda x: re.compile(r'(^--\s+.*|^/\*.*\*/;\s*$)').sub(
'', x, count=1), sql_content.splitlines(1))).strip()
# 去除空行
sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content)
sql_list = sql_content.strip().split('\n')
for sql in sql_list:
if re.match(r"^select|^show|^explain", sql.lower()):
break
else:
result['status'] = 1
result['msg'] = '仅支持^select|^show|^explain语法，请联系管理员！'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 执行第一条有效sql
sql_content = sqlparse.split(sql_content)[0].rstrip(';')
try:
# 查询权限校验
priv_check_info = query_priv_check(user, instance_name, db_name,
sql_content, limit_num)
if priv_check_info['status'] == 0:
limit_num = priv_check_info['data']['limit_num']
priv_check = priv_check_info['data']['priv_check']
else:
return HttpResponse(json.dumps(priv_check_info),
content_type='application/json')
if re.match(r"^explain", sql_content.lower()):
limit_num = 0
query_engine = get_engine(instance=instance)
filter_result = query_engine.query_check(db_name=db_name,
sql=sql_content,
limit_num=limit_num)
if filter_result.get('bad_query'):
result['status'] = 1
result['msg'] = filter_result.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
else:
sql_content = filter_result['filtered_sql']
sql_content = sql_content + ';'
# 执行查询语句,统计执行时间
t_start = time.time()
query_result = query_engine.query(db_name=str(db_name),
sql=sql_content,
limit_num=limit_num)
t_end = time.time()
query_result.query_time = "%5s" % "{:.4f}".format(t_end - t_start)
# 数据脱敏，同样需要检查配置，是否开启脱敏，语法树解析是否允许出错继续执行
hit_rule = 0 if re.match(r"^select", sql_content.lower(
)) else 2 # 查询是否命中脱敏规则，0, '未知', 1, '命中', 2, '未命中'
masking = 2 # 查询结果是否正常脱敏，1, '是', 2, '否'
t_start = time.time()
# 仅对查询语句进行脱敏
if SysConfig().sys_config.get('data_masking') and re.match(
r"^select", sql_content.lower()):
try:
query_result = query_engine.query_masking(
db_name=db_name, sql=sql_content, resultset=query_result)
if SysConfig().sys_config.get(
'query_check') and query_result.is_critical == True:
return HttpResponse(json.dumps(masking_result),
content_type='application/json')
else:
# 实际未命中, 则显示为未做脱敏
if query_result.is_masked:
masking = 1
hit_rule = 1
except Exception:
logger.error(traceback.format_exc())
# 报错, 未脱敏, 未命中
hit_rule = 2
masking = 2
if SysConfig().sys_config.get('query_check'):
result['status'] = 1
result['msg'] = '脱敏数据报错,请联系管理员'
return HttpResponse(json.dumps(result),
content_type='application/json')
t_end = time.time()
query_result.mask_time = "%5s" % "{:.4f}".format(t_end - t_start)
sql_result = query_result.__dict__
sql_result['masking_cost_time'] = query_result.mask_time
sql_result['cost_time'] = query_result.query_time
result['data'] = sql_result
# 成功的查询语句记录存入数据库
if sql_result.get('error'):
pass
else:
query_log = QueryLog()
query_log.username = user.username
query_log.user_display = user.display
query_log.db_name = db_name
query_log.instance_name = instance_name
query_log.sqllog = sql_content
if int(limit_num) == 0:
limit_num = int(sql_result['affected_rows'])
else:
limit_num = min(int(limit_num),
int(sql_result['affected_rows']))
query_log.effect_row = limit_num
query_log.cost_time = query_result.query_time
query_log.priv_check = priv_check
query_log.hit_rule = hit_rule
query_log.masking = masking
# 防止查询超时
try:
query_log.save()
except:
connection.close()
query_log.save()
except Exception as e:
logger.error(traceback.format_exc())
result['status'] = 1
result['msg'] = str(e)
# 返回查询结果
try:
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
except Exception:
return HttpResponse(json.dumps(result,
default=str,
bigint_as_string=True,
encoding='latin1'),
content_type='application/json')
def execute_check(self, db_name=None, sql='', close_conn=True):
"""
上线单执行前的检查, 返回Review set
update by Jan.song 20200302
使用explain对数据修改预计进行检测
"""
config = SysConfig()
check_result = ReviewSet(full_sql=sql)
# explain支持的语法
explain_re = r"^merge|^update|^delete|^insert|^create\s+table|^create\s+index|^create\s+unique\s+index"
# 禁用/高危语句检查
line = 1
# 保存SQL中的新建对象
object_name_list = set()
critical_ddl_regex = config.get('critical_ddl_regex', '')
p = re.compile(critical_ddl_regex)
check_result.syntax_type = 2 # TODO 工单类型 0、其他 1、DDL，2、DML
try:
sqlitemList = get_full_sqlitem_list(sql, db_name)
for sqlitem in sqlitemList:
sql_lower = sqlitem.statement.lower().rstrip(';')
# 禁用语句
if re.match(r"^select|^with|^explain", sql_lower):
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='驳回不支持语句',
errormessage='仅支持DML和DDL语句，查询语句请使用SQL查询功能！',
sql=sqlitem.statement)
# 高危语句
elif critical_ddl_regex and p.match(sql_lower.strip()):
check_result.is_critical = True
result = ReviewResult(id=line,
errlevel=2,
stagestatus='驳回高危SQL',
errormessage='禁止提交匹配' +
critical_ddl_regex + '条件的语句！',
sql=sqlitem.statement)
# 驳回未带where数据修改语句，如确实需做全部删除或更新，显示的带上where 1=1
elif re.match(r"^update((?!where).)*$|^delete((?!where).)*$",
sql_lower):
check_result.is_critical = True
result = ReviewResult(id=line,
errlevel=2,
stagestatus='驳回未带where数据修改',
errormessage='数据修改需带where条件！',
sql=sqlitem.statement)
# 驳回事务控制，会话控制SQL
elif re.match(r"^set|^rollback|^exit", sql_lower):
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='SQL中不能包含^set|^rollback|^exit',
errormessage='SQL中不能包含^set|^rollback|^exit',
sql=sqlitem.statement)
# 通过explain对SQL做语法语义检查
elif re.match(explain_re,
sql_lower) and sqlitem.stmt_type == 'SQL':
if self.check_create_index_table(
db_name=db_name,
sql=sql_lower,
object_name_list=object_name_list):
object_name = self.get_sql_first_object_name(
sql=sql_lower)
if '.' in object_name:
object_name = object_name
else:
object_name = f"""{db_name}.{object_name}"""
object_name_list.add(object_name)
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='WARNING:新建表的新建索引语句暂无法检测！',
errormessage='WARNING:新建表的新建索引语句暂无法检测！',
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
sql=sqlitem.statement)
elif len(object_name_list) > 0 and self.get_dml_table(
db_name=db_name,
sql=sql_lower,
object_name_list=object_name_list):
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='WARNING:新建表的数据修改暂无法检测！',
errormessage='WARNING:新建表的数据修改暂无法检测！',
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
sql=sqlitem.statement)
else:
result_set = self.explain_check(db_name=db_name,
sql=sqlitem.statement,
close_conn=False)
if result_set['msg']:
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus='explain语法检查未通过！',
errormessage=result_set['msg'],
sql=sqlitem.statement)
else:
# 对create table\create index\create unique index语法做对象存在性检测
if re.match(
r"^create\s+table|^create\s+index|^create\s+unique\s+index",
sql_lower):
object_name = self.get_sql_first_object_name(
sql=sql_lower)
# 保存create对象对后续SQL做存在性判断
if '.' in object_name:
object_name = object_name
else:
object_name = f"""{db_name}.{object_name}"""
if self.object_name_check(
db_name=db_name,
object_name=object_name
) or object_name in object_name_list:
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus=f"""{object_name}对象已经存在！""",
errormessage=
f"""{object_name}对象已经存在！""",
sql=sqlitem.statement)
else:
object_name_list.add(object_name)
if result_set['rows'] > 1000:
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='影响行数大于1000，请关注',
errormessage='影响行数大于1000，请关注',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=result_set['rows'],
execute_time=0,
)
else:
result = ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=result_set['rows'],
execute_time=0,
)
else:
if result_set['rows'] > 1000:
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='影响行数大于1000，请关注',
errormessage='影响行数大于1000，请关注',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=result_set['rows'],
execute_time=0,
)
else:
result = ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=result_set['rows'],
execute_time=0,
)
# 其它无法用explain判断的语句
else:
# 对alter table做对象存在性检查
if re.match(r"^alter\s+table\s", sql_lower):
object_name = self.get_sql_first_object_name(
sql=sql_lower)
if '.' in object_name:
object_name = object_name
else:
object_name = f"""{db_name}.{object_name}"""
if not self.object_name_check(
db_name=db_name, object_name=object_name
) and object_name not in object_name_list:
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus=f"""{object_name}对象不存在！""",
errormessage=f"""{object_name}对象不存在！""",
sql=sqlitem.statement)
else:
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='当前平台，此语法不支持审核！',
errormessage='当前平台，此语法不支持审核！',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=0,
execute_time=0,
)
# 对create做对象存在性检查
elif re.match(r"^create", sql_lower):
object_name = self.get_sql_first_object_name(
sql=sql_lower)
if '.' in object_name:
object_name = object_name
else:
object_name = f"""{db_name}.{object_name}"""
if self.object_name_check(
db_name=db_name, object_name=object_name
) or object_name in object_name_list:
check_result.is_critical = True
result = ReviewResult(
id=line,
errlevel=2,
stagestatus=f"""{object_name}对象已经存在！""",
errormessage=f"""{object_name}对象已经存在！""",
sql=sqlitem.statement)
else:
object_name_list.add(object_name)
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='当前平台，此语法不支持审核！',
errormessage='当前平台，此语法不支持审核！',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=0,
execute_time=0,
)
else:
result = ReviewResult(
id=line,
errlevel=1,
stagestatus='当前平台，此语法不支持审核！',
errormessage='当前平台，此语法不支持审核！',
sql=sqlitem.statement,
stmt_type=sqlitem.stmt_type,
object_owner=sqlitem.object_owner,
object_type=sqlitem.object_type,
object_name=sqlitem.object_name,
affected_rows=0,
execute_time=0,
)
# 判断工单类型
if get_syntax_type(sql=sqlitem.statement,
db_type='oracle') == 'DDL':
check_result.syntax_type = 1
check_result.rows += [result]
# 遇到禁用和高危语句直接返回，提高效率
if check_result.is_critical:
check_result.error_count += 1
return check_result
line += 1
except Exception as e:
logger.warning(
f"Oracle 语句执行报错，第{line}个SQL：{sqlitem.statement}，错误信息{traceback.format_exc()}"
)
check_result.error = str(e)
finally:
if close_conn:
self.close()
return check_result
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 notify_for_audit(audit_id, **kwargs):
"""
工作流消息通知，不包含工单执行结束的通知
:param audit_id:
:param kwargs:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
sys_config = SysConfig()
wx_status = sys_config.get('wx')
if not sys_config.get('mail') and not sys_config.get('ding') and not wx_status:
logger.info('未开启消息通知，可在系统设置中开启')
return None
wx_msg_content = ''
# 获取审核信息
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)
# 企业微信消息格式
if wx_status:
wx_msg_content = "[【{}】新的工单申请（点击查看）]({})\n" \
">发起时间：<font color=\"comment\">{}</font>\n" \
">发起人：<font color=\"comment\">{}</font>\n" \
">组：<font color=\"comment\">{}</font>\n" \
">目标实例：<font color=\"comment\">{}</font>\n"\
">数据库：<font color=\"comment\">{}</font>\n" \
">审批流程：<font color=\"comment\">{}</font>\n" \
">当前审批：<font color=\"comment\">{}</font>\n" \
">工单名称：<font color=\"comment\">{}</font>\n".format(
workflow_type_display, workflow_url, 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
)
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)
if wx_status:
wx_msg_content = "[【{}】工单审核通过（点击查看）]({})\n" \
">发起时间：<font color=\"comment\">{}</font>\n" \
">发起人：<font color=\"comment\">{}</font>\n" \
">组：<font color=\"comment\">{}</font>\n" \
">目标实例：<font color=\"comment\">{}</font>\n" \
">数据库：<font color=\"comment\">{}</font>\n" \
">审批流程：<font color=\"comment\">{}</font>\n" \
">工单名称：<font color=\"comment\">{}</font>\n".format(
workflow_type_display, workflow_url, workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from, group_name, instance, db_name, workflow_auditors, workflow_title
)
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)
if wx_status:
wx_msg_content = "[【{}】工单被驳回（点击查看）]({})\n" \
">发起时间：<font color=\"comment\">{}</font>\n" \
">目标实例：<font color=\"comment\">{}</font>\n" \
">数据库：<font color=\"comment\">{}</font>\n" \
">工单名称：<font color=\"comment\">{}</font>\n" \
">驳回原因：<font color=\"comment\">{}</font>\n" \
"提醒：此工单审核不通过，请按照驳回原因进行修改！".format(
workflow_type_display, workflow_url, workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
instance, db_name, workflow_title, 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)
if wx_status:
wx_msg_content = "[【{}】提交人主动终止工单（点击查看）]({})\n" \
">发起时间：<font color=\"comment\">{}</font>\n" \
">发起人：<font color=\"comment\">{}</font>\n" \
">组：<font color=\"comment\">{}</font>\n" \
">目标实例：<font color=\"comment\">{}</font>\n" \
">数据库：<font color=\"comment\">{}</font>\n" \
">工单名称：<font color=\"comment\">{}</font>\n" \
">终止原因：<font color=\"comment\">{}</font>\n".format(
workflow_type_display, workflow_url, workflow_detail.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow_from, group_name, instance, db_name, workflow_title, workflow_audit_remark
)
else:
raise Exception('工单状态不正确')
# 处理接收人信息
msg_to_email = [user.email for user in msg_to if user.email]
msg_to_ding_user = [user.ding_user_id for user in msg_to if user.ding_user_id]
# 发送通知
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') and webhook_url:
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
if sys_config.get('ding_to_person') and msg_to_ding_user:
msg_sender.send_ding2user(msg_to_ding_user, msg_title + '\n' + msg_content)
if wx_status:
user_list = []
for user in msg_to:
if user.wx_user_id:
user_list.append(user.wx_user_id)
else:
user_list.append(user.username)
# user_list = [user.wx_user_id for user in msg_to if user.wx_user_id]
msg_sender.send_wx2user(wx_msg_content, user_list)
def notify_for_execute(workflow):
"""
工单执行结束的通知
:param workflow:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
if not __notify_cnf_status():
return None
sys_config = SysConfig()
# 获取当前审批和审批流程
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工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow.engineer_display, workflow.group_name,
workflow.instance.instance_name, workflow.db_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)
# 处理接收人
dingding_webhook = ResourceGroup.objects.get(
group_id=workflow.group_id).ding_webhook
feishu_webhook = ResourceGroup.objects.get(
group_id=workflow.group_id).feishu_webhook
qywx_webhook = ResourceGroup.objects.get(
group_id=workflow.group_id).qywx_webhook
# 发送通知
__send(msg_title,
msg_content,
msg_to,
msg_cc,
dingding_webhook=dingding_webhook,
feishu_webhook=feishu_webhook,
qywx_webhook=qywx_webhook)
# DDL通知
if 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
ddl_notify_auth_group = sys_config.get('ddl_notify_auth_group',
'').split(',')
msg_to = Users.objects.filter(
groups__name__in=ddl_notify_auth_group)
# 发送通知
__send(msg_title, msg_content, msg_to, msg_cc)
def notify_for_execute(workflow):
"""
工单执行结束的通知
:param workflow:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
sys_config = SysConfig()
wx_status = sys_config.get('wx')
wx_msg_content = ''
if not sys_config.get('mail') and not sys_config.get('ding') and not sys_config.get('ding_to_person') \
and not wx_status:
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', '')))
if wx_status:
wx_msg_content = "[工单执行完毕（点击查看）]({})\n" \
">发起人：<font color=\"comment\">{}</font>\n" \
">组：<font color=\"comment\">{}</font>\n" \
">审批流程：<font color=\"comment\">{}</font>\n" \
">工单名称：<font color=\"comment\">{}</font>\n" \
">审批流程：<font color=\"comment\">{}</font>\n".format(
url, workflow.engineer_display, workflow.group_name, audit_auth_group, workflow.workflow_name,
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_to_ding_user = [user.ding_user_id for user in msg_to if user.ding_user_id]
# 判断是发送钉钉还是发送邮件
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)
if sys_config.get('ding_to_person') and msg_to_ding_user:
msg_sender.send_ding2user(msg_to_ding_user, msg_title + '\n' + msg_content)
if wx_status:
msg_to_wx_user = []
for user in msg_to:
if user.wx_user_id:
msg_to_wx_user.append(user.wx_user_id)
else:
msg_to_wx_user.append(user.username)
msg_sender.send_wx2user(wx_msg_content, msg_to_wx_user)
# 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 debug(request):
# 获取完整信息
full = request.GET.get('full')
# 系统配置
sys_config = SysConfig().sys_config
# 敏感信息处理
secret_keys = [
'inception_remote_backup_password', 'ding_app_secret',
'feishu_app_secret', 'mail_smtp_password'
]
sys_config.update({k: "******" for k in secret_keys})
# MySQL信息
cursor = connection.cursor()
mysql_info = {
'mysql_server_info': cursor.db.mysql_server_info,
'timezone_name': cursor.db.timezone_name
}
# Redis信息
try:
redis_conn = get_redis_connection("default")
full_redis_info = redis_conn.info()
redis_info = {
'redis_version': full_redis_info.get('redis_version'),
'redis_mode': full_redis_info.get('redis_mode'),
'role': full_redis_info.get('role'),
'maxmemory_human': full_redis_info.get('maxmemory_human'),
'used_memory_human': full_redis_info.get('used_memory_human'),
}
except Exception as e:
redis_info = f'获取Redis信息报错:{e}'
full_redis_info = redis_info
# django_q
try:
django_q_version = '.'.join(str(i) for i in django_q.VERSION)
broker = get_broker()
stats = Stat.get_all(broker=broker)
queue_size = broker.queue_size()
lock_size = broker.lock_size()
if lock_size:
queue_size = '{}({})'.format(queue_size, lock_size)
q_broker_stats = {
'info': broker.info(),
'Queued': queue_size,
'Success': Success.objects.count(),
'Failures': Failure.objects.count(),
}
q_cluster_stats = []
for stat in stats:
# format uptime
uptime = (timezone.now() - stat.tob).total_seconds()
hours, remainder = divmod(uptime, 3600)
minutes, seconds = divmod(remainder, 60)
uptime = '%d:%02d:%02d' % (hours, minutes, seconds)
q_cluster_stats.append({
'host': stat.host,
'cluster_id': stat.cluster_id,
'state': stat.status,
'pool': len(stat.workers),
'tq': stat.task_q_size,
'rq': stat.done_q_size,
'rc': stat.reincarnations,
'up': uptime
})
django_q_info = {
'version': django_q_version,
'conf': django_q.conf.Conf.conf,
'q_cluster_stats': q_cluster_stats
if q_cluster_stats else '没有正在运行的集群信息，请检查django_q状态',
'q_broker_stats': q_broker_stats
}
except Exception as e:
django_q_info = f'获取django_q信息报错:{e}'
# Inception和goInception信息
inception_host = sys_config.get('inception_host')
inception_port = sys_config.get('inception_port', 0)
go_inception_host = sys_config.get('go_inception_host')
go_inception_port = sys_config.get('go_inception_port', 0)
inception_remote_backup_host = sys_config.get(
'inception_remote_backup_host', '')
inception_remote_backup_port = sys_config.get(
'inception_remote_backup_port', '')
inception_remote_backup_user = sys_config.get(
'inception_remote_backup_user', '')
inception_remote_backup_password = sys_config.get(
'inception_remote_backup_password', '')
# inception
try:
inc_conn = MySQLdb.connect(host=inception_host,
port=int(inception_port),
connect_timeout=1,
cursorclass=MySQLdb.cursors.DictCursor)
cursor = inc_conn.cursor()
cursor.execute('inception get variables')
rows = cursor.fetchall()
full_inception_info = dict()
for row in rows:
full_inception_info[row.get('Variable_name')] = row.get('Value')
inception_info = {
'version':
full_inception_info.get('version'),
'max_allowed_packet':
full_inception_info.get('max_allowed_packet'),
'inception_language_code':
full_inception_info.get('inception_language_code'),
'inception_osc_on':
full_inception_info.get('inception_osc_on'),
'inception_osc_bin_dir':
full_inception_info.get('inception_osc_bin_dir'),
}
except Exception as e:
inception_info = f'获取Inception信息报错:{e}'
full_inception_info = inception_info
# goInception
try:
goinc_conn = MySQLdb.connect(host=go_inception_host,
port=int(go_inception_port),
connect_timeout=1,
cursorclass=MySQLdb.cursors.DictCursor)
cursor = goinc_conn.cursor()
cursor.execute('inception get variables')
rows = cursor.fetchall()
full_goinception_info = dict()
for row in rows:
full_goinception_info[row.get('Variable_name')] = row.get('Value')
goinception_info = {
'version': full_goinception_info.get('version'),
'max_allowed_packet':
full_goinception_info.get('max_allowed_packet'),
'lang': full_goinception_info.get('lang'),
'osc_on': full_goinception_info.get('osc_on'),
'osc_bin_dir': full_goinception_info.get('osc_bin_dir'),
'ghost_on': full_goinception_info.get('ghost_on'),
}
except Exception as e:
goinception_info = f'获取goInception信息报错:{e}'
full_goinception_info = goinception_info
# 备份库
try:
bak_conn = MySQLdb.connect(host=inception_remote_backup_host,
port=int(inception_remote_backup_port),
user=inception_remote_backup_user,
password=inception_remote_backup_password,
connect_timeout=1)
cursor = bak_conn.cursor()
cursor.execute('select 1;')
backup_info = 'normal'
except Exception as e:
backup_info = f'无法连接Inception备份库\n{e}'
# PACKAGES
installed_packages = pkg_resources.working_set
installed_packages_list = sorted(
["%s==%s" % (i.key, i.version) for i in installed_packages])
# 最终集合
system_info = {
'archery': {
'version': archery.display_version
},
'django_q': django_q_info,
'inception': {
'enable_inception': sys_config.get('inception'),
'inception_info': full_inception_info if full else inception_info,
'goinception_info':
full_goinception_info if full else goinception_info,
'backup_info': backup_info
},
'runtime_info': {
'python_version': platform.python_version(),
'mysql_info': mysql_info,
'redis_info': full_redis_info if full else redis_info,
'sys_argv': sys.argv,
'platform': platform.uname()
},
'sys_config': sys_config,
'packages': installed_packages_list
}
return JsonResponse(system_info)
class ArcheryAuth(object):
def __init__(self, request):
self.request = request
self.sys_config = SysConfig()
@staticmethod
def challenge(username=None, password=None):
# 仅验证密码, 验证成功返回 user 对象, 清空计数器
user = authenticate(username=username, password=password)
# 登录成功
if user:
# 如果登录成功, 登录失败次数重置为0
user.failed_login_count = 0
user.save()
return user
def authenticate(self):
username = self.request.POST.get('username')
password = self.request.POST.get('password')
# 确认用户是否已经存在
try:
user = Users.objects.get(username=username)
except Users.DoesNotExist:
authenticated_user = self.challenge(username=username,
password=password)
if authenticated_user:
# ldap 首次登录逻辑
init_user(authenticated_user)
login(self.request, authenticated_user)
return {'status': 0, 'msg': 'ok', 'data': authenticated_user}
else:
return {'status': 1, 'msg': '用户名或密码错误，请重新输入！', 'data': ''}
except:
logger.error('验证用户密码时报错')
logger.error(traceback.format_exc())
return {
'status': 1,
'msg': f'服务器错误{traceback.format_exc()}',
'data': ''
}
# 已存在用户, 验证是否在锁期间
# 读取配置文件
lock_count = int(self.sys_config.get('lock_cnt_threshold', 5))
lock_time = int(self.sys_config.get('lock_time_threshold', 60 * 5))
# 验证是否在锁, 分了几个if 防止代码太长
if user.failed_login_count and user.last_login_failed_at:
if user.failed_login_count >= lock_count:
now = datetime.datetime.now()
if user.last_login_failed_at + datetime.timedelta(
seconds=lock_time) > now:
return {
'status': 3,
'msg': f'登录失败超过限制，该账号已被锁定！请等候大约{lock_time}秒再试',
'data': ''
}
else:
# 如果锁已超时, 重置失败次数
user.failed_login_count = 0
user.save()
authenticated_user = self.challenge(username=username,
password=password)
if authenticated_user:
if not authenticated_user.last_login:
init_user(authenticated_user)
login(self.request, authenticated_user)
return {'status': 0, 'msg': 'ok', 'data': authenticated_user}
user.failed_login_count += 1
user.last_login_failed_at = datetime.datetime.now()
user.save()
return {'status': 1, 'msg': '用户名或密码错误，请重新输入！', 'data': ''}
def execute_check(self, db_name=None, sql=''):
"""上线单执行前的检查, 返回Review set"""
archer_config = SysConfig()
check_result = ReviewSet(full_sql=sql)
if archer_config.get('critical_ddl_regex'):
# 如果启用critical_ddl 的检查
critical_ddl_regex = archer_config.get('critical_ddl_regex')
p = re.compile(critical_ddl_regex)
# 逐行匹配正则
line = 1
for statement in sqlparse.split(sql):
# 删除注释语句
statement = sqlparse.format(statement, strip_comments=True)
if p.match(statement.strip().lower()):
result = ReviewResult(id=line,
errlevel=2,
stagestatus='驳回高危SQL',
errormessage='禁止提交匹配' +
critical_ddl_regex + '条件的语句！',
sql=statement)
check_result.is_critical = True
else:
result = ReviewResult(id=line, errlevel=0, sql=statement)
check_result.rows += [result]
line += 1
if check_result.is_critical:
return check_result
# 检查 inception 不支持的函数
check_result.rows = []
line = 1
for statement in sqlparse.split(sql):
# 删除注释语句
statement = sqlparse.format(statement, strip_comments=True)
if re.match(
r"(\s*)alter(\s+)table(\s+)(\S+)(\s*);|(\s*)alter(\s+)table(\s+)(\S+)\.(\S+)(\s*);",
statement.lower() + ";"):
result = ReviewSet(id=line,
errlevel=2,
stagestatus='SQL语法错误',
errormessage='ALTER TABLE 必须带有选项',
sql=statement)
check_result.is_critical = True
else:
result = ReviewSet(id=line, errlevel=0, sql=statement)
check_result.rows += [result]
line += 1
if check_result.is_critical:
return check_result
# inception 校验
check_result.rows = []
inception_sql = "/*--user=%s;--password=%s;--host=%s;--enable-check=1;--port=%d;*/\
inception_magic_start;\
use %s;\
%s\
inception_magic_commit;" % (self.user, self.password, self.host,
self.port, db_name, sql)
inception_engine = InceptionEngine()
inception_result = inception_engine.query(sql=inception_sql)
for r in inception_result.rows:
check_result.rows += [ReviewResult(inception_result=r)]
check_result.column_list = inception_result.column_list
return check_result
def send_msg(workflowDetail, url):
mailSender = MailSender()
sys_config = SysConfig().sys_config
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Workflow.review_info(
workflowDetail.id, 2)
audit_id = Workflow.auditinfobyworkflow_id(workflowDetail.id, 2).audit_id
# 如果执行完毕了，则根据配置决定是否给提交者和DBA一封邮件提醒，DBA需要知晓审核并执行过的单子
msg_title = "[{}]工单{}#{}".format(
WorkflowDict.workflow_type['sqlreview_display'], workflowDetail.status,
audit_id)
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflowDetail.engineer_display, workflowDetail.group_name,
audit_auth_group, workflowDetail.workflow_name, url,
re.sub('[\r\n\f]{2,}', '\n',
workflowDetail.sql_content[0:500].replace('\r', '')))
if sys_config.get('mail'):
# 邮件通知申请人，审核人，抄送DBA
notify_users = workflowDetail.audit_auth_groups.split(',')
notify_users.append(workflowDetail.engineer)
listToAddr = [
email['email'] for email in Users.objects.filter(
username__in=notify_users).values('email')
]
listCcAddr = [
email['email'] for email in
auth_group_users(auth_group_names=['DBA'],
group_id=workflowDetail.group_id).values('email')
]
mailSender.send_email(msg_title,
msg_content,
listToAddr,
listCcAddr=listCcAddr)
if sys_config.get('ding'):
# 钉钉通知申请人，审核人，抄送DBA
webhook_url = SqlGroup.objects.get(
group_id=workflowDetail.group_id).ding_webhook
MailSender.send_ding(webhook_url, msg_title + '\n' + msg_content)
if sys_config.get('mail') and sys_config.get('ddl_notify_auth_group', None) \
and workflowDetail.status == '已正常结束':
# 判断上线语句是否存在DDL，存在则通知相关人员
sql_content = workflowDetail.sql_content
# 删除注释语句
sql_content = ''.join(
map(
lambda x: re.compile(r'(^--\s+.*|^/\*.*\*/;\s*$)').sub(
'', x, count=1), sql_content.splitlines(1))).strip()
# 去除空行
sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content)
# 匹配DDL语句CREATE、ALTER（排除索引变更）、DROP、TRUNCATE、RENAME
send = 0
for row in sql_content.strip(';').split(';'):
# alter语法
if re.match(
r"^alter\s+table\s+\S+\s+(add|alter|change|drop|rename|modify)\s+(?!.*(index|key|unique))",
row.strip().lower()):
send = 1
break
# create语法
elif re.match(r"^create\s+(temporary\s+)?(database|schema|table)",
row.strip().lower()):
send = 1
break
# drop语法
elif re.match(r"^drop", row.strip().lower()):
send = 1
break
# rename语法
elif re.match(r"^rename", row.strip().lower()):
send = 1
break
# truncate语法
elif re.match(r"^truncate", row.strip().lower()):
send = 1
break
if send == 1:
# 消息内容通知
msg_title = '[archery]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflowDetail.engineer).display,
workflowDetail.group_name, workflowDetail.instance_name,
workflowDetail.db_name, workflowDetail.workflow_name, url,
workflowDetail.sql_content[0:500])
# 获取通知成员
msg_to = [
email['email']
for email in Users.objects.filter(groups__name=sys_config.get(
'ddl_notify_auth_group')).values('email')
]
# 发送
mailSender.send_email(msg_title, msg_content, msg_to)
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
list_to_addr = [
email['email'] for email in Users.objects.filter(
username=workflow.engineer).values('email')
]
list_cc_addr = [
email['email'] for email
in auth_group_users(auth_group_names=['DBA'],
group_id=workflow.group_id).values('email')
]
msg_email_reciver = list_to_addr + list_cc_addr
# 判断是发送钉钉还是发送邮件
msg_sender = MsgSender()
logger.info('发送消息通知，消息audit_id={}'.format(audit_id))
logger.info('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(msg_title,
msg_email_reciver,
msg_content))
if sys_config.get('mail'):
msg_sender.send_email(msg_title,
msg_content,
list_to_addr,
list_cc_addr=list_cc_addr)
if sys_config.get('ding'):
# 钉钉通知申请人，审核人，抄送DBA
webhook_url = ResourceGroup.objects.get(
group_id=workflow.group_id).ding_webhook
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 = [
email['email']
for email in Users.objects.filter(groups__name=sys_config.get(
'ddl_notify_auth_group')).values('email')
]
# 发送
logger.info('发送DDL通知，消息audit_id={}'.format(audit_id))
logger.info('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(
msg_title, msg_to, msg_content))
msg_sender.send_email(msg_title, msg_content, msg_to)
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import logging
import json
import time
import traceback
import requests
from common.config import SysConfig
from sql.utils.ding_api import get_access_token, rs
logger = logging.getLogger('default')
sys_config = SysConfig()
username2ding = sys_config.get('ding_archer_username')
ding_dept_ids = sys_config.get('ding_dept_ids', '')
def get_dept_list_id_fetch_child(token, parent_dept_id):
ids = [int(parent_dept_id)]
url = 'https://oapi.dingtalk.com/department/list_ids?id={0}&access_token={1}'.format(
parent_dept_id, token)
resp = requests.get(url, timeout=3)
ret = str(resp.content, encoding="utf8")
s = json.loads(ret)
if s["errcode"] == 0:
for dept_id in s["sub_dept_id_list"]:
ids.extend(get_dept_list_id_fetch_child(token, dept_id))
return ids
def sync_ding_user_id():
"""
def autoreview(request):
workflow_id = request.POST.get('workflow_id')
sql_content = request.POST['sql_content']
workflow_title = request.POST['workflow_name']
group_name = request.POST['group_name']
group_id = ResourceGroup.objects.get(group_name=group_name).group_id
instance_name = request.POST['instance_name']
db_name = request.POST.get('db_name')
is_backup = request.POST['is_backup']
notify_users = request.POST.getlist('notify_users')
# 服务器端参数验证
if sql_content is None or workflow_title is None or instance_name is None or db_name is None or is_backup is None:
context = {'errMsg': '页面提交参数可能为空'}
return render(request, 'error.html', context)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(request.user, 'master').get(instance_name=instance_name)
except Exception:
context = {'errMsg': '你所在组未关联该实例！'}
return render(request, 'error.html', context)
# # 删除注释语句
# sql_content = ''.join(
# map(lambda x: re.compile(r'(^--.*|^/\*.*\*/;\s*$)').sub('', x, count=1),
# sql_content.splitlines(1))).strip()
# # 去除空行
# sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content)
sql_content = sql_content.strip()
# 交给inception进行自动审核
try:
inception_result = InceptionDao(instance_name=instance_name).sqlauto_review(sql_content, db_name)
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
if inception_result is None or len(inception_result) == 0:
context = {'errMsg': 'inception返回的结果集为空！可能是SQL语句有语法错误'}
return render(request, 'error.html', context)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
json_result = json.dumps(inception_result)
# 遍历result，看是否有任何自动审核不通过的地方，并且按配置确定是标记审核不通过还是放行，放行的可以在工单内跳过inception直接执行
sys_config = SysConfig().sys_config
is_manual = 0
workflow_status = Const.workflowStatus['manreviewing']
for row in inception_result:
# 1表示警告，不影响执行
if row[2] == 1 and sys_config.get('auto_review_wrong', '') == '1':
workflow_status = Const.workflowStatus['autoreviewwrong']
break
# 2表示严重错误，或者inception不支持的语法，标记手工执行，可以跳过inception直接执行
elif row[2] == 2:
is_manual = 1
if sys_config.get('auto_review_wrong', '') in ('', '1', '2'):
workflow_status = Const.workflowStatus['autoreviewwrong']
break
elif re.match(r"\w*comments\w*", row[4]):
is_manual = 1
if sys_config.get('auto_review_wrong', '') in ('', '1', '2'):
workflow_status = Const.workflowStatus['autoreviewwrong']
break
# 判断SQL是否包含DDL语句，SQL语法 1、DDL，2、DML
sql_syntax = 2
for stmt in sqlparse.split(sql_content):
statement = sqlparse.parse(stmt)[0]
syntax_type = statement.token_first().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 = Workflow.audit_settings(group_id, WorkflowDict.workflow_type['sqlreview'])
sql_workflow.status = workflow_status
sql_workflow.is_backup = is_backup
sql_workflow.review_content = json_result
sql_workflow.instance_name = instance_name
sql_workflow.db_name = db_name
sql_workflow.sql_content = sql_content
sql_workflow.execute_result = ''
sql_workflow.is_manual = is_manual
sql_workflow.audit_remark = ''
sql_workflow.sql_syntax = sql_syntax
sql_workflow.save()
workflow_id = sql_workflow.id
# 自动审核通过了，才调用工作流
if workflow_status == Const.workflowStatus['manreviewing']:
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
# 抄送通知人
list_cc_addr = [email['email'] for email in
Users.objects.filter(username__in=notify_users).values('email')]
Workflow().addworkflowaudit(request, WorkflowDict.workflow_type['sqlreview'], workflow_id,
list_cc_addr=list_cc_addr)
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))
def _send(audit_info, msg_type=0, **kwargs):
audit_id = audit_info.audit_id
workflow_audit_remark = kwargs.get('audit_remark', '')
workflow_url = kwargs.get('workflow_url', '')
msg_email_cc = kwargs.get('email_cc', [])
workflow_id = audit_info.workflow_id
workflow_type = audit_info.workflow_type
status = audit_info.current_status
workflow_title = audit_info.workflow_title
workflow_from = audit_info.create_user_display
group_name = audit_info.group_name
webhook_url = SqlGroup.objects.get(
group_id=audit_info.group_id).ding_webhook
# 获取当前审批和审批流程
from sql.utils.workflow import Workflow
workflow_auditors, current_workflow_auditors = Workflow.review_info(
audit_info.workflow_id, audit_info.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)
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:
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)
workflow_content = re.sub(
'[\r\n\f]{2,}', '\n',
workflow_detail.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_info.current_audit).name
msg_email_reciver = [
user.email for user in auth_group_users([auth_group_names],
audit_info.group_id)
]
# 消息内容
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n当前审批：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_from, group_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_email_reciver = [
Users.objects.get(username=audit_info.create_user).email
]
# 消息内容
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_from, group_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_email_reciver = [
Users.objects.get(username=audit_info.create_user).email
]
# 消息内容
msg_content = '''工单名称：{}\n工单地址：{}\n驳回原因：{}\n提醒：此工单被审核不通过，请按照驳回原因进行修改！'''.format(
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_info.audit_auth_groups.split(',')
]
msg_email_reciver = [
user.email
for user in auth_group_users(auth_group_names, audit_info.group_id)
]
# 消息内容
msg_content = '''发起人：{}\n组：{}\n工单名称：{}\n工单地址：{}\n提醒：提交人主动终止流程'''.format(
workflow_from, group_name, workflow_title, workflow_url)
else:
raise Exception('工单状态不正确')
# 判断是发送钉钉还是发送邮件
msg_sender = MailSender()
logger.debug('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(msg_title,
msg_email_reciver,
msg_content))
if msg_type == 0:
sys_config = SysConfig().sys_config
if sys_config.get('mail'):
msg_sender.send_email(msg_title,
msg_content,
msg_email_reciver,
list_cc_addr=msg_email_cc)
if sys_config.get('ding'):
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
elif msg_type == 1:
msg_sender.send_email(msg_title,
msg_content,
msg_email_reciver,
list_cc_addr=msg_email_cc)
elif msg_type == 2:
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
