def __extract_tables(self):
"""获取sql语句中的表名"""
return [i['name'].strip('`') for i in extract_tables(self.sqltext)]
def query_priv_check(user, instance, db_name, sql_content, limit_num):
"""
查询权限校验
:param user:
:param instance:
:param db_name:
:param sql_content:
:param limit_num:
:return:
"""
result = {'status': 0, 'msg': 'ok', 'data': {'priv_check': True, 'limit_num': 0}}
# 如果有can_query_all_instance, 视为管理员, 仅获取limit值信息
# superuser 拥有全部权限, 不需做特别修改
if user.has_perm('sql.query_all_instances'):
priv_limit = int(SysConfig().get('admin_query_limit', 5000))
result['data']['limit_num'] = min(priv_limit, limit_num) if limit_num else priv_limit
return result
# explain和show create跳过权限校验
if re.match(r"^explain|^show\s+create", sql_content, re.I):
return result
# 其他尝试使用inception解析
try:
# 尝试使用Inception校验表权限
table_ref = _table_ref(f"{sql_content.rstrip(';')};", instance, db_name)
# 循环验证权限，可能存在性能问题，但一次查询涉及的库表数量有限，可忽略
for table in table_ref:
# 既无库权限也无表权限
if not _db_priv(user, instance, table['db']) and not _tb_priv(user, instance, db_name, table['table']):
result['status'] = 1
result['msg'] = f"你无{db_name}.{table['table']}表的查询权限！请先到查询权限管理进行申请"
return result
# 获取查询涉及库/表权限的最小limit限制，和前端传参作对比，取最小值
# 循环获取，可能存在性能问题，但一次查询涉及的库表数量有限，可忽略
for table in table_ref:
priv_limit = _priv_limit(user, instance, db_name=table['db'], tb_name=table['table'])
limit_num = min(priv_limit, limit_num) if limit_num else priv_limit
result['data']['limit_num'] = limit_num
except SyntaxError as msg:
result['status'] = 1
result['msg'] = f"SQL语法错误，{msg}"
return result
except Exception as msg:
# 表权限校验失败再次校验库权限
# 先获取查询语句涉及的库
if instance.db_type in ['redis', 'mssql']:
dbs = [db_name]
else:
dbs = [i['schema'].strip('`') for i in extract_tables(sql_content) if i['schema'] is not None]
dbs.append(db_name)
# 库去重
dbs = list(set(dbs))
# 排序
dbs.sort()
# 校验库权限，无库权限直接返回
for db_name in dbs:
if not _db_priv(user, instance, db_name):
result['status'] = 1
result['msg'] = f"你无{db_name}数据库的查询权限！请先到查询权限管理进行申请"
return result
# 有所有库权限则获取最小limit值
for db_name in dbs:
priv_limit = _priv_limit(user, instance, db_name=db_name)
limit_num = min(priv_limit, limit_num) if limit_num else priv_limit
result['data']['limit_num'] = limit_num
# 实例为mysql的，需要判断query_check状态
if instance.db_type == 'mysql':
# 开启query_check，则禁止执行
if SysConfig().get('query_check'):
result['status'] = 1
result['msg'] = f"无法校验查询语句权限，请检查语法是否正确或联系管理员，错误信息：{msg}"
return result
# 关闭query_check，标记权限校验为跳过，可继续执行
else:
result['data']['priv_check'] = False
return result
def __extract_tables(self):
"""获取sql语句中的表名"""
return [
i.name.replace('`', '').lower()
for i in extract_tables(self.sqltext)
]
def query_priv_check(user, instance, db_name, sql_content, limit_num):
"""
查询权限校验
:param user:
:param instance:
:param db_name:
:param sql_content:
:param limit_num:
:return:
"""
result = {
'status': 0,
'msg': 'ok',
'data': {
'priv_check': True,
'limit_num': 0
}
}
# 如果有can_query_all_instance, 视为管理员, 仅获取limit值信息
# superuser 拥有全部权限, 不需做特别修改
if user.has_perm('sql.query_all_instances'):
priv_limit = int(SysConfig().get('admin_query_limit', 5000))
result['data']['limit_num'] = min(
priv_limit, limit_num) if limit_num else priv_limit
return result
# 如果有can_query_resource_group_instance, 视为资源组管理员, 可查询资源组内所有实例数据
if user.has_perm('sql.query_resource_group_instance'):
if user_instances(user, tag_codes=['can_read'
]).filter(pk=instance.pk).exists():
priv_limit = int(SysConfig().get('admin_query_limit', 5000))
result['data']['limit_num'] = min(
priv_limit, limit_num) if limit_num else priv_limit
return result
# 仅MySQL做表权限校验
if instance.db_type == 'mysql':
try:
# explain和show create跳过权限校验
if re.match(r"^explain|^show\s+create", sql_content, re.I):
return result
# 其他权限校验
table_ref = _table_ref(sql_content, instance, db_name)
# 循环验证权限，可能存在性能问题，但一次查询涉及的库表数量有限
for table in table_ref:
# 既无库权限也无表权限则鉴权失败
if not _db_priv(user, instance, table['schema']) and \
not _tb_priv(user, instance, table['schema'], table['name']):
# 没有库表查询权限时的staus为2
result['status'] = 2
result[
'msg'] = f"你无{table['schema']}.{table['name']}表的查询权限！请先到查询权限管理进行申请"
return result
# 获取查询涉及库/表权限的最小limit限制，和前端传参作对比，取最小值
for table in table_ref:
priv_limit = _priv_limit(user,
instance,
db_name=table['schema'],
tb_name=table['name'])
limit_num = min(priv_limit,
limit_num) if limit_num else priv_limit
result['data']['limit_num'] = limit_num
except Exception as msg:
logger.error(
f"无法校验查询语句权限，{instance.instance_name}，{sql_content}，{traceback.format_exc()}"
)
result['status'] = 1
result['msg'] = f"无法校验查询语句权限，请联系管理员，错误信息：{msg}"
# 其他类型实例仅校验库权限
else:
# 先获取查询语句涉及的库，redis、mssql特殊处理，仅校验当前选择的库
if instance.db_type in ['redis', 'mssql']:
dbs = [db_name]
else:
dbs = [
i['schema'].strip('`') for i in extract_tables(sql_content)
if i['schema'] is not None
]
dbs.append(db_name)
# 库去重
dbs = list(set(dbs))
# 排序
dbs.sort()
# 校验库权限，无库权限直接返回
for db_name in dbs:
if not _db_priv(user, instance, db_name):
# 没有库表查询权限时的staus为2
result['status'] = 2
result['msg'] = f"你无{db_name}数据库的查询权限！请先到查询权限管理进行申请"
return result
# 有所有库权限则获取最小limit值
for db_name in dbs:
priv_limit = _priv_limit(user, instance, db_name=db_name)
limit_num = min(priv_limit, limit_num) if limit_num else priv_limit
result['data']['limit_num'] = limit_num
return result
