def testSendMail(self, _quit, sendmail, login, _):
"""有密码测试"""
some_sub = 'test_subject'
some_body = 'mail_body'
some_to = ['mail_to']
archer_config = SysConfig()
archer_config.set('mail_ssl', '')
archer_config.set('mail_smtp_password', self.smtp_password)
sender = MsgSender()
sender.send_email(some_sub, some_body, some_to)
login.assert_called_once()
sendmail.assert_called_with(self.smtp_user, some_to, ANY)
_quit.assert_called_once()
def get_backup_connection():
archer_config = SysConfig()
backup_host = archer_config.get('inception_remote_backup_host')
backup_port = int(archer_config.get('inception_remote_backup_port', 3306))
backup_user = archer_config.get('inception_remote_backup_user')
backup_password = archer_config.get('inception_remote_backup_password')
return MySQLdb.connect(host=backup_host,
port=backup_port,
user=backup_user,
passwd=backup_password,
charset='utf8mb4',
autocommit=True
)
def tablesapce(request):
instance_name = request.POST.get('instance_name')
# 判断是RDS还是其他实例
if len(
AliyunRdsConfig.objects.filter(instance_name=instance_name,
is_enable=1)) > 0:
if SysConfig().sys_config.get('aliyun_rds_manage'):
result = aliyun_sapce_status(request)
else:
raise Exception('未开启rds管理，无法查看rds数据！')
else:
sql = '''
SELECT
table_schema,
table_name,
engine,
TRUNCATE((data_length+index_length+data_free)/1024/1024,2) AS total_size,
table_rows,
TRUNCATE(data_length/1024/1024,2) AS data_size,
TRUNCATE(index_length/1024/1024,2) AS index_size,
TRUNCATE(data_free/1024/1024,2) AS data_free,
TRUNCATE(data_free/(data_length+index_length+data_free)*100,2) AS pct_free
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'test', 'sys')
ORDER BY total_size DESC
LIMIT 14;'''.format(instance_name)
table_space = Dao(instance_name=instance_name).mysql_query(
'information_schema', sql)
column_list = table_space['column_list']
rows = []
for row in table_space['rows']:
row_info = {}
for row_index, row_item in enumerate(row):
row_info[column_list[row_index]] = row_item
rows.append(row_info)
result = {'status': 0, 'msg': 'ok', 'data': rows}
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def login_authenticate(username, password):
"""
登录认证，包含一个登录失败计数器，5分钟内连续失败5次的账号，会被锁定5分钟
"""
sys_config = SysConfig().sys_config
if sys_config.get('lock_cnt_threshold'):
lock_cnt_threshold = int(sys_config.get('lock_cnt_threshold'))
else:
lock_cnt_threshold = 5
if sys_config.get('lock_time_threshold'):
lock_time_threshold = int(sys_config.get('lock_time_threshold'))
else:
lock_time_threshold = 300
# 服务端二次验证参数
if username == "" or password == "" or username is None or password is None:
result = {'status': 2, 'msg': '登录用户名或密码为空，请重新输入!', 'data': ''}
elif username in login_failure_counter and login_failure_counter[username][
"cnt"] >= lock_cnt_threshold and (
datetime.datetime.now() - login_failure_counter[username]
["last_failure_time"]).seconds <= lock_time_threshold:
result = {'status': 3, 'msg': '登录失败超过5次，该账号已被锁定5分钟!', 'data': ''}
else:
# 登录
user = authenticate(username=username, password=password)
# 登录成功
if user:
# 如果登录失败计数器中存在该用户名，则清除之
if username in login_failure_counter:
login_failure_counter.pop(username)
result = {'status': 0, 'msg': 'ok', 'data': user}
# 登录失败
else:
if username not in login_failure_counter:
# 第一次登录失败，登录失败计数器中不存在该用户，则创建一个该用户的计数器
login_failure_counter[username] = {
"cnt": 1,
"last_failure_time": datetime.datetime.now()
}
else:
if (datetime.datetime.now() -
login_failure_counter[username]["last_failure_time"]
).seconds <= lock_time_threshold:
login_failure_counter[username]["cnt"] += 1
else:
# 上一次登录失败时间早于5分钟前，则重新计数。以达到超过5分钟自动解锁的目的。
login_failure_counter[username]["cnt"] = 1
login_failure_counter[username][
"last_failure_time"] = datetime.datetime.now()
result = {'status': 1, 'msg': '用户名或密码错误，请重新输入！', 'data': ''}
return result
def _db_priv(user, instance, db_name):
"""
检测用户是否拥有指定库权限
:param user: 用户对象
:param instance: 实例对象
:param db_name: 库名
:return: 权限存在则返回对应权限的limit_num，否则返回False
TODO 返回统一为 int 类型, 不存在返回0 (虽然其实在python中 0==False)
"""
# 获取用户库权限
user_privileges = QueryPrivileges.objects.filter(user_name=user.username, instance=instance, db_name=str(db_name),
valid_date__gte=datetime.datetime.now(), is_deleted=0,
priv_type=1)
if user.is_superuser:
return int(SysConfig().get('admin_query_limit', 5000))
else:
if user_privileges.exists():
return user_privileges.first().limit_num
return False
def _tb_priv(user, instance, db_name, tb_name):
"""
检测用户是否拥有指定表权限
:param user: 用户对象
:param instance: 实例对象
:param db_name: 库名
:param tb_name: 表名
:return: 权限存在则返回对应权限的limit_num，否则返回False
"""
# 获取用户表权限
user_privileges = QueryPrivileges.objects.filter(user_name=user.username, instance=instance, db_name=str(db_name),
table_name=str(tb_name), valid_date__gte=datetime.datetime.now(),
is_deleted=0, priv_type=2)
if user.is_superuser:
return int(SysConfig().get('admin_query_limit', 5000))
else:
if user_privileges.exists():
return user_privileges.first().limit_num
return False
def sqladvisor(request):
sql_content = request.POST.get('sql_content')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
verbose = request.POST.get('verbose')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 服务器端参数验证
if sql_content is None or instance_name is None:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result), content_type='application/json')
sql_content = sql_content.strip()
try:
user_instances(request.user, 'all').get(instance_name=instance_name)
except Exception:
result['status'] = 1
result['msg'] = '你所在组未关联该实例！'
return HttpResponse(json.dumps(result), content_type='application/json')
if verbose is None or verbose == '':
verbose = 1
# 取出实例的连接信息
instance_info = Instance.objects.get(instance_name=instance_name)
# 提交给sqladvisor获取审核结果
sqladvisor_path = SysConfig().get('sqladvisor')
sql_content = sql_content.strip().replace('"', '\\"').replace('`', '').replace('\n', ' ')
try:
p = subprocess.Popen(sqladvisor_path + ' -h "%s" -P "%s" -u "%s" -p "%s\" -d "%s" -v %s -q "%s"' % (
str(instance_info.host), str(instance_info.port), str(instance_info.user),
str(instance_info.raw_password), str(db_name), verbose, sql_content),
stdin=subprocess.PIPE,
stdout=subprocess.PIPE, stderr=subprocess.STDOUT, shell=True, universal_newlines=True)
stdout, stderr = p.communicate()
result['data'] = stdout
except Exception:
logger.error(traceback.format_exc())
result['data'] = 'sqladvisor运行报错，请检查日志'
return HttpResponse(json.dumps(result), content_type='application/json')
def test_replace_configs(self):
archer_config = SysConfig()
new_config = json.dumps(
[{'key': 'numconfig', 'value': 1},
{'key': 'strconfig', 'value': 'strconfig'},
{'key': 'boolconfig', 'value': 'false'}])
archer_config.replace(new_config)
archer_config.get_all_config()
expected_config = {
'numconfig': '1',
'strconfig': 'strconfig',
'boolconfig': False
}
self.assertEqual(archer_config.sys_config, expected_config)
def sign_up(request):
username = request.POST.get('username')
password = request.POST.get('password')
password2 = request.POST.get('password2')
display = request.POST.get('display')
email = request.POST.get('email')
result = {'status': 0, 'msg': 'ok', 'data': None}
if not (username and password):
result['status'] = 1
result['msg'] = '用户名和密码不能为空'
elif len(Users.objects.filter(username=username)) > 0:
result['status'] = 1
result['msg'] = '用户名已存在'
elif password != password2:
result['status'] = 1
result['msg'] = '两次输入密码不一致'
else:
# 验证密码
try:
validate_password(password)
except ValidationError as msg:
result['status'] = 1
result['msg'] = str(msg)
else:
# 添加用户并且添加到默认权限组
Users.objects.create_user(username=username,
password=password,
display=display,
email=email,
is_active=1,
is_staff=1)
default_auth_group = SysConfig().sys_config.get(
'default_auth_group', '')
try:
user = Users.objects.get(username=username)
group = Group.objects.get(name=default_auth_group)
user.groups.add(group)
except Exception:
logger.error('无name为{}的权限组，无法默认关联，请到系统设置进行配置'.format(
default_auth_group))
return HttpResponse(json.dumps(result), content_type='application/json')
def analyze(request):
"""
利用soar分析SQL
:param request:
:return:
"""
text = request.POST.get('text')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
if not text:
result = {"total": 0, "rows": []}
else:
soar = Soar()
if instance_name != '' and db_name != '':
try:
instance_info = user_instances(request.user, db_type=['mysql']).get(instance_name=instance_name)
except Instance.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例！', 'data': []})
soar_test_dsn = SysConfig().get('soar_test_dsn')
# 获取实例连接信息
online_dsn = "{user}:{pwd}@{host}:{port}/{db}".format(user=instance_info.user,
pwd=instance_info.password,
host=instance_info.host,
port=instance_info.port,
db=db_name)
else:
online_dsn = ''
soar_test_dsn = ''
args = {"report-type": "markdown",
"query": '',
"online-dsn": online_dsn,
"test-dsn": soar_test_dsn,
"allow-online-as-test": "false"}
rows = generate_sql(text)
for row in rows:
args['query'] = row['sql'].replace('\n', ' ')
cmd_args = soar.generate_args2cmd(args=args, shell=True)
stdout, stderr = soar.execute_cmd(cmd_args, shell=True).communicate()
row['report'] = stdout if stdout else stderr
result = {"total": len(rows), "rows": rows}
return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
content_type='application/json')
def kill_session(request):
instance_name = request.POST.get('instance_name')
request_params = request.POST.get('request_params')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 判断是RDS还是其他实例
if len(AliyunRdsConfig.objects.filter(instance_name=instance_name)) > 0:
if SysConfig().sys_config.get('aliyun_rds_manage'):
result = aliyun_kill_session(request)
else:
raise Exception('未开启rds管理，无法查看rds数据！')
else:
kill_sql = request_params
Dao(instance_name=instance_name).mysql_execute('information_schema',
kill_sql)
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def query_check(self, db_name=None, sql=''):
# 查询语句的检查、注释去除、切分
result = {'msg': '', 'bad_query': False, 'filtered_sql': sql, 'has_star': False}
banned_keywords = ["ascii", "char", "charindex", "concat", "concat_ws", "difference", "format",
"len", "nchar", "patindex", "quotename", "replace", "replicate",
"reverse", "right", "soundex", "space", "str", "string_agg",
"string_escape", "string_split", "stuff", "substring", "trim", "unicode"]
keyword_warning = ''
star_patter = r"(^|,|\s)\*(\s|\(|$)"
sql_whitelist = ['select', SysConfig().get("mssql_cmd_white_list", "").strip().split(',')]
# 根据白名单list拼接pattern语句
whitelist_pattern = "^" + "|^".join(sql_whitelist)
# 删除注释语句，进行语法判断，执行第一条有效sql
try:
sql = sql.format(sql, strip_comments=True)
sql = sqlparse.split(sql)[0]
result['filtered_sql'] = sql.strip()
sql_lower = sql.lower()
except IndexError:
result['bad_query'] = True
result['msg'] = '没有有效的SQL语句'
return result
if re.match(whitelist_pattern, sql_lower) is None:
result['bad_query'] = True
result['msg'] = '仅支持{}语法!'.format(','.join(sql_whitelist))
return result
if re.search(star_patter, sql_lower) is not None:
keyword_warning += '禁止使用 * 关键词\n'
result['has_star'] = True
if '+' in sql_lower:
keyword_warning += '禁止使用 + 关键词\n'
result['bad_query'] = True
for keyword in banned_keywords:
pattern = r"(^|,| |=){}( |\(|$)".format(keyword)
if re.search(pattern, sql_lower) is not None:
keyword_warning += '禁止使用 {} 关键词\n'.format(keyword)
result['bad_query'] = True
if result.get('bad_query') or result.get('has_star'):
result['msg'] = keyword_warning
return result
def testStarOptionOn(self, _priv_check):
c = Client()
c.force_login(self.u2)
some_limit = 100
sql_with_star = 'select * from some_table'
some_db = 'some_db'
_priv_check.return_value = {'status': 0, 'data': {'limit_num': 100, 'priv_check': 1}}
archer_config = SysConfig()
archer_config.set('disable_star', True)
r = c.post('/query/', data={'instance_name': self.slave1.instance_name,
'sql_content': sql_with_star,
'db_name': some_db,
'limit_num': some_limit})
archer_config.set('disable_star', False)
r_json = r.json()
self.assertEqual(1, r_json['status'])
class TestSchemaSync(TestCase):
"""
测试SchemaSync
"""
def setUp(self):
self.superuser = User(username='******', is_superuser=True)
self.superuser.save()
# 使用 travis.ci 时实例和测试service保持一致
self.master = Instance(
instance_name='test_instance',
type='master',
db_type='mysql',
host=settings.DATABASES['default']['HOST'],
port=settings.DATABASES['default']['PORT'],
user=settings.DATABASES['default']['USER'],
password=settings.DATABASES['default']['PASSWORD'])
self.master.save()
self.sys_config = SysConfig()
self.client = Client()
self.client.force_login(self.superuser)
def tearDown(self):
self.superuser.delete()
self.master.delete()
self.sys_config.replace(json.dumps({}))
def test_schema_sync(self):
"""
测试SchemaSync
:return:
"""
data = {
"instance_name": "test_instance",
"db_name": "*",
"target_instance_name": "test_instance",
"target_db_name": "*",
"sync_auto_inc": True,
"sync_comments": False
}
r = self.client.post(path='/instance/schemasync/', data=data)
self.assertEqual(json.loads(r.content)['status'], 1)
self.assertEqual(json.loads(r.content)['msg'], '请配置SchemaSync路径！')
self.sys_config.set('schemasync',
'/opt/venv4schemasync/bin/schemasync')
self.sys_config.get_all_config()
r = self.client.post(path='/instance/schemasync/', data=data)
self.assertEqual(json.loads(r.content)['status'], 0)
def sign_up(request):
sign_up_enabled = SysConfig().get('sign_up_enabled', False)
if not sign_up_enabled:
result = {'status': 1, 'msg': '注册未启用,请联系管理员开启', 'data': None}
return HttpResponse(json.dumps(result),
content_type='application/json')
username = request.POST.get('username')
password = request.POST.get('password')
password2 = request.POST.get('password2')
display = request.POST.get('display')
email = request.POST.get('email')
result = {'status': 0, 'msg': 'ok', 'data': None}
if not (username and password):
result['status'] = 1
result['msg'] = '用户名和密码不能为空'
elif len(Users.objects.filter(username=username)) > 0:
result['status'] = 1
result['msg'] = '用户名已存在'
elif password != password2:
result['status'] = 1
result['msg'] = '两次输入密码不一致'
elif not display:
result['status'] = 1
result['msg'] = '请填写中文名'
else:
# 验证密码
try:
validate_password(password)
Users.objects.create_user(username=username,
password=password,
display=display,
email=email,
is_active=1,
is_staff=True)
except ValidationError as msg:
result['status'] = 1
result['msg'] = str(msg)
return HttpResponse(json.dumps(result), content_type='application/json')
def sqlcronnewquery(request):
"""SQL在线查询页面"""
user = request.user
# 获取组信息
group_list = user_groups(user)
# 获取所有有效用户，通知对象
active_user = Users.objects.filter(is_active=1)
# 获取系统配置
archer_config = SysConfig()
# 主动创建标签
InstanceTag.objects.get_or_create(tag_code='can_read',
defaults={
'tag_name': '支持查询',
'active': True
})
context = {'active_user': active_user, 'group_list': group_list}
# 主动创建标签
return render(request, 'sqlcron/newquery.html', context)
def __init__(self, **kwargs):
if kwargs:
self.MAIL_REVIEW_SMTP_SERVER = kwargs.get('server')
self.MAIL_REVIEW_SMTP_PORT = kwargs.get('port', 0)
self.MAIL_REVIEW_FROM_ADDR = kwargs.get('user')
self.MAIL_REVIEW_FROM_PASSWORD = kwargs.get('password')
self.MAIL_SSL = kwargs.get('ssl')
else:
sys_config = SysConfig()
self.MAIL_REVIEW_SMTP_SERVER = sys_config.get('mail_smtp_server')
self.MAIL_REVIEW_SMTP_PORT = sys_config.get('mail_smtp_port', 0)
self.MAIL_SSL = sys_config.get('mail_ssl')
self.MAIL_REVIEW_FROM_ADDR = sys_config.get('mail_smtp_user')
self.MAIL_REVIEW_FROM_PASSWORD = sys_config.get('mail_smtp_password')
if self.MAIL_REVIEW_SMTP_PORT:
self.MAIL_REVIEW_SMTP_PORT = int(self.MAIL_REVIEW_SMTP_PORT)
elif self.MAIL_SSL:
self.MAIL_REVIEW_SMTP_PORT = 465
else:
self.MAIL_REVIEW_SMTP_PORT = 25
def authenticate_entry(request):
"""接收http请求，然后把请求中的用户名密码传给loginAuthenticate去验证"""
username = request.POST.get('username')
password = request.POST.get('password')
result = login_authenticate(username, password)
if result['status'] == 0:
# 开启LDAP的认证通过后更新用户密码
if settings.ENABLE_LDAP:
try:
Users.objects.get(username=username)
except Exception:
insert_info = Users()
insert_info.password = make_password(password)
insert_info.save()
else:
replace_info = Users.objects.get(username=username)
replace_info.password = make_password(password)
replace_info.save()
# 添加到默认组
default_auth_group = SysConfig().sys_config.get(
'default_auth_group', '')
try:
user = Users.objects.get(username=username)
group = Group.objects.get(name=default_auth_group)
user.groups.add(group)
except Exception:
logger.error(
'无name为{}的权限组，无法默认关联，请到系统设置进行配置'.format(default_auth_group))
# 调用了django内置登录方法，防止管理后台二次登录
user = authenticate(username=username, password=password)
if user:
login(request, user)
result = {'status': 0, 'msg': 'ok', 'data': None}
return HttpResponse(json.dumps(result), content_type='application/json')
def execute_workflow(self, workflow):
"""执行上线单，返回Review set"""
# 判断实例是否只读
read_only = self.query(sql='select @@read_only;').rows[0][0]
if read_only:
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
# 原生执行
if workflow.is_manual == 1:
return self.execute(db_name=workflow.db_name, sql=workflow.sqlworkflowcontent.sql_content)
# inception执行
elif not SysConfig().get('inception'):
inception_engine = GoInceptionEngine()
return inception_engine.execute(workflow)
else:
inception_engine = InceptionEngine()
return inception_engine.execute(workflow)
def process(request):
instance_name = request.POST.get('instance_name')
command_type = request.POST.get('command_type')
base_sql = "select id, user, host, db, command, time, state, ifnull(info,'') as info from information_schema.processlist"
# 判断是RDS还是其他实例
if len(
AliyunRdsConfig.objects.filter(instance_name=instance_name,
is_enable=1)) > 0:
if SysConfig().sys_config.get('aliyun_rds_manage'):
result = aliyun_process_status(request)
else:
raise Exception('未开启rds管理，无法查看rds数据！')
else:
if command_type == 'All':
sql = base_sql + ";"
elif command_type == 'Not Sleep':
sql = "{} where command<>'Sleep';".format(base_sql)
else:
sql = "{} where command= '{}';".format(base_sql, command_type)
processlist = Dao(instance_name=instance_name).mysql_query(
'information_schema', sql)
column_list = processlist['column_list']
rows = []
for row in processlist['rows']:
row_info = {}
for row_index, row_item in enumerate(row):
row_info[column_list[row_index]] = row_item
rows.append(row_info)
result = {'status': 0, 'msg': 'ok', 'data': rows}
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def __send(msg_title, msg_content, msg_to, msg_cc=None, **kwargs):
"""
按照通知配置发送通知消息
:param msg_title: 通知标题
:param msg_content: 通知内容
:param msg_to: 通知人user list
:return:
"""
sys_config = SysConfig()
msg_sender = MsgSender()
msg_cc = msg_cc if msg_cc else []
webhook_url = kwargs.get('webhook_url')
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 chain(msg_to, msg_cc) if user.ding_user_id]
msg_to_wx_user = [user.wx_user_id if user.wx_user_id else user.username for user in chain(msg_to, msg_cc)]
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'):
msg_sender.send_ding2user(msg_to_ding_user, msg_title + '\n' + msg_content)
if sys_config.get('wx'):
msg_sender.send_wx2user(msg_title + '\n' + msg_content, msg_to_wx_user)
def init_user(user):
"""
给用户关联默认资源组和权限组
:param user:
:return:
"""
# 添加到默认权限组
default_auth_group = SysConfig().get('default_auth_group', '')
if default_auth_group:
default_auth_group = default_auth_group.split(',')
[
user.groups.add(group)
for group in Group.objects.filter(name__in=default_auth_group)
]
# 添加到默认资源组
default_resource_group = SysConfig().get('default_resource_group', '')
if default_resource_group:
default_resource_group = default_resource_group.split(',')
[
user.resource_group.add(group)
for group in ResourceGroup.objects.filter(
group_name__in=default_resource_group)
]
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))
schema_name = request.POST.get('schema_name', None)
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 HttpResponse(json.dumps(result),
content_type='application/json')
# 服务器端参数验证
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:
# 获取主从延迟信息
seconds_behind_master = query_engine.seconds_behind_master
if instance.db_type == 'pgsql': # TODO 此处判断待优化，请在 修改传参方式后去除
query_result = query_engine.query(db_name,
sql_content,
limit_num,
schema_name=schema_name)
else:
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:
logger.warning(
f'数据脱敏异常，按照配置放行，查询语句：{sql_content}，错误信息：{masking_result.error}'
)
query_result.error = None
result['data'] = query_result.__dict__
# 正常脱敏
else:
result['data'] = masking_result.__dict__
except Exception as msg:
# 抛出未定义异常，并且开启query_check，直接返回异常，禁止执行
if config.get('query_check'):
result['status'] = 1
result['msg'] = f'数据脱敏异常，请联系管理员，错误信息：{msg}'
# 关闭query_check，忽略错误信息，返回未脱敏数据，权限校验标记为跳过
else:
logger.warning(
f'数据脱敏异常，按照配置放行，查询语句：{sql_content}，错误信息：{msg}')
query_result.error = None
result['data'] = query_result.__dict__
# 无需脱敏的语句
else:
result['data'] = query_result.__dict__
# 仅将成功的查询语句记录存入数据库
if not query_result.error:
result['data']['seconds_behind_master'] = 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=ExtendJSONEncoderFTime,
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 test_set_other_data(self):
archer_config = SysConfig()
archer_config.set('other_config', 'testvalue3')
self.assertEqual(archer_config.sys_config['other_config'],
'testvalue3')
def setUp(self):
archer_config = SysConfig()
self.smtp_server = 'test_smtp_server'
self.smtp_user = '******'
self.smtp_password = '******'
self.smtp_port = 1234
self.smtp_ssl = True
archer_config.set('mail_smtp_server', self.smtp_server)
archer_config.set('mail_smtp_user', self.smtp_user)
archer_config.set('mail_smtp_password', self.smtp_password)
archer_config.set('mail_smtp_port', self.smtp_port)
archer_config.set('mail_ssl', self.smtp_ssl)
def test_set_bool_transform(self):
archer_config = SysConfig()
archer_config.set('boolconfig3', False)
self.assertEqual(archer_config.sys_config['boolconfig3'], False)
def test_get_bool_transform(self):
bool_config = json.dumps([{'key': 'boolconfig2', 'value': 'false'}])
archer_config = SysConfig()
archer_config.replace(bool_config)
self.assertEqual(archer_config.sys_config['boolconfig2'], False)
def tearDown(self):
archer_config = SysConfig()
archer_config.set('mail_smtp_server', '')
archer_config.set('mail_smtp_user', '')
archer_config.set('mail_smtp_password', '')
archer_config.set('mail_smtp_port', '')
archer_config.set('mail_ssl', '')
def execute(request):
workflow_id = request.POST['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)
instance_name = workflow_detail.instance_name
db_name = workflow_detail.db_name
url = get_detail_url(request, workflow_id)
if can_execute(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 判断是否高危SQL，禁止执行
if SysConfig().sys_config.get('critical_ddl_regex', '') != '':
if InceptionDao().critical_ddl(workflow_detail.sql_content):
context = {'errMsg': '高危语句，禁止执行！'}
return render(request, 'error.html', context)
# 将流程状态修改为执行中，并更新reviewok_time字段
workflow_detail.status = Const.workflowStatus['executing']
workflow_detail.reviewok_time = timezone.now()
workflow_detail.save()
# 判断是通过inception执行还是直接执行，is_manual=0则通过inception执行，is_manual=1代表inception审核不通过，需要直接执行
if workflow_detail.is_manual == 0:
# 执行之前重新split并check一遍，更新SHA1缓存；因为如果在执行中，其他进程去做这一步操作的话，会导致inception core dump挂掉
try:
split_review_result = InceptionDao(
instance_name=instance_name).sqlauto_review(
workflow_detail.sql_content, db_name, is_split='yes')
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
workflow_detail.review_content = json.dumps(split_review_result)
try:
workflow_detail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflow_detail.save()
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_call_back,
args=(workflow_id, instance_name, url))
t.start()
else:
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_skipinc_call_back,
args=(workflow_id, instance_name, db_name,
workflow_detail.sql_content, url))
t.start()
# 删除定时执行job
if workflow_detail.status == Const.workflowStatus['timingtask']:
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
del_sqlcronjob(job_id)
# 增加工单日志
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
workflowOb.add_workflow_log(audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info="人工操作执行",
operator=request.user.username,
operator_display=request.user.display)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_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 = 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, )))
