def auditors(request): group_name = request.POST.get('group_name') workflow_type = request.POST['workflow_type'] result = {'status': 0, 'msg': 'ok', 'data': {'auditors': '', 'auditors_display': ''}} if group_name: group_id = SqlGroup.objects.get(group_name=group_name).group_id audit_auth_groups = Workflow.audit_settings(group_id=group_id, workflow_type=workflow_type) else: result['status'] = 1 result['msg'] = '参数错误' return HttpResponse(json.dumps(result), content_type='application/json') # 获取权限组名称 if audit_auth_groups: # 校验配置 for auth_group_id in audit_auth_groups.split(','): try: Group.objects.get(id=auth_group_id) except Exception: result['status'] = 1 result['msg'] = '审批流程权限组不存在,请重新配置!' return HttpResponse(json.dumps(result), content_type='application/json') audit_auth_groups_name = '->'.join( [Group.objects.get(id=auth_group_id).name for auth_group_id in audit_auth_groups.split(',')]) result['data']['auditors'] = audit_auth_groups result['data']['auditors_display'] = audit_auth_groups_name return HttpResponse(json.dumps(result), content_type='application/json')
def 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 = SqlGroup.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() if sql_content[-1] != ";": context = {'errMsg': "SQL语句结尾没有以;结尾,请后退重新修改并提交!"} return render(request, 'error.html', context) # 交给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 row in sql_content.strip(';').split(';'): if re.match(r"^alter|^create|^drop|^truncate|^rename", row.strip().lower()): 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') ] workflowOb.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 applyforprivileges(request): title = request.POST['title'] instance_name = request.POST['instance_name'] group_name = request.POST['group_name'] group_id = SqlGroup.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'] try: workflow_remark = request.POST['apply_remark'] except Exception: workflow_remark = '' # 获取用户信息 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 = Workflow.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 = workflowOb.addworkflowaudit( request, 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 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') # 服务器端参数验证 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().sys_config is_manual = 0 workflow_status = Const.workflowStatus['manreviewing'] for row in check_result.rows: # 1表示警告,不影响执行 if row.errlevel == 1 and sys_config.get('auto_review_wrong', '') == '1': workflow_status = Const.workflowStatus['autoreviewwrong'] break # 2表示严重错误,或者inception不支持的语法,标记手工执行,可以跳过inception直接执行 elif row.errlevel == 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.errormessage): 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(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 = Workflow.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 == 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, )))