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
