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