def applyforprivileges(request): title = request.POST['title'] instance_name = request.POST['instance_name'] group_name = request.POST['group_name'] group_id = ResourceGroup.objects.get(group_name=group_name).group_id priv_type = request.POST['priv_type'] db_name = request.POST['db_name'] valid_date = request.POST['valid_date'] limit_num = request.POST['limit_num'] # 获取用户信息 user = request.user # 服务端参数校验 result = {'status': 0, 'msg': 'ok', 'data': []} if int(priv_type) == 1: db_list = request.POST['db_list'] if title is None or instance_name is None or db_list is None or valid_date is None or limit_num is None: result['status'] = 1 result['msg'] = '请填写完整' return HttpResponse(json.dumps(result), content_type='application/json') elif int(priv_type) == 2: table_list = request.POST['table_list'] if title is None or instance_name is None or db_name is None or valid_date is None or table_list is None or limit_num is None: result['status'] = 1 result['msg'] = '请填写完整' return HttpResponse(json.dumps(result), content_type='application/json') try: user_instances(request.user, 'slave').get(instance_name=instance_name) except Exception: context = {'errMsg': '你所在组未关联该实例!'} return render(request, 'error.html', context) # 判断是否需要限制到表级别的权限 # 库权限 if int(priv_type) == 1: db_list = db_list.split(',') # 检查申请账号是否已拥整个库的查询权限 own_dbs = QueryPrivileges.objects.filter( instance_name=instance_name, user_name=user.username, db_name__in=db_list, valid_date__gte=datetime.datetime.now(), priv_type=1, is_deleted=0).values('db_name') own_db_list = [table_info['db_name'] for table_info in own_dbs] if own_db_list is None: pass else: for db_name in db_list: if db_name in own_db_list: result['status'] = 1 result[ 'msg'] = '你已拥有' + instance_name + '实例' + db_name + '库的全部查询权限,不能重复申请' return HttpResponse(json.dumps(result), content_type='application/json') # 表权限 elif int(priv_type) == 2: table_list = table_list.split(',') # 检查申请账号是否已拥有该表的查询权限 own_tables = QueryPrivileges.objects.filter( instance_name=instance_name, user_name=user.username, db_name=db_name, table_name__in=table_list, valid_date__gte=datetime.datetime.now(), priv_type=2, is_deleted=0).values('table_name') own_table_list = [ table_info['table_name'] for table_info in own_tables ] if own_table_list is None: pass else: for table_name in table_list: if table_name in own_table_list: result['status'] = 1 result[ 'msg'] = '你已拥有' + instance_name + '实例' + db_name + '.' + table_name + '表的查询权限,不能重复申请' return HttpResponse(json.dumps(result), content_type='application/json') # 使用事务保持数据一致性 try: with transaction.atomic(): # 保存申请信息到数据库 applyinfo = QueryPrivilegesApply() applyinfo.title = title applyinfo.group_id = group_id applyinfo.group_name = group_name applyinfo.audit_auth_groups = Audit.settings( group_id, WorkflowDict.workflow_type['query']) applyinfo.user_name = user.username applyinfo.user_display = user.display applyinfo.instance_name = instance_name if int(priv_type) == 1: applyinfo.db_list = ','.join(db_list) applyinfo.table_list = '' elif int(priv_type) == 2: applyinfo.db_list = db_name applyinfo.table_list = ','.join(table_list) applyinfo.priv_type = int(priv_type) applyinfo.valid_date = valid_date applyinfo.status = WorkflowDict.workflow_status[ 'audit_wait'] # 待审核 applyinfo.limit_num = limit_num applyinfo.create_user = user.username applyinfo.save() apply_id = applyinfo.apply_id # 调用工作流插入审核信息,查询权限申请workflow_type=1 audit_result = Audit.add(WorkflowDict.workflow_type['query'], apply_id) if audit_result['status'] == 0: # 更新业务表审核状态,判断是否插入权限信息 query_audit_call_back(apply_id, audit_result['data']['workflow_status']) except Exception as msg: logger.error(traceback.format_exc()) result['status'] = 1 result['msg'] = str(msg) else: result = audit_result return HttpResponse(json.dumps(result), content_type='application/json')
def submit(request): """正式提交SQL, 此处生成工单""" sql_content = request.POST.get('sql_content').strip() workflow_title = request.POST.get('workflow_name') demand_url = request.POST.get('demand_url', '') # 检查用户是否有权限涉及到资源组等, 比较复杂, 可以把检查权限改成一个独立的方法 group_name = request.POST.get('group_name') group_id = ResourceGroup.objects.get(group_name=group_name).group_id instance_name = request.POST.get('instance_name') instance = Instance.objects.get(instance_name=instance_name) db_name = request.POST.get('db_name') is_backup = True if request.POST.get('is_backup') == 'True' else False cc_users = request.POST.getlist('cc_users') run_date_start = request.POST.get('run_date_start') run_date_end = request.POST.get('run_date_end') # 服务器端参数验证 if None in [ sql_content, db_name, instance_name, db_name, is_backup, demand_url ]: context = {'errMsg': '页面提交参数可能为空'} return render(request, 'error.html', context) # 验证组权限(用户是否在该组、该组是否有指定实例) try: user_instances(request.user, tag_codes=['can_write' ]).get(instance_name=instance_name) except instance.DoesNotExist: context = {'errMsg': '你所在组未关联该实例!'} return render(request, 'error.html', context) # 再次交给engine进行检测,防止绕过 try: check_engine = get_engine(instance=instance) check_result = check_engine.execute_check(db_name=db_name, sql=sql_content.strip()) except Exception as e: context = {'errMsg': str(e)} return render(request, 'error.html', context) # 未开启备份选项,并且engine支持备份,强制设置备份 sys_config = SysConfig() if not sys_config.get('enable_backup_switch') and check_engine.auto_backup: is_backup = True # 按照系统配置确定是自动驳回还是放行 auto_review_wrong = sys_config.get('auto_review_wrong', '') # 1表示出现警告就驳回,2和空表示出现错误才驳回 workflow_status = 'workflow_manreviewing' if check_result.warning_count > 0 and auto_review_wrong == '1': workflow_status = 'workflow_autoreviewwrong' elif check_result.error_count > 0 and auto_review_wrong in ('', '1', '2'): workflow_status = 'workflow_autoreviewwrong' # 调用工作流生成工单 # 使用事务保持数据一致性 try: with transaction.atomic(): # 存进数据库里 sql_workflow = SqlWorkflow.objects.create( workflow_name=workflow_title, demand_url=demand_url, group_id=group_id, group_name=group_name, engineer=request.user.username, engineer_display=request.user.display, audit_auth_groups=Audit.settings( group_id, WorkflowDict.workflow_type['sqlreview']), status=workflow_status, is_backup=is_backup, instance=instance, db_name=db_name, is_manual=0, syntax_type=check_result.syntax_type, create_time=timezone.now(), run_date_start=run_date_start or None, run_date_end=run_date_end or None) SqlWorkflowContent.objects.create( workflow=sql_workflow, sql_content=sql_content, review_content=check_result.json(), execute_result='') workflow_id = sql_workflow.id # 自动审核通过了,才调用工作流 if workflow_status == 'workflow_manreviewing': # 调用工作流插入审核信息, 查询权限申请workflow_type=2 Audit.add(WorkflowDict.workflow_type['sqlreview'], workflow_id) except Exception as msg: logger.error(f"提交工单报错,错误信息:{traceback.format_exc()}") context = {'errMsg': msg} logger.error(traceback.format_exc()) return render(request, 'error.html', context) else: # 自动审核通过才进行消息通知 if workflow_status == 'workflow_manreviewing': # 获取审核信息 audit_id = Audit.detail_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id async_task(notify_for_audit, audit_id=audit_id, cc_users=cc_users, timeout=60, task_name=f'sqlreview-submit-{workflow_id}') return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def submit(request): """正式提交SQL, 此处生成工单""" sql_content = request.POST['sql_content'].strip() workflow_title = request.POST['workflow_name'] # 检查用户是否有权限涉及到资源组等, 比较复杂, 可以把检查权限改成一个独立的方法 # 工单表中可以考虑不存储资源组相关信息 # 工单和实例关联, 实例和资源组关联, 资源组和用户关联。(reply 一个实例可以被多个资源组关联,无法去除) group_name = request.POST['group_name'] group_id = ResourceGroup.objects.get(group_name=group_name).group_id instance_name = request.POST['instance_name'] instance = Instance.objects.get(instance_name=instance_name) db_name = request.POST.get('db_name') is_backup = True if request.POST['is_backup'] == 'True' else False notify_users = request.POST.getlist('notify_users') list_cc_addr = [ email['email'] for email in Users.objects.filter( username__in=notify_users).values('email') ] # 服务器端参数验证 if None in [sql_content, db_name, instance_name, db_name, is_backup]: context = {'errMsg': '页面提交参数可能为空'} return render(request, 'error.html', context) # 验证组权限(用户是否在该组、该组是否有指定实例) try: user_instances(request.user, type='master', db_type='all').get(instance_name=instance_name) except instance.DoesNotExist: context = {'errMsg': '你所在组未关联该实例!'} return render(request, 'error.html', context) # 再次交给engine进行检测,防止绕过 try: check_engine = get_engine(instance=instance) check_result = check_engine.execute_check(db_name=db_name, sql=sql_content.strip()) except Exception as e: context = {'errMsg': str(e)} return render(request, 'error.html', context) # 按照系统配置确定是自动驳回还是放行 sys_config = SysConfig() auto_review_wrong = sys_config.get('auto_review_wrong', '') # 1表示出现警告就驳回,2和空表示出现错误才驳回 workflow_status = 'workflow_manreviewing' if check_result.warning_count > 0 and auto_review_wrong == '1': workflow_status = 'workflow_autoreviewwrong' elif check_result.error_count > 0 and auto_review_wrong in ('', '1', '2'): workflow_status = 'workflow_autoreviewwrong' # 调用工作流生成工单 # 使用事务保持数据一致性 try: with transaction.atomic(): # 存进数据库里 sql_workflow = SqlWorkflow.objects.create( workflow_name=workflow_title, group_id=group_id, group_name=group_name, engineer=request.user.username, engineer_display=request.user.display, audit_auth_groups=Audit.settings( group_id, WorkflowDict.workflow_type['sqlreview']), status=workflow_status, is_backup=is_backup, instance=instance, db_name=db_name, is_manual=0, syntax_type=check_result.syntax_type, create_time=timezone.now()) SqlWorkflowContent.objects.create( workflow=sql_workflow, sql_content=sql_content, review_content=check_result.json(), execute_result='') workflow_id = sql_workflow.id # 自动审核通过了,才调用工作流 if workflow_status == 'workflow_manreviewing': # 调用工作流插入审核信息, 查询权限申请workflow_type=2 Audit.add(WorkflowDict.workflow_type['sqlreview'], workflow_id) except Exception as msg: logger.error(f"提交工单报错,错误信息:{traceback.format_exc()}") context = {'errMsg': msg} return render(request, 'error.html', context) else: # 自动审核通过才进行消息通知 if workflow_status == 'workflow_manreviewing': # 获取审核信息 audit_id = Audit.detail_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id async_task(notify_for_audit, audit_id=audit_id, email_cc=list_cc_addr, timeout=60) return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def newexec(request): """正式提交SQL, 此处生成工单""" workflow_name = request.POST.get('workflow_name') demand_url = request.POST.get('demand_url') group_name = request.POST.get('group_name') instance_name = request.POST.get('instance_name') db_name = request.POST.get('db_name') first_run_time = request.POST.get('first_run_time') schedule_type = request.POST.get('period') minutes = request.POST.get('minutes') receivers = request.POST.getlist('receivers') cc_list = request.POST.getlist('cc_list') is_backup = True if request.POST.get('is_backup') == 'True' else False sql_content = request.POST.get('sql_content').strip() # 服务器端参数验证 if not all([ workflow_name, group_name, instance_name, db_name, first_run_time, schedule_type, sql_content ]): context = {'errMsg': '请检查提交工单填写内容是否完整'} return render(request, 'error.html', context) group_id = ResourceGroup.objects.get(group_name=group_name).group_id instance = Instance.objects.get(instance_name=instance_name) # 验证组权限(用户是否在该组、该组是否有指定实例) try: user_instances(request.user, tag_codes=['can_write' ]).get(instance_name=instance_name) except instance.DoesNotExist: context = {'errMsg': '你所在组未关联该实例!'} return render(request, 'error.html', context) # 再次交给engine进行检测,防止绕过 try: check_engine = get_engine(instance=instance) check_result = check_engine.execute_check(db_name=db_name, sql=sql_content.strip()) except Exception as e: context = {'errMsg': str(e)} return render(request, 'error.html', context) # 未开启备份选项,并且engine支持备份,强制设置备份 sys_config = SysConfig() if not sys_config.get('enable_backup_switch') and check_engine.auto_backup: is_backup = True # 按照系统配置确定是自动驳回还是放行 auto_review_wrong = sys_config.get('auto_review_wrong', '') # 1表示出现警告就驳回,2和空表示出现错误才驳回 workflow_status = 'workflow_manreviewing' if check_result.warning_count > 0 and auto_review_wrong == '1': workflow_status = 'workflow_autoreviewwrong' elif check_result.error_count > 0 and auto_review_wrong in ('', '1', '2'): workflow_status = 'workflow_autoreviewwrong' # 调用工作流生成工单 # 使用事务保持数据一致性 try: with transaction.atomic(): # 存进数据库里 sql_workflow = SqlWorkflow.objects.create( order_type="sqlcron_order", workflow_name=workflow_name, demand_url=demand_url, group_id=group_id, group_name=group_name, engineer=request.user.username, engineer_display=request.user.display, audit_auth_groups=Audit.settings( group_id, WorkflowDict.workflow_type['sqlreview']), status=workflow_status, is_backup=is_backup, instance=instance, db_name=db_name, is_manual=0, syntax_type=check_result.syntax_type, create_time=timezone.now(), run_date_start=None, run_date_end=None, cc_list=cc_list, ) sql_workflow.receivers.set( Users.objects.filter(username__in=receivers)) sql_workflow_content = SqlWorkflowContent.objects.create( workflow=sql_workflow, sql_content=sql_content, review_content=check_result.json(), execute_result='') sched = schedule( 'sql.utils.execute_sql.execute', sql_workflow.id, hook='sql.utils.execute_sql.execute_callback', name=f'sqlcron-{sql_workflow.id}', schedule_type=schedule_type, minutes=minutes, next_run=first_run_time, repeats=0, # 在审批结束前创建但不启用 timeout=-1, ) sql_workflow.schedule = sched sql_workflow.save() sql_workflow_content.save() # 自动审核通过了,才调用工作流 if workflow_status == 'workflow_manreviewing': # 调用工作流插入审核信息, 查询权限申请workflow_type=2 Audit.add(WorkflowDict.workflow_type['sqlreview'], sql_workflow.id) except Exception as msg: logger.error(f"提交工单报错,错误信息:{traceback.format_exc()}") context = {'errMsg': msg} logger.error(traceback.format_exc()) return render(request, 'error.html', context) else: # 自动审核通过才进行消息通知 if workflow_status == 'workflow_manreviewing': # 获取审核信息 audit_id = Audit.detail_by_workflow_id( workflow_id=sql_workflow.id, workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id async_task(notify_for_audit, audit_id=audit_id, cc_users=receivers, timeout=60, task_name=f'sqlreview-submit-{sql_workflow.id}') return redirect(reverse('sql:sqlcrondetail', args=(sql_workflow.id, )))
def newquery(request): """正式提交SQL, 此处生成工单""" workflow_name = request.POST.get('workflow_name') demand_url = request.POST.get('demand_url', '') group_name = request.POST.get('group_name') instance_name = request.POST.get('instance_name') db_name = request.POST.get('db_name') first_run_time = request.POST.get('first_run_time') schedule_type = request.POST.get('period') minutes = request.POST.get('minutes') receivers = request.POST.getlist('receivers') cc_list = request.POST.getlist('cc_list') is_backup = False sql_content = request.POST.get('sql_content').strip() result = {'status': 0, 'msg': 'ok', 'data': {}} # 服务器端参数验证 if not all([ workflow_name, group_name, instance_name, db_name, first_run_time, schedule_type, sql_content ]): result['status'] = 1 result['msg'] = '请检查提交工单填写内容是否完整' return HttpResponse(json.dumps(result), content_type='application/json') user = request.user group_id = ResourceGroup.objects.get(group_name=group_name).group_id instance = Instance.objects.get(instance_name=instance_name) # 验证组权限(用户是否在该组、该组是否有指定实例) try: user_instances(user, tag_codes=['can_read']).get(instance_name=instance_name) except instance.DoesNotExist: result['status'] = 1 result['msg'] = '你所在组未关联该实例' return HttpResponse(json.dumps(result), content_type='application/json') try: config = SysConfig() # 查询前的检查,禁用语句检查,语句切分 query_engine = get_engine(instance=instance) query_check_info = query_engine.query_check(db_name=db_name, sql=sql_content) if query_check_info.get('bad_query'): # 引擎内部判断为 bad_query result['status'] = 1 result['msg'] = query_check_info.get('msg') return HttpResponse(json.dumps(result), content_type='application/json') if query_check_info.get( 'has_star') and config.get('disable_star') is True: # 引擎内部判断为有 * 且禁止 * 选项打开 result['status'] = 1 result['msg'] = query_check_info.get('msg') return HttpResponse(json.dumps(result), content_type='application/json') sql_content = query_check_info['filtered_sql'] # 查询权限校验,并且获取limit_num priv_check_info = query_priv_check(user, instance, db_name, sql_content, 0) if priv_check_info['status'] != 0: result['status'] = 1 result['msg'] = priv_check_info['msg'] return HttpResponse(json.dumps(result), content_type='application/json') except Exception as e: logger.error( f'查询异常报错,查询语句:{sql_content}\n,错误信息:{traceback.format_exc()}') result['status'] = 1 result['msg'] = f'查询异常报错,错误信息:{e}' return HttpResponse(json.dumps(result), content_type='application/json') check_result = ReviewSet(full_sql=sql_content) check_result.rows = [ ReviewResult( id=1, errlevel=0, stagestatus='Audit completed', errormessage='None', sql=sql_content, affected_rows=0, execute_time=0, ) ] # 调用工作流生成工单 # 使用事务保持数据一致性 try: with transaction.atomic(): # 存进数据库里 sql_workflow = SqlWorkflow.objects.create( order_type="sqlcron_order", workflow_name=workflow_name, demand_url=demand_url, group_id=group_id, group_name=group_name, engineer=request.user.username, engineer_display=request.user.display, audit_auth_groups=Audit.settings( group_id, WorkflowDict.workflow_type['sqlreview']), status='workflow_manreviewing', is_backup=is_backup, instance=instance, db_name=db_name, is_manual=0, syntax_type=3, #0、未知,1、DDL,2、DML 3、QUERY' create_time=timezone.now(), run_date_start=None, run_date_end=None, cc_list=cc_list, ) sql_workflow.receivers.set( Users.objects.filter(username__in=receivers)) sql_workflow_content = SqlWorkflowContent.objects.create( workflow=sql_workflow, sql_content=sql_content, review_content=check_result.json(), execute_result='') sched = schedule( 'sql.utils.query_sql.query', sql_workflow.id, hook='sql.utils.query_sql.query_callback', name=f'sqlcron-{sql_workflow.id}', schedule_type=schedule_type, minutes=minutes, next_run=first_run_time, repeats=0, # 在审批结束前创建但不启用 timeout=-1, ) sql_workflow.schedule = sched sql_workflow.save() sql_workflow_content.save() # 调用工作流插入审核信息, 查询权限申请workflow_type=2 Audit.add(WorkflowDict.workflow_type['sqlreview'], sql_workflow.id) except Exception as e: logger.error(f"提交工单报错,错误信息:{traceback.format_exc()}") result['status'] = 1 result['msg'] = f'提交工单报错,错误信息:{e}' return HttpResponse(json.dumps(result), content_type='application/json') else: # 进行消息通知 audit_id = Audit.detail_by_workflow_id( workflow_id=sql_workflow.id, workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id async_task(notify_for_audit, audit_id=audit_id, cc_users=receivers, timeout=60, task_name=f'sqlreview-submit-{sql_workflow.id}') result['status'] = 0 result['data'] = { 'redirect': reverse('sql:sqlcrondetail', args=(sql_workflow.id, )) } return HttpResponse(json.dumps(result), content_type='application/json')
def archive_apply(request): """申请归档实例数据""" user = request.user title = request.POST.get('title') group_name = request.POST.get('group_name') src_instance_name = request.POST.get('src_instance_name') src_db_name = request.POST.get('src_db_name') src_table_name = request.POST.get('src_table_name') mode = request.POST.get('mode') dest_instance_name = request.POST.get('dest_instance_name') dest_db_name = request.POST.get('dest_db_name') dest_table_name = request.POST.get('dest_table_name') condition = request.POST.get('condition') no_delete = True if request.POST.get('no_delete') == 'true' else False sleep = request.POST.get('sleep', 0) result = {'status': 0, 'msg': 'ok', 'data': {}} # 参数校验 if not all([ title, group_name, src_instance_name, src_db_name, src_table_name, mode, condition ]) or no_delete is None: return JsonResponse({'status': 1, 'msg': '请填写完整!', 'data': {}}) if mode == 'dest' and not all( [dest_instance_name, dest_db_name, dest_table_name]): return JsonResponse({ 'status': 1, 'msg': '归档到实例时目标实例信息必选!', 'data': {} }) # 获取源实例信息 try: s_ins = user_instances(request.user, db_type=['mysql' ]).get(instance_name=src_instance_name) except Instance.DoesNotExist: return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例!', 'data': {}}) # 获取目标实例信息 if mode == 'dest': try: d_ins = user_instances( request.user, db_type=['mysql']).get(instance_name=dest_instance_name) except Instance.DoesNotExist: return JsonResponse({ 'status': 1, 'msg': '你所在组未关联该实例!', 'data': {} }) else: d_ins = None # 获取资源组和审批信息 res_group = ResourceGroup.objects.get(group_name=group_name) audit_auth_groups = Audit.settings(res_group.group_id, WorkflowDict.workflow_type['archive']) if not audit_auth_groups: return JsonResponse({ 'status': 1, 'msg': '审批流程不能为空,请先配置审批流程', 'data': {} }) # 使用事务保持数据一致性 try: with transaction.atomic(): # 保存申请信息到数据库 archive_info = ArchiveConfig.objects.create( title=title, resource_group=res_group, audit_auth_groups=audit_auth_groups, src_instance=s_ins, src_db_name=src_db_name, src_table_name=src_table_name, dest_instance=d_ins, dest_db_name=dest_db_name, dest_table_name=dest_table_name, condition=condition, mode=mode, no_delete=no_delete, sleep=sleep, status=WorkflowDict.workflow_status['audit_wait'], state=False, user_name=user.username, user_display=user.display, ) archive_id = archive_info.id # 调用工作流插入审核信息 audit_result = Audit.add(WorkflowDict.workflow_type['archive'], archive_id) except Exception as msg: logger.error(traceback.format_exc()) result['status'] = 1 result['msg'] = str(msg) else: result = audit_result # 消息通知 audit_id = Audit.detail_by_workflow_id( workflow_id=archive_id, workflow_type=WorkflowDict.workflow_type['archive']).audit_id async_task(notify_for_audit, audit_id=audit_id, timeout=60, task_name=f'archive-apply-{archive_id}') return HttpResponse(json.dumps(result), content_type='application/json')
def query_priv_apply(request): """ 申请查询权限 :param request: :return: """ title = request.POST['title'] instance_name = request.POST.get('instance_name') group_name = request.POST.get('group_name') group_id = ResourceGroup.objects.get(group_name=group_name).group_id priv_type = request.POST.get('priv_type') db_name = request.POST.get('db_name') db_list = request.POST.getlist('db_list[]') table_list = request.POST.getlist('table_list[]') valid_date = request.POST.get('valid_date') limit_num = request.POST.get('limit_num') # 获取用户信息 user = request.user # 服务端参数校验 result = {'status': 0, 'msg': 'ok', 'data': []} if int(priv_type) == 1: if not (title and instance_name and db_list and valid_date and limit_num): result['status'] = 1 result['msg'] = '请填写完整' return HttpResponse(json.dumps(result), content_type='application/json') elif int(priv_type) == 2: if not (title and instance_name and db_name and valid_date and table_list and limit_num): result['status'] = 1 result['msg'] = '请填写完整' return HttpResponse(json.dumps(result), content_type='application/json') try: user_instances(request.user, type='slave', db_type='all').get(instance_name=instance_name) except Instance.DoesNotExist: context = {'errMsg': '你所在组未关联该实例!'} return render(request, 'error.html', context) # 库权限 ins = Instance.objects.get(instance_name=instance_name) if int(priv_type) == 1: # 检查申请账号是否已拥库查询权限 for db_name in db_list: if _db_priv(user, ins, db_name): result['status'] = 1 result['msg'] = f'你已拥有{instance_name}实例{db_name}库权限,不能重复申请' return HttpResponse(json.dumps(result), content_type='application/json') # 表权限 elif int(priv_type) == 2: # 先检查是否拥有库权限 if _db_priv(user, ins, db_name): result['status'] = 1 result['msg'] = f'你已拥有{instance_name}实例{db_name}库的全部权限,不能重复申请' return HttpResponse(json.dumps(result), content_type='application/json') # 检查申请账号是否已拥有该表的查询权限 for tb_name in table_list: if _tb_priv(user, ins, db_name, tb_name): result['status'] = 1 result[ 'msg'] = f'你已拥有{instance_name}实例{db_name}.{tb_name}表的查询权限,不能重复申请' return HttpResponse(json.dumps(result), content_type='application/json') # 使用事务保持数据一致性 try: with transaction.atomic(): # 保存申请信息到数据库 applyinfo = QueryPrivilegesApply( title=title, group_id=group_id, group_name=group_name, audit_auth_groups=Audit.settings( group_id, WorkflowDict.workflow_type['query']), user_name=user.username, user_display=user.display, instance=ins, priv_type=int(priv_type), valid_date=valid_date, status=WorkflowDict.workflow_status['audit_wait'], limit_num=limit_num) if int(priv_type) == 1: applyinfo.db_list = ','.join(db_list) applyinfo.table_list = '' elif int(priv_type) == 2: applyinfo.db_list = db_name applyinfo.table_list = ','.join(table_list) applyinfo.save() apply_id = applyinfo.apply_id # 调用工作流插入审核信息,查询权限申请workflow_type=1 audit_result = Audit.add(WorkflowDict.workflow_type['query'], apply_id) if audit_result['status'] == 0: # 更新业务表审核状态,判断是否插入权限信息 _query_apply_audit_call_back( apply_id, audit_result['data']['workflow_status']) except Exception as msg: logger.error(traceback.format_exc()) result['status'] = 1 result['msg'] = str(msg) else: result = audit_result # 消息通知 audit_id = Audit.detail_by_workflow_id( workflow_id=apply_id, workflow_type=WorkflowDict.workflow_type['query']).audit_id async_task(notify_for_audit, audit_id=audit_id, timeout=60) return HttpResponse(json.dumps(result), content_type='application/json')
def autoreview(request): """正式提交SQL, 此处生成工单""" workflow_id = request.POST.get('workflow_id') sql_content = request.POST['sql_content'] workflow_title = request.POST['workflow_name'] group_name = request.POST['group_name'] group_id = ResourceGroup.objects.get(group_name=group_name).group_id instance_name = request.POST['instance_name'] instance = Instance.objects.get(instance_name=instance_name) db_name = request.POST.get('db_name') is_backup = request.POST['is_backup'] notify_users = request.POST.getlist('notify_users') list_cc_addr = [ email['email'] for email in Users.objects.filter( username__in=notify_users).values('email') ] # 服务器端参数验证 if sql_content is None or workflow_title is None or instance_name is None or db_name is None or is_backup is None: context = {'errMsg': '页面提交参数可能为空'} return render(request, 'error.html', context) # 验证组权限(用户是否在该组、该组是否有指定实例) try: user_instances(request.user, 'master').get(instance_name=instance_name) except Exception: context = {'errMsg': '你所在组未关联该实例!'} return render(request, 'error.html', context) sql_content = sql_content.strip() # 审核 try: check_engine = get_engine(instance) check_result = check_engine.execute_check(db_name=db_name, sql=sql_content) except Exception as msg: context = {'errMsg': msg} return render(request, 'error.html', context) if not check_result: context = {'errMsg': 'inception返回的结果集为空!可能是SQL语句有语法错误'} return render(request, 'error.html', context) # 要把result转成JSON存进数据库里,方便SQL单子详细信息展示 # 遍历result,看是否有任何自动审核不通过的地方,并且按配置确定是标记审核不通过还是放行,放行的可以在工单内跳过inception直接执行 sys_config = SysConfig() is_manual = 0 workflow_status = 'workflow_manreviewing' for row in check_result.rows: # 1表示警告,不影响执行 if row.errlevel == 1 and sys_config.get('auto_review_wrong', '') == '1': workflow_status = 'workflow_autoreviewwrong' break # 2表示严重错误,或者inception不支持的语法,标记手工执行,可以跳过inception直接执行 elif row.errlevel == 2: is_manual = 1 if sys_config.get('auto_review_wrong', '') in ('', '1', '2'): workflow_status = 'workflow_autoreviewwrong' break elif re.match(r"\w*comments\w*", row.errormessage): is_manual = 1 if sys_config.get('auto_review_wrong', '') in ('', '1', '2'): workflow_status = 'workflow_autoreviewwrong' break # 判断SQL是否包含DDL语句,SQL语法 1、DDL,2、DML sql_syntax = 2 for stmt in sqlparse.split(sql_content): statement = sqlparse.parse(stmt)[0] syntax_type = statement.token_first(skip_cm=True).ttype.__str__() if syntax_type == 'Token.Keyword.DDL': sql_syntax = 1 break # 调用工作流生成工单 # 使用事务保持数据一致性 try: with transaction.atomic(): # 存进数据库里 engineer = request.user.username if not workflow_id: sql_workflow = SqlWorkflow() sql_workflow.create_time = timezone.now() else: sql_workflow = SqlWorkflow.objects.get(id=int(workflow_id)) sql_workflow.workflow_name = workflow_title sql_workflow.group_id = group_id sql_workflow.group_name = group_name sql_workflow.engineer = engineer sql_workflow.engineer_display = request.user.display sql_workflow.audit_auth_groups = Audit.settings( group_id, WorkflowDict.workflow_type['sqlreview']) sql_workflow.status = workflow_status sql_workflow.is_backup = is_backup sql_workflow.review_content = check_result.json() sql_workflow.instance_name = instance_name sql_workflow.db_name = db_name sql_workflow.sql_content = sql_content sql_workflow.execute_result = '' sql_workflow.is_manual = is_manual sql_workflow.audit_remark = '' sql_workflow.sql_syntax = sql_syntax sql_workflow.save() workflow_id = sql_workflow.id # 自动审核通过了,才调用工作流 if workflow_status == 'workflow_manreviewing': # 调用工作流插入审核信息, 查询权限申请workflow_type=2 Audit.add(WorkflowDict.workflow_type['sqlreview'], workflow_id) except Exception as msg: logger.error(traceback.format_exc()) context = {'errMsg': msg} return render(request, 'error.html', context) else: # 自动审核通过了,才调用工作流,进行消息通知 if workflow_status == 'workflow_manreviewing': # 再次获取审核信息 audit_id = Audit.detail_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id async_task(notify_for_audit, audit_id=audit_id, email_cc=list_cc_addr, timeout=60) return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def create(self, validated_data): """使用原工单submit流程创建工单""" workflow_data = validated_data.pop('workflow') instance = workflow_data['instance'] sql_content = validated_data['sql_content'].strip() user = Users.objects.get(username=workflow_data['engineer']) group = ResourceGroup.objects.get(pk=workflow_data['group_id']) active_user = Users.objects.filter(is_active=1) # 验证组权限(用户是否在该组、该组是否有指定实例) try: user_instances(user, tag_codes=['can_write']).get(id=instance.id) except instance.DoesNotExist: raise serializers.ValidationError({'errors': '你所在组未关联该实例!'}) # 再次交给engine进行检测,防止绕过 try: check_engine = get_engine(instance=instance) check_result = check_engine.execute_check(db_name=workflow_data['db_name'], sql=sql_content) except Exception as e: raise serializers.ValidationError({'errors': str(e)}) # 未开启备份选项,并且engine支持备份,强制设置备份 is_backup = workflow_data['is_backup'] if 'is_backup' in workflow_data.keys() else False sys_config = SysConfig() if not sys_config.get('enable_backup_switch') and check_engine.auto_backup: is_backup = True # 按照系统配置确定是自动驳回还是放行 auto_review_wrong = sys_config.get('auto_review_wrong', '') # 1表示出现警告就驳回,2和空表示出现错误才驳回 workflow_status = 'workflow_manreviewing' if check_result.warning_count > 0 and auto_review_wrong == '1': workflow_status = 'workflow_autoreviewwrong' elif check_result.error_count > 0 and auto_review_wrong in ('', '1', '2'): workflow_status = 'workflow_autoreviewwrong' workflow_data.update(status=workflow_status, is_backup=is_backup, is_manual=0, syntax_type=check_result.syntax_type, engineer_display=user.display, group_name=group.group_name, audit_auth_groups=Audit.settings(workflow_data['group_id'], WorkflowDict.workflow_type['sqlreview'])) try: with transaction.atomic(): workflow = SqlWorkflow.objects.create(**workflow_data) validated_data['review_content'] = check_result.json() workflow_content = SqlWorkflowContent.objects.create(workflow=workflow, **validated_data) # 自动审核通过了,才调用工作流 if workflow_status == 'workflow_manreviewing': # 调用工作流插入审核信息, SQL上线权限申请workflow_type=2 Audit.add(WorkflowDict.workflow_type['sqlreview'], workflow.id) except Exception as e: logger.error(f"提交工单报错,错误信息:{traceback.format_exc()}") raise serializers.ValidationError({'errors': str(e)}) else: # 自动审核通过且开启了Apply阶段通知参数才发送消息通知 is_notified = 'Apply' in sys_config.get('notify_phase_control').split(',') \ if sys_config.get('notify_phase_control') else True if workflow_status == 'workflow_manreviewing' and is_notified: # 获取审核信息 audit_id = Audit.detail_by_workflow_id(workflow_id=workflow.id, workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id async_task(notify_for_audit, audit_id=audit_id, cc_users=active_user, timeout=60, task_name=f'sqlreview-submit-{workflow.id}') return workflow_content
def submit(request): """正式提交SQL, 此处生成工单""" sql_content = request.POST['sql_content'] workflow_title = request.POST['workflow_name'] # 检查用户是否有权限涉及到资源组等, 比较复杂, 可以把检查权限改成一个独立的方法 # 工单表中可以考虑不存储资源组相关信息 # 工单和实例关联, 实例和资源组关联, 资源组和用户关联。 group_name = request.POST['group_name'] group_id = ResourceGroup.objects.get(group_name=group_name).group_id instance_name = request.POST['instance_name'] instance = Instance.objects.get(instance_name=instance_name) db_name = request.POST.get('db_name') is_backup = request.POST['is_backup'] notify_users = request.POST.getlist('notify_users') list_cc_addr = [ email['email'] for email in Users.objects.filter( username__in=notify_users).values('email') ] # 服务器端参数验证 if sql_content is None or workflow_title is None or instance_name is None or db_name is None or is_backup is None: context = {'errMsg': '页面提交参数可能为空'} return render(request, 'error.html', context) # 验证组权限(用户是否在该组、该组是否有指定实例) try: user_instances(request.user, type='master', db_type='mysql').get(instance_name=instance_name) except Exception: context = {'errMsg': '你所在组未关联该实例!'} return render(request, 'error.html', context) sql_content = sql_content.strip() # 审核 try: check_engine = get_engine(instance) check_result = check_engine.execute_check(db_name=db_name, sql=sql_content) except Exception as msg: context = {'errMsg': msg} return render(request, 'error.html', context) if not check_result: context = {'errMsg': 'inception返回的结果集为空!可能是SQL语句有语法错误'} return render(request, 'error.html', context) # 遍历result,看是否有任何自动审核不通过的地方,并且按配置确定是标记审核不通过还是放行,放行的可以在工单内原生执行 sys_config = SysConfig() is_manual = 0 workflow_status = 'workflow_manreviewing' for row in check_result.rows: # 1表示警告,不影响执行 if row.errlevel == 1 and sys_config.get('auto_review_wrong', '') == '1': workflow_status = 'workflow_autoreviewwrong' break # 2表示严重错误,或者inception不支持的语法,标记手工执行,可以跳过inception直接执行 elif row.errlevel == 2: is_manual = 1 if sys_config.get('auto_review_wrong', '') in ('', '1', '2'): workflow_status = 'workflow_autoreviewwrong' break elif re.match(r"\w*comments\w*", row.errormessage): is_manual = 1 if sys_config.get('auto_review_wrong', '') in ('', '1', '2'): workflow_status = 'workflow_autoreviewwrong' break # 判断SQL是否包含DDL语句,SQL语法 1、DDL,2、DML syntax_type = 2 for stmt in sqlparse.split(sql_content): if get_syntax_type(stmt) == 'DDL': syntax_type = 1 break # 调用工作流生成工单 # 使用事务保持数据一致性 try: with transaction.atomic(): # 存进数据库里 sql_workflow = SqlWorkflow.objects.create( workflow_name=workflow_title, group_id=group_id, group_name=group_name, engineer=request.user.username, engineer_display=request.user.display, audit_auth_groups=Audit.settings( group_id, WorkflowDict.workflow_type['sqlreview']), status=workflow_status, is_backup=is_backup, instance=instance, db_name=db_name, is_manual=is_manual, syntax_type=syntax_type, create_time=timezone.now()) SqlWorkflowContent.objects.create( workflow=sql_workflow, sql_content=sql_content, review_content=check_result.json(), execute_result='') workflow_id = sql_workflow.id # 自动审核通过了,才调用工作流 if workflow_status == 'workflow_manreviewing': # 调用工作流插入审核信息, 查询权限申请workflow_type=2 Audit.add(WorkflowDict.workflow_type['sqlreview'], workflow_id) except Exception as msg: logger.error(traceback.format_exc()) context = {'errMsg': msg} return render(request, 'error.html', context) else: # 自动审核通过了,才调用工作流,进行消息通知 if workflow_status == 'workflow_manreviewing': # 再次获取审核信息 audit_id = Audit.detail_by_workflow_id( workflow_id=workflow_id, workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id async_task(notify_for_audit, audit_id=audit_id, email_cc=list_cc_addr, timeout=60) return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))