def update_sql_work(obj): sql_info = get_sql_info_by_id(obj.sql_id) if (obj.current_user_id != sql_info.create_user_id): return "你不能编辑此工单,你不是工单创建者!" audit_result = inception_util.sql_audit(get_use_db_sql(obj.sql_value, obj.db_name), cache.MyCache().get_mysql_host_info(obj.host_id)) if (get_sql_execute_status(audit_result) == False): return "提交的SQL有错误,请仔细检查!" sql = """update `mysql_audit`.`sql_work` set `title` = '{0}', `jira_url` = '{1}', `execute_user_id` = {2}, `is_backup` = {3}, sleep = {4}, `execute_user_name` = '{5}', audit_user_id = {6}, audit_user_name = '{7}', `sql_value` = '{8}', audit_result_value = '{9}' where id = {10};""".format(db_util.DBUtil().escape(str(obj.title)), db_util.DBUtil().escape(str(obj.jira_url)), obj.dba_user_id, obj.is_backup, obj.sleep_time, cache.MyCache().get_user_chinese_name(obj.dba_user_id), obj.audit_user_tmp, cache.MyCache().get_user_chinese_name(obj.audit_user_tmp), db_util.DBUtil().escape(obj.sql_value), db_util.DBUtil().escape(json.dumps(audit_result, default=lambda o: o.__dict__)), obj.sql_id) db_util.DBUtil().execute(settings.MySQL_HOST, sql) return "更新成功!"
def sql_execute(obj): try: return_info = custom_entity.Entity() return_info.message = "" return_info.execute_result = None sql_info = get_sql_info_by_id(obj.sql_id) user_info = cache.MyCache().get_user_info(obj.current_user_id) if (user_info.group_id != settings.ADMIN_GROUP_ID): # 如果审核没通过,或者审核失败,也不允许执行 if (sql_info.status == settings.SQL_NO_AUDIT or sql_info.status == settings.SQL_AUDIT_FAIL): return_info.message = "审核不通过,不允许执行!" return return_info # 如果工单指定执行的用户跟实际执行的用户不一样,那不允许通过 elif (sql_info.execute_user_id != user_info.user_id): return_info.message = "你不能执行此工单,该工单指定执行用户不是你!" return return_info if (sql_info.status == settings.SQL_EXECUTE_ING): # 如果工单正在执行中,不允许重复执行SQL return_info.message = "SQL工单正在执行中,请耐心等待..." return return_info if (sql_info.status == settings.SQL_EXECUTE_SUCCESS): # 如果已经执行成功,直接返回执行结果 return_info.execute_result = json.loads(sql_info.return_value) else: # 更新工单状态为执行中 sql = "update mysql_audit.sql_work set `status` = {0}, `execute_start_date_time` = NOW(), `execute_date_time` = NOW() where id = {1};".format(settings.SQL_EXECUTE_ING, sql_info.id) db_util.DBUtil().execute(settings.MySQL_HOST, sql) if (len(sql_info.execute_db_name.strip()) > 0): sql_info.sql_value = "use {0};{1}".format(sql_info.execute_db_name, sql_info.sql_value) result_obj = inception_util.sql_execute(sql_info.sql_value, cache.MyCache().get_mysql_host_info(sql_info.mysql_host_id), is_backup=sql_info.is_backup, ignore_warnings=True if (obj.ignore_warnings.upper() == "TRUE") else False, sleep_time=sql_info.sleep) sql = """update mysql_audit.sql_work set return_value = '{0}', `status` = {1}, `ignore_warnings` = {2}, `execute_finish_date_time` = NOW(), `real_execute_user_id` = {3}, `real_execute_user_name` = '{4}' where id = {5};""".format(db_util.DBUtil().escape(json.dumps(result_obj, default=lambda o: o.__dict__)), settings.SQL_EXECUTE_SUCCESS if (get_sql_execute_status(result_obj)) else settings.SQL_EXECUTE_FAIL, obj.ignore_warnings, obj.current_user_id, cache.MyCache().get_user_info(obj.current_user_id).chinese_name, sql_info.id) print(sql) db_util.DBUtil().execute(settings.MySQL_HOST, sql) send_mail_for_execute_success(sql_info.id) return_info.execute_result = result_obj except Exception, e: # 出现异常要更新状态,直接把状态变为fail sql = "update mysql_audit.sql_work set `status` = {0} where id = {1};".format(settings.SQL_EXECUTE_FAIL, sql_info.id) db_util.DBUtil().execute(settings.MySQL_HOST, sql) traceback.print_exc() return_info.message = "执行时出现异常,请联系管理员!"
def start_user(user_id): sql = """update mysql_audit.work_user set is_deleted = 0 where user_id = {0}; update mysql_audit.group_info t1 left join mysql_audit.work_user t2 on t1.group_id = t2.group_id set t1.user_count = t1.user_count + 1 where t2.user_id = {0};""".format(user_id) db_util.DBUtil().execute(settings.MySQL_HOST, sql) cache.MyCache().load_user_infos() cache.MyCache().load_group_infos() return "启用用户成功"
def send_mail_for_execute_success(sql_id): if (settings.EMAIL_SEND_ENABLE): sql_info = get_sql_info_by_id(sql_id) sql_info.status_str = settings.SQL_WORK_STATUS_DICT[sql_info.status] sql_info.host_url = request.host_url sql_info.email = "{0},{1}".format( cache.MyCache().get_user_email(sql_info.create_user_id), cache.MyCache().get_user_email(sql_info.audit_user_id)) if (len(sql_info.email) > 0): subject = "SQL工单-[{0}]-执行完成".format(sql_info.title) sql_info.work_url = "{0}execute/sql/execute/new/{1}".format( request.host_url, sql_info.id) content = render_template("mail_template.html", sql_info=sql_info) common_util.send_html(subject, sql_info.email, content)
def audit_sql(obj): obj.sql = get_use_db_sql(obj.sql, obj.db_name) return render_template("audit_view.html", audit_infos=inception_util.sql_audit( obj.sql, cache.MyCache().get_mysql_host_info( obj.host_id)))
def get(user_id): if not user_id: return None user_info = cache.MyCache().get_user_info(user_id=user_id) if (user_info != None): return User(user_info.user_name) return None
def execute_rollback_sql(sql_id): sql_info = get_sql_info_by_id(sql_id) rollback_host = cache.MyCache().get_mysql_host_info(int(sql_info.mysql_host_id)) rollback_sql = "start transaction; " + get_rollback_sql(sql_id).rollback_sql_value + " commit;" if (db_util.DBUtil().execute(rollback_host, rollback_sql)): db_util.DBUtil().execute(settings.MySQL_HOST, "update mysql_audit.sql_work set `status` = {0} where id = {1};".format(settings.SQL_WORK_ROLLBACK, sql_id)) return "回滚成功" return "回滚失败"
def get_sql_work_for_leader(obj): sql_where = "" user_info = cache.MyCache().get_user_info(obj.current_user_id) if (obj.tab_type == settings.ALL_SQL_WORK_TAB): sql_where = " and create_user_id = {0}".format(obj.current_user_id) else: sql_where = " and (create_user_id = {0} or create_user_group_id = {1})".format(obj.current_user_id, user_info.group_id) return get_sql_work_list_by_where(obj, sql_where)
def audit_sql_by_sql_id(sql_id): sql_info = get_sql_info_by_id(sql_id) sql_info.sql_value = get_use_db_sql(sql_info.sql_value, sql_info.execute_db_name) return render_template("audit_view.html", audit_infos=inception_util.sql_audit( sql_info.sql_value, cache.MyCache().get_mysql_host_info( sql_info.mysql_host_id)))
def add_user(obj): result_json = Entity() result_json.flag = False if (len(str(obj.user_name)) <= 0): result_json.message = "请输入用户名!" elif (len(str(obj.user_password)) <= 0): result_json.message = "请输入密码!" elif (len(str(obj.chinese_name)) <= 0): result_json.message = "请输入中文名!" elif (len(str(obj.email)) <= 0): result_json.message = "请输入用户邮箱!" elif (obj.group_id <= 0): result_json.message = "请选择用户组!" elif (obj.role_id <= 0): result_json.message = "请选择用户角色!" else: result_json.flag = True if (result_json.flag): # 这边要判断下用户名是否已经存在 sql = "select user_id from mysql_audit.work_user where user_name = '{0}' limit 1;".format( obj.user_name) result = db_util.DBUtil().fetchone(settings.MySQL_HOST, sql) if (result is not None): result_json.flag = False result_json.message = "用户名已存在!" else: # 对用户能够访问哪些数据库进行设置 if (len(obj.user_hosts) > 1): pass else: pass sql = """insert into mysql_audit.work_user (user_name, user_password, chinese_name, group_id, role_id, email) VALUES ('{0}', md5('{1}'), '{2}', {3}, {4}, '{5}'); update mysql_audit.group_info set user_count = user_count + 1 where group_id = {6};""" \ .format(obj.user_name, obj.user_password, obj.chinese_name, obj.group_id, obj.role_id, obj.email, obj.group_id) db_util.DBUtil().execute(settings.MySQL_HOST, sql) cache.MyCache().load_user_infos() cache.MyCache().load_group_infos() result_json.message = "添加用户成功!" return json.dumps(result_json, default=lambda o: o.__dict__)
def delete_user_group_info(group_id): # 删除用户组必须要判断组内是否还有用户 sql = "SELECT user_count FROM mysql_audit.group_info where group_id = {0};".format(group_id) result = db_util.DBUtil().fetchone(settings.MySQL_HOST, sql) if (int(result["user_count"]) > 0): return "该组内还有用户,请先删除用户再删除组!" else: sql = "update mysql_audit.group_info set is_deleted = 1 where group_id = {0};".format(group_id) db_util.DBUtil().execute(settings.MySQL_HOST, sql) cache.MyCache().load_group_infos() return "用户组删除成功!"
def audit_sql_work(obj): user_info = cache.MyCache().get_user_info(obj.current_user_id) if (user_info.group_id != settings.ADMIN_GROUP_ID): sql_work = get_sql_info_by_id(obj.sql_id) if (sql_work.audit_user_id != obj.current_user_id): return "此工单审核人不是你,你无法审核!" status = settings.SQL_AUDIT_OK if (obj.status) else settings.SQL_AUDIT_FAIL sql = """update `mysql_audit`.`sql_work` set `status` = {0}, remark = '{1}' where id = {2};""".format(status, obj.remark, obj.sql_id) db_util.DBUtil().execute(settings.MySQL_HOST, sql) send_mail_for_audit_success(obj.sql_id) return "操作成功!"
def get_database_names(host_id): html_str = """<select id="db_name" name="db_name" class="selectpicker show-tick form-control bs-select-hidden"> <option value="0" disabled selected style="color: black">请选择要执行的库:</option> {0} </select>""" options_str = "" result = db_util.DBUtil().get_list_infos(cache.MyCache().get_mysql_host_info(host_id=host_id), "show databases;") for num in range(0, len(result)): db_name = result[num].Database; # 过滤掉系统库 if (db_name != "information_schema" and db_name != "mysql" and db_name != "sys" and db_name != "performance_schema"): options_str += "<option value=\"{0}\">{1}</option>".format(db_name, db_name) return html_str.format(options_str)
def add_sql_work(obj): try: audit_result = inception_util.sql_audit( get_use_db_sql(obj.sql_value, obj.db_name), cache.MyCache().get_mysql_host_info(obj.host_id)) if (get_sql_execute_status(audit_result) == False): return "提交的SQL有错误,请审核之后在提交!" user_info = cache.MyCache().get_user_info(obj.current_user_id) sql = """INSERT INTO `mysql_audit`.`sql_work` (`create_user_id`, `audit_user_id`, `audit_date_time`, `execute_date_time`, `mysql_host_id`, `jira_url`, `is_backup`, `sql_value`, `return_value`, `status`, `title`, `audit_result_value`, `execute_db_name`, `create_user_group_id`, sleep, `create_user_name`, `audit_user_name`, `execute_user_name`, `execute_user_id`) VALUES ({0}, {1}, NOW(), NULL, {2}, '{3}', {4}, '{5}', '', {6}, '{7}', '{8}', '{9}', {10}, {11}, '{12}', '{13}', '{14}', {15});""" \ .format(obj.current_user_id, obj.audit_user_id, obj.host_id, db_util.DBUtil().escape(str(obj.jira_url)), obj.is_backup, db_util.DBUtil().escape(obj.sql_value), settings.SQL_NO_AUDIT, db_util.DBUtil().escape(str(obj.title)), db_util.DBUtil().escape(json.dumps(audit_result, default=lambda o: o.__dict__)), obj.db_name, user_info.group_id, obj.sleep_time, user_info.chinese_name, cache.MyCache().get_user_chinese_name(obj.audit_user_id), cache.MyCache().get_user_chinese_name(obj.dba_user_id), obj.dba_user_id) db_util.DBUtil().execute(settings.MySQL_HOST, sql) send_mail_for_create(0) return "创建SQL工单成功" except Exception, e: traceback.print_exc() return e.message
def add_group_info(obj): result_info = Entity() result_info.flag = False result_info.message = "" if (len(obj.group_name) <= 0): result_info.message = "请填写用户组名称!" elif (len(obj.remark_value) <= 0): result_info.message = "请填写用户组备注!" else: sql = "insert into mysql_audit.group_info (group_name, remark) VALUES ('{0}', '{1}');".format(obj.group_name, obj.remark_value) db_util.DBUtil().execute(settings.MySQL_HOST, sql) cache.MyCache().load_group_infos() result_info.flag = True result_info.message = "添加用户组成功!" return to_json(result_info)
def add(obj): sql = "select host_id from mysql_audit.mysql_hosts where ip = '{0}' and port = {1};".format( obj.host_ip, obj.host_port) result = db_util.DBUtil().fetchone(settings.MySQL_HOST, sql) if (result is not None): return "1" is_alive = test_connection_new(obj) sql = """insert into mysql_audit.mysql_hosts (ip, port, `user`, `password`, host_name, is_alive) VALUES ('{0}', {1}, '{2}', '{3}', '{4}', {5})""" \ .format(obj.host_ip, obj.host_port, custom_algorithm.encrypt(settings.MY_KEY, obj.host_user), custom_algorithm.encrypt(settings.MY_KEY, obj.host_password), obj.host_name, is_alive) db_util.DBUtil().fetchone(settings.MySQL_HOST, sql) cache.MyCache().load_mysql_host_infos() return "2"
def query_host_infos(): return cache.MyCache().get_mysql_host_info()
def delete(obj): sql = "delete from mysql_audit.mysql_hosts where host_id = {0};".format( obj.host_id) db_util.DBUtil().execute(settings.MySQL_HOST, sql) cache.MyCache().delete_host_info_by_host_id(obj.host_id)
def get_execute_mysql_host(): return cache.MyCache().get_mysql_host_info()
def get_audit_mysql_host(): return cache.MyCache().get_mysql_host_info()
def add_group_info(obj): sql = "insert into mysql_audit.group_info (group_name, remark) VALUES ('{0}', '{1}');".format( obj.group_name, obj.remark_value) db_util.DBUtil().execute(settings.MySQL_HOST, sql) cache.MyCache().load_group_infos() return "添加用户组成功!"
def get_user_info_by_user_name(self, user_name, attr_name): for info in cache.MyCache().get_user_info(): if (info.user_name == user_name): value = getattr(info, attr_name) return value return None